Jump to content


Photo

copy record from table1 to table2


  • Please log in to reply
4 replies to this topic

#1 quasiman

quasiman
  • Members
  • PipPipPip
  • Advanced Member
  • 194 posts
  • LocationPortland, Oregon

Posted 25 July 2006 - 03:20 AM

I'm trying to copy all records in table1 to table2 (then verify and delete from table1, but I haven't gotten that far yet).

But table2 has additional columns, so I must specify each of the columns I want copied. 
Even doing so I'm getting the error "Column count doesn't match value count at row 1".

Here's what I'm trying:
<?php
include('includes/config.inc');

// Make the DB connection
$sql_link = mysql_connect ("$sql_host", "$sql_user", "$sql_pass")
	or exit;
mysql_select_db("$sql_db");
//collect info
$result = mysql_query("SELECT 'id', 'time', 'name', 'address', 'subject_text', 'body_text' FROM table1");
//insert the info into the 2nd table
$move = "INSERT INTO mailbox (id, time, name, address, subject_text, body_text) VALUES ('$result')";
mysql_query($move) or die(mysql_error());
?>

Any help would be greatly appreciated!

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 25 July 2006 - 03:48 AM

Sounds like you want:
INSERT INTO mailbox (id, time, name, address, subject_text, body_text) SELECT 'id', 'time', 'name', 'address', 'subject_text', 'body_text' FROM table1 

Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 quasiman

quasiman
  • Members
  • PipPipPip
  • Advanced Member
  • 194 posts
  • LocationPortland, Oregon

Posted 25 July 2006 - 04:13 AM

do you mean:
$move_message = "INSERT INTO mailbox (id, time, name, address, subject_text, body_text) SELECT 'id', 'time', 'name', 'address', 'subject_text', 'body_text' FROM table1";
mysql_query($move_message) or die(mysql_error());

All this does is insert "id, time, name, address, subject_text, body_text" - not the actual records

#4 king arthur

king arthur
  • Members
  • PipPipPip
  • Advanced Member
  • 335 posts
  • LocationUK HQ

Posted 25 July 2006 - 09:25 AM

Try it without the single quotes. INSERT....SELECT syntax is explained here: http://www.phpfreaks...l#INSERT_SELECT
Sir Isaac Newton said "If I have seen farther, it is by standing on the shoulders of giants". But it is not recorded as to whether he said it before or after he was hit on the head by a falling apple.

#5 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 25 July 2006 - 03:32 PM

Right, no string literals... I just blindly copied & pasted.. my bad.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users