Jump to content

mySQL little question. Deleteing from database sitiuation


npsari

Recommended Posts

 

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

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 :P

 

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.

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.