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:
limit # of mysql rows
Moderator: General Moderators
- mydimension
- Moderator
- Posts: 531
- Joined: Tue Apr 23, 2002 6:00 pm
- Location: Lowell, MA USA
- Contact:
- daven
- Forum Contributor
- Posts: 332
- Joined: Tue Dec 17, 2002 1:29 pm
- Location: Gaithersburg, MD
- Contact:
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:
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.
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");
?>