limit # of mysql rows

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
stickman373
Forum Commoner
Posts: 30
Joined: Mon Jul 22, 2002 10:26 am

limit # of mysql rows

Post 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:
User avatar
mydimension
Moderator
Posts: 531
Joined: Tue Apr 23, 2002 6:00 pm
Location: Lowell, MA USA
Contact:

Post by mydimension »

let's see what you have so far.
User avatar
daven
Forum Contributor
Posts: 332
Joined: Tue Dec 17, 2002 1:29 pm
Location: Gaithersburg, MD
Contact:

Post 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.
DaiWelsh
Forum Commoner
Posts: 36
Joined: Wed Jan 08, 2003 9:39 am
Location: Derbyshire, UK

Post 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
Post Reply