I have been trying this for a while using what I know, but it does not work or simply transfers just one row across, not all of them. It is almost like the loop is not working.

Either way, this is what I have been trying to acomplish:

I have two tables. One contains current data and to optimise it, after 10 minutes I need the outdated rows moved to an archive table to keep queries down on the first table. Of course, you could use "INSERT INTO newtable SELECT * FROM old table" but that does not check and update existing records.

//Remove old entries from first table
mysql_query("DELETE FROM server_players WHERE time < DATE_SUB(NOW(), INTERVAL 10 MINUTE)");

Fou-Lu

06-18-2010, 04:49 PM

Of course, you could use "INSERT INTO newtable SELECT * FROM old table" but that does not check and update existing records.

Since this is MySQL, you could use the REPLACE INTO syntax instead of the INSERT INTO syntax. This will insert records if they don't exist, and update records if they do (based on PK of course). Looks to me like the IP is a unique value, so as long as its either unique or PK, it should replace properly.

saviola

06-18-2010, 05:47 PM

It's more useful to use REPLACE INTO syntax instead of the INSERT INTO syntax. Like Fou-Lu say, this will insert records if they don't exist, and update records if they do (based on PK of course).

You can use MySQL trigger (http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html) to delete outdated rows in server_players :

and a trigger for AFTER UPDATE ON archive_table.
... about trigger for REPLACE INTO event i'm not sure, check MySQL TRIGGER Syntax (http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html)

If you want all this to be repeated over a period of time, you can export this functionality in a CronJob file.
Read this about Cron function. (http://drupal.org/cron) | Introducing Cron (http://articles.sitepoint.com/article/introducing-cron)