[SOLVED] limit mysql table to 100 entries

[tqla]

Hello. Is possible to limit the number of rows in a table to 100?

 

Like, before a new row is added, count the existing rows, if there are already 100, delete row 100 (the oldest row), and add the new row to the top (newest). 

 

???

[ngreenwood6]

You can look into the mysql_num_rows function for that. A basic example would be:

 

$query = "SELECT * FROM table";
$results = mysql_query($query);
$num_rows = mysql_num_rows($results);

if($num_rows == 100)
{
//put delete query here
//put insert query here
}

[Philip]

Or, if you wanted to keep all your data, like me and become an e-packrat. A query would become (no DELETE query required):

 

SELECT * FROM `table` ORDER BY `id` DESC LIMIT 100

 

You wouldn't lose any data - but if your point is to save space, ngreenwood6 is pretty much correct... I would just change it to:

<?php
$query = "SELECT * FROM table";
$results = mysql_query($query);
$num_rows = mysql_num_rows($results);

if($num_rows == 100)
{
    //put delete query here (only deletes if 100 rows were found)
}
//put insert query here (always inserts)
?>

[tqla]

Thanks ngreenwood6! That did it. 

 

e-packrat! lol. Thanks KingPhilip

[ngreenwood6]

king phillip is right put the insert query outside of the "if" statement. Sorry about that its getting late lol

[tqla]

Ah ha. Got it. Thank you both!