Moving records from one table to another

[Schmoopy]

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:



$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.

[CrazyChop]

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)

[Schmoopy]

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".

[Schmoopy]

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:


"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'";