Results 1 to 4 of 4

Thread: Moving records from one table to another

  1. #1
    Join Date
    Sep 2008
    Location
    Bristol - UK
    Posts
    842
    Thanks
    32
    Thanked 132 Times in 131 Posts

    Default Moving records from one table to another

    Hi, I'm trying to shift old records into another table (Archive), but I'm not really sure how to go about it. I do have it sort of working but I can foresee problems with it and it just doesn't look good / efficient. Here's what I have:

    PHP Code:
    $archiveq mysql_query("SELECT * FROM enquiries WHERE `id` = '$id'");

    $row mysql_fetch_row($archiveq);
    $rowstring implode($row",");
    $rowstring mysql_real_escape_string($rowstring);
    $row explode(","$rowstring);

    $updatearchive mysql_query("INSERT INTO archive (id, phone_no, boat_type, name, address, start_date, finish_date, deposit, payment_received, description, email) VALUES ('$row[0]', '$row[1]', '$row[2]', '$row[3]', '$row[4]', '$row[5]', '$row[6]', '$row[7]', '$row[8]', '$row[9]', '$row[10]')");        
            if(!
    $updatearchive)
            die(
    'Error inserting into archive: ' mysql_error()); 
    There's only one row to get, as it's done by IDs, so it gets the row as an array, then I chop it up, escape characters like ' which terminate the query string, then put it back together and then do that big ugly statement, putting each row in the correct value slot.

    The problem with this is that if a field has a comma within it, it breaks it up, so another value is added to the array.

    Anyone got any ideas on improving this? I was thinking that INNER JOIN would come into it somewhere but I'm not so great with databases.

    Cheers.
    Last edited by Schmoopy; 04-10-2009 at 01:39 AM. Reason: Solved

  2. #2
    Join Date
    Mar 2009
    Posts
    65
    Thanks
    13
    Thanked 4 Times in 4 Posts

    Default

    Try using mysql_fetch_assoc so that you don't need the extra step of imploding by commas....

    Actually, when you do a fetch, they are already in an array form. There is no need for the additional implode or explode. Just use $row as an array (do a print_r on $row and you'll get what I mean)

  3. #3
    Join Date
    Sep 2008
    Location
    Bristol - UK
    Posts
    842
    Thanks
    32
    Thanked 132 Times in 131 Posts

    Default

    Well I only did the explode and implode so I could do mysql_real_escape_string on the values. If I didn't escape them then a value like "My dog's bone" would terminate the string at "My dog" instead of putting an escape character in, like "My dog\'s bone".

  4. #4
    Join Date
    Sep 2008
    Location
    Bristol - UK
    Posts
    842
    Thanks
    32
    Thanked 132 Times in 131 Posts

    Default

    Found a solution, much much easier and is flexible in case I add / remove fields in the future, here's the SQL if anyone ever needs help with this:

    PHP Code:
    "INSERT INTO targetTable SELECT * FROM oldTable"// Then add a WHERE clause if you need to take data from a specific row

    "INSERT INTO targetTable SELECT * FROM oldTable WHERE `id` = '$id'"

Bookmarks

Posting Permissions

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