[SOLVED] Delete Oldest Posts First

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Cammet
Forum Newbie
Posts: 22
Joined: Thu Sep 23, 2004 8:00 am

Delete Oldest Posts First

Post 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
qads
DevNet Resident
Posts: 1199
Joined: Tue Apr 23, 2002 10:02 am
Location: Brisbane

Post 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");
}
?>
Cammet
Forum Newbie
Posts: 22
Joined: Thu Sep 23, 2004 8:00 am

Post by Cammet »

Ahhhh Thankyou very much :)
Worked Like A Charm :D
Cammet
Forum Newbie
Posts: 22
Joined: Thu Sep 23, 2004 8:00 am

Post 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
User avatar
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

limit

Post 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.
Cammet
Forum Newbie
Posts: 22
Joined: Thu Sep 23, 2004 8:00 am

Post by Cammet »

Sweet :D all makes sense now.
By The Way i love your sig phpScott :)
User avatar
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

thanks

Post 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:
qads
DevNet Resident
Posts: 1199
Joined: Tue Apr 23, 2002 10:02 am
Location: Brisbane

Re: thanks

Post by qads »

phpScott wrote:.....but to many people where taking it the wrong way...
and i was one of them :lol:
Cammet
Forum Newbie
Posts: 22
Joined: Thu Sep 23, 2004 8:00 am

Post by Cammet »

Hahahahaha:D i an see how that could happen
Post Reply