...

View Full Version : How to move MYSQL row to another table using PHP



m002.p
06-18-2010, 11:51 AM
Hi everyone,

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.

So this is what I have formulated to no success.

Any ideas anyone?


// Update & Archive old logs over 10 minutes

// Check for outdated entries
$query_b = mysql_query("SELECT * FROM server_players WHERE time < DATE_SUB(NOW(), INTERVAL 10 MINUTE)") or die(mysql_error());

while($row_b = mysql_fetch_array($query_b))
{
$ip = split_ip($row_b['ip']);
$querya = mysql_fetch_array(mysql_query("SELECT ip FROM archive_table WHERE ip LIKE '" . $ip[0] . "." . $ip[1] . "." . $ip[2] . ".%'"));

// Check for existing entries
if (empty($querya))
{
mysql_query("INSERT INTO archive_table (name,ip,servers,time) values ('".$row_b['name']."', '".$row_b['ip']."', '".$row_b['server']."', '".$row_b['time']."')") or die(mysql_error());
}
else
{
mysql_query("UPDATE archive_table SET name='".$row_b['name']."', ip='".$row_b['ip']."', time=NOW()");
}
}

//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 :


delimiter |

CREATE TRIGGER outdated AFTER INSERT ON archive_table
DELETE FROM server_players WHERE ip= NEW.ip;
END;
|

delimiter ;

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)

Because i saq split_ip function in your script, just for clarity POSIX Regex Functions :
- ereg_ replace
- ereg
- eregi_ replace
- eregi
- split
- spliti
- sql_ regcase
have been DEPRECATED as of PHP 5.3.0.
Relying on this features is highly discouraged.

m002.p
06-18-2010, 11:31 PM
Thanks for the suggestions guys.

However, I would prefer to use my method put forward as it will update even partial IPs.

Can anyone spot my problem with the code?



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum