Limiting a table size

[SkyRocket]

I have a table that I want to have no more than 500 records. This table is ordered by some value.

I want to do something like this:

if ( $NumRecords > 500 )
{
DELETE * FROM $table WHERE ($Value < $table[500].Value )
}

What is the correct syntax for doing this?

thanks in advance.

[mcibor]

Welcome to WebmasterWorld!

<?php

$conn = mysql_connect("host", "user", "pass") or die(mysql_error());
mysql_select_db("db", $conn) or die(mysql_error());
$result = mysql_query("SELECT id FROM table ORDER BY id") or die(mysql_error());
$num = mysql_num_rows($result);

if($num > 500)
{
for($i = $num - 500; $i >= 0; $i--)
{
$answer = mysql_fetch_array($result)
$ids_to_delete .= $answer["id"].",";//they are sorted by creation - the oldest are the first
}

$ids_to_delete = substr($ids_to_delete, 0, -1);// get rid of the last comma
if(mysql_query("DELETE FROM table WHERE id IN ($ids_to_delete)")) echo "Deletion successful!";
else die(mysql_error());
}?>

I hope this cleares it a little
Best regards
Michal Cibor

[SkyRocket]

Thank you!

Worked like a charm.

[mcibor]

I had a very similar problem to your, however I had to select the amount not bigger as stated. It was a heck of a problem, but therefore your code was fairly easy to write.

I'm glad it worked all right!

Hope to hear from you more.
Michal Cibor