showing the form after insertind data

[sam1]

hi,

is there a way to delete data in mysql using the 'date' column, i have a table with name message and date. if the date is say yesterday then delete the row....

it is for the shoutbox i am implementing, so i need to delete one messages from yesterday. or is there other ways to delete them(better way)..

ps. sorry about the title i solved that problem
thanks.

[sam1]

ok some update:

i tried to count the number of rows and if it is more then four i delete the records in table:

$query = mysql_query("SELECT * FROM shoutbox");
$number=mysql_num_rows($query);


if ($number == 4)
{
	$query = "TRUNCATE TABLE shoutbox";
	mysql_query($query);
}

now my question how would i make it to delete only 2 rows at the beginning of table...

[Rhyan]

hi,

is there a way to delete data in mysql using the 'date' column, i have a table with name message and date. if the date is say yesterday then delete the row....

it is for the shoutbox i am implementing, so i need to delete one messages from yesterday. or is there other ways to delete them(better way)..

ps. sorry about the title i solved that problem
thanks.

You can delete basis 'date' coloumn, however, if you have time- like hours, minutes, seconds - you have to specify both time and date.

If you have an ID in your table, better delete records using the ID, or, try sorting the table on Date coloumn and then delete the unnecessary records.

[Rhyan]

ok some update:

i tried to count the number of rows and if it is more then four i delete the records in table:

$query = mysql_query("SELECT * FROM shoutbox");
$number=mysql_num_rows($query);


if ($number == 4)
{
    $query = "TRUNCATE TABLE shoutbox";
    mysql_query($query);
}

now my question how would i make it to delete only 2 rows at the beginning of table...

Dude, using truncate you delete ALL RECORDS in the table and it starts indexing from 0.

If I am understanding you correctly, you want to select the last 4 records and delete the rest, is it correct?

Please explain in simple words what do you want to do and we'll figure it out.

[sam1]

hi,

i got a ID in the table, as the above code shows i did delete it but all the table is getting deleted. i want to delete first few rows..

say i have 10 records and i want to delete first 5.
is that possible?

thanks

[sam1]

rhyan, i am implementing a shoutbox where i store the details in mysql. now if say 1000 users give a shout then it is gonna be alot of records in database.

now i want to delete the old shouts. for example when there is 1000 shouts then delete first 500.

[Rhyan]

rhyan, i am implementing a shoutbox where i store the details in mysql. now if say 1000 users give a shout then it is gonna be alot of records in database.

now i want to delete the old shouts. for example when there is 1000 shouts then delete first 500.

Oh, I see...
Now, there are a few things you have to figure out first.

1. Suppose this is a maintenance function you want to implement - is it going to be performed by an administrator, so he will run let's say 500 entries to delete, or, this furnction will be performed automatically on each post, and when the post number reaches let's say 1000 entries, you will delete 500?

2. Are you sure you want to delete these entries? Instead, why don't you show only the latest 10 or whatever number you want? In this way you may make an archive, so users can see the old shouts, following a link.

3. If your concern is the database, the amount of data stored, then you should not worry if you are using a DB like MySQL, Postgre, or Oracle. These databases are robust and they are capable of storing very very large ammounts of data. So no need to delete your entries. Deleting or archiving of data is good when using something like Access DB - it is not in a way solid thing, and it stores relatively "dirty" data, that is for the sole purpose of the access application to be able to manipulate with the data you need. So, what I want to say in short is - you don't have to delelte the records from the table - it will perform fast, no matter how much info you have stored. And text is not a large thing to store... if not storing all parts from "War And Peace" :)

Now, if you want to select only the latest 10 entries from the database, which are supposed to be the newest, then your select should be like this:

"SELECT * from shouts ORDER BY id DESC LIMIT 10";
This will return the latest 10 entries in your DB.

If this does not do the trick, then we will make it delete the records if you want, still - think it over - no need to delete.

[sam1]

thanks for your time and effort, now i understand it clearly. yes i went with your advice and i am happy now, as before i was worried about the space it takes to store all those data. thanks again