Delete if more than x rows

emitind

ok, i want to delete any rows which are greater than 20. i thought i could use num_rows and that gives me the number of rows but how do i use that number to keep the number of rows at 20? i can't use auto_incretment id because it doesn't take account of deleted entries.

i want any new entries to be stored with the oldest deleted.

this is the structure of the script:

if($num_rows < "20"){

$query = "DELETE FROM table WHERE id = ''"; 
$result = mysql_query($query);


}

...you can see i'm stuck at the WHERE.

i hope this makes sense! thanks 😕

BooRadLey

$num_rows = something;
$delete_amount = ($num_rows - 20);

if($num_rows > 20) { 

$query = "DELETE FROM table ORDER BY id LIMIT ".$delete_amount;

$result = mysql_query($query);

}

Please backup your table before you try this, as I am not sure on the ORDER BY clause and you might delete the first records, not the last, if it doesn't work right, try adding a DESC or ASC to the ORDER BY clause. Perhaps you can order by a timestamp rather then an id.

I hope this helps,

Brad

emitind

thanks alot, that was perfect 🆒

jonki

Hi - Your solution is exactly what I am looking for but for some reason it doesn't seem to work.

I do a statistical analysis of every search done on my site and therefore have a massive database and I am only interested in the last say 10 000 searches - I have reached that 10 000 so now all I want to do is, right after the line of code that inserts new data, put a line of code which deletes the first bit of data.

$query = "DELETE FROM marketanalysis ORDER BY user_id LIMIT 1"; 
$result = mysql_query($query);

This doesn't work.....

Is this a matter of switching my computer off and restarting it or am I missing something obvious???

Thanks a lot.

Jon