Page 1 of 1

Delete Oldest Posts First

Posted: Thu Sep 23, 2004 8:08 am
by Cammet
Hi There,
Im very much a mysql php newbie, i have started writting a script which people can post messages on similar to a bulliten board. what i would like to do is have a maximum of 200 posts and when this figure is reached the oldest posts are deleted so there will always be 200 hundred posts.
i see mysql has a delete function but i am not sure how to use this. in my table i have
id
timestamp
message

I would really love some help with this as im sure you guys have the answer. :D

Posted: Thu Sep 23, 2004 8:20 am
by qads

Code: Select all

<?php
$num = mysql_num_rows(mysql_query("select id from table limit 201"));//no need to grab 4000 records when we only need to check for > 200 rows
if($num >= 200)
{
//delete 20 posts
mysql_query("DELETE from table order by id ASC limit 20");
}
?>

Posted: Thu Sep 23, 2004 8:33 am
by Cammet
Ahhhh Thankyou very much :)
Worked Like A Charm :D

Posted: Thu Sep 23, 2004 9:32 am
by Cammet
Ok 1 thing i dont get.
$num = mysql_num_rows(mysql_query("select id from table limit 201"));
does the above code say check the first 201 roms or the rows with id 201 and below. Just if it does the latter then pretty soon it will be invalid because all the records with id 201 and below will of been deleted, because the number will keep incrementing.
Have i completely missed the point?
:D

limit

Posted: Thu Sep 23, 2004 10:41 am
by phpScott
The limit clause says to only return the firtst (in this case) 201 rows. So
the statement

Code: Select all

$num = mysql_num_rows(mysql_query("select id from table limit 201"));
says
count the number of rows returned from the query but only return the first 201 rows.

Posted: Thu Sep 23, 2004 10:51 am
by Cammet
Sweet :D all makes sense now.
By The Way i love your sig phpScott :)

thanks

Posted: Thu Sep 23, 2004 10:58 am
by phpScott
It is a change from what it used to be which was
"My wife just loves the back of my head"
but to many people where taking it the wrong way :wink:

Re: thanks

Posted: Thu Sep 23, 2004 11:49 am
by qads
phpScott wrote:.....but to many people where taking it the wrong way...
and i was one of them :lol:

Posted: Thu Sep 23, 2004 7:06 pm
by Cammet
Hahahahaha:D i an see how that could happen