Column count doesn't match value count at row 1

[jimmyoneshot]

I am trying to get a php file which copies all the records from one table to another but I get this error when I run it. My query is as follows:-

 

$query = "INSERT INTO userlinks( userid, linkid, categoryid, label, icon, url, favourited ) SELECT linkid, categoryid, label, icon, url FROM links";

 

I think it's to do with the fact that my userlinks table has 2 more columns (userid and favourited) than the links table but into these two columns I simply want to insert the value '1' for userid and 'N' for favourited for each record that gets copied over from the links table. How can I edit my query to do this so it doesn't return this error?  :-\

[aeroswat]

I've never done this before but I saw this while searching. Maybe it'll work

 

$query = "INSERT INTO userlinks( userid, linkid, categoryid, label, icon, url, favourited ) SELECT "1", linkid, categoryid, label, icon, url, "N" FROM links";

[jimmyoneshot]

That makes sense but when I run it it returns the error "syntax error, unexpected T_LNUMBER " pointing to the line with that query on. Thanks for your help so far.

[Ken2k7]

aeroswat, I suggest using the preview button.

 

jimmyoneshot, replace the double quotes surrounding 1 and N to single quotes.

[jimmyoneshot]

Thanks Ken that works. I've modified to include $param_userid which will be dynamic based on what user is logged in i.e. $param_userid is the userid of the user that is currently logged in.

 

Is there any way I can modify this query so that if the records exist FOR that logged in specific user it will update all of these records with the new ones from the links table as well as add in any possible new ones that my client may have add to the links table?

 

Also I've noticed if I run the query it does the insert correctly but if I then go to the userlinks table in mysql and delete all the records and run it again it doesn't. Strange.

 

$query = "INSERT INTO userlinks( userid, linkid, categoryid, label, icon, url, favourited ) SELECT '$param_userid', linkid, categoryid, label, icon, url, "N" FROM links";

[Ken2k7]

For the first inquiry, please read up on INSERT ... ON DUPLICATE KEY UPDATE.

 

For the second inquiry, copy and paste the code into MySQL. It should tell you why it failed.