Page 2 of 2

Re: MySQL (Auto Delete Old Rows)

Posted: Fri Dec 09, 2011 11:17 am
by pickle
Every time you insert a row into a table with an auto_increment primary key, the mysql connection remembers what the newly created row's ID is. How you get that depends on the library you're using, but with MySQLi it's just stored in the mysqli::$insert_id property & doesn't require another query to retrieve.

Once you have that id, just do another query to delete everything with an ID smaller than the last inserted ID - 100:

Code: Select all

$query = "INSERT...";
$mysqli->query($query);
$last_insert_id = $mysql->insert_id;

$delete_query = <<<DELETE
DELETE
FROM
  `table`
WHERE
  `id` > $last_insert_id - 100
DELETE;
$mysqli->query($delete_query);
With all that said, you/we might be going about this all wrong. Is the point of this just to have a table that stores the last time someone logged in? If so, you should be using a table with the username as the primary key (therefore only ever 1 row per person), and use a REPLACE statement. REPLACE acts like an INSERT if there is no match in the primary key (ie: if the user has never logged in before), but acts like an UPDATE if there is a match.