Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New Coder
    Join Date
    Apr 2010
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to move MYSQL row to another table using PHP

    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?

    Code:
    // 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)");

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Quote Originally Posted by m002.p View Post
    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.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #3
    New Coder
    Join Date
    Jun 2010
    Posts
    20
    Thanks
    0
    Thanked 1 Time in 1 Post
    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 to delete outdated rows in server_players :
    Code:
    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

    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. | 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.
    Nothing's imposible imagination is everything!
    Database Benchmark Software (GNU GPL) | world's fastest database

  • #4
    New Coder
    Join Date
    Apr 2010
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •