mySQL little question. Deleteing from database sitiuation

[npsari]

 

Allow me to describe my question,

 

I have a Table called ads

Contains 4 colombs

ID, Name, Post, Date

 

I know how to delete from the database

mysql_query("DELETE FROM ads WHERE ID='$ID'");
mysql_close($con);
print"Post deleted successfully";

 

But i want to do something a little different.

 

How can I make the databse store only 20 rows max

i.e. when a new post is added, the oldest one gets deleted automatically

The databse hence stores only 20

 

Please give me some feedback on how this can be done

[ryeman98]

Well I'm not entirely sure to be honest but...

 

If you only want to display 20 ads you could add 'LIMIT 5' on the end of your displaying query or...

 

I think you could use a math equation...like...when you are adding another row, run this:

$ID = //Put whatever that would equal here...

//Something like this...I'm not too sure...
$ID2 = $ID - 20;

mysql_query("DELETE FROM ads WHERE ID='$ID2'");
mysql_close($con);

 

I hope that helps... I also hope that I'm not wrong

 

[npsari]

thats a good way of doing it actually

 

ID -

 

Thanks

 

Any other ideas guys - if there is any

[ryeman98]

Thanks. For a month I've been in a mind block and I'm beginning to come out of it.

 

Oh, on my first post, I ment to say 'LIMIT 20' lol

 

Good luck!

Rye

[corbin]

Have an auto incrementing id column and do something like:

mysql_query("DELETE FROM table WHERE id < (MAX(id) - 20)"); //haven't tested this, so I don't know if it'll work ;p

 

 

Or you could do it based off a descending add_data column, or if your 'id' column goes in order you could do something like

 

$q = mysql_query("SELECT id FROM ads ORDER BY id DESC limit 19, 1"); //if the id's aren't in order you could try it with out the order clause.... but it could throw some random stuff at you ;p
$r = mysql_fetch_row($q);
if($r[0] > 0) {
mysql_query("DELETE FROM ads WHERE id < '{$r[0]}'");
}

 

With that method, you would wanna make sure there were more than 20 rows first, or it would mess up.

[npsari]

Thanks Corbin

 

I will try this technique