Page 1 of 1

limit # of mysql rows

Posted: Mon Jan 06, 2003 7:20 pm
by stickman373
Hey,

What I want to do is have a mysql table which will at any one time never have more than 30 rows in it. So basically how can i insert and delete rows so that I always have 30 rows in my table never anymore than that?

I thought I could enter into the table each entry with an Id and then always delete the smallest id, but i'm just not sure on the coding, could anyone help me out?:Bang:

Posted: Mon Jan 06, 2003 10:15 pm
by mydimension
let's see what you have so far.

Posted: Wed Jan 08, 2003 10:08 am
by daven
When you want to insert a row, check for MAX(ID) and MIN(ID). If MAX(ID)-MIN(ID)>=30, delete the row with MIN(ID), then add the new row with ID=MAX(ID)+1. The code would look something like this:

Code: Select all

<?php
$qry_id="SELECT MAX(ID) AS max, MIN(ID) AS min FROM table";
$result=mysql_query($qry_id,$conn);
if(mysql_affected_rows()>0){ // if there are DB records
  $max=mysql_result($result,0);
  $min=mysql_result($result,1);
  $new_max=$max++;
  if($max-$min>=30){
    $sql_del="DELETE FROM table WHERE ID=$min)";
    mysql_query($sql_del,$conn) or die("delete failed");
  }
}
else{ // no records (first row being created)
  $new_max=1;
}

$sql_add="INSERT INTO table(ID, fileld1, field2) VALUES($new_max,$field1,$field2)";
mysql_query($sql_add,$conn) or die("insert failed");
?>
WARNING: I came up with this off the top of my head while suffering brain frag from work. The theory should be sound, but I am not promising anything.

Posted: Wed Jan 08, 2003 10:16 am
by DaiWelsh
Or you could add a timestamp column, then query ids from table order by timestamp desc, limit 0,30. If you get 30 rows back then delete all rows with ids not among those top 30. This is a little more robust if other bits of code/people might be updating deleting the table elsewhere.

HTH,

Dai