CODEWITHSUNDEEP
mysqlsqlselectinsert
Kenneth Chan
Kenneth Chan

Reputation: 530

insert data from select query results and adding a new column

I already read the similar question on the website. but I still not able to find out the problem. I would like to copy data from table "main" to table "metro" and add one more column. but the statment havn't run and no error prompting.

$s_id=crypt($myid);
$result = mysqli_query($link, "INSERT INTO users (username, password, s_id, email, dob) VALUES ('$myid', '$mypw', '$s_id', '$email', STR_TO_DATE('$dob', '%d/%m/%Y'));");
$result = mysqli_query($link, "SELECT user_id FROM users WHERE username='$myid'");
$row = mysqli_fetch_array($result,MYSQLI_BOTH);
$user_id=$row["user_id"];
//below is the statment which can't be perform.
$result = mysqli_query($link, "INSERT INTO metro (ID, Row, Column, Caption, link, Ico, Size, Color, user_id) SELECT ID, Row, Column, Caption, link, Ico, Size, Color, '$user_id' FROM main");

The table "main" only have the field (ID,Row,Column,Caption,link,Ico,Size,Color)
The table "metro" only have the above field and field(user_id) I tried to run the statment
INSERT INTO...
in phpmyadmin the error message show "#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near
'Column, Caption, link, Ico, Size, Color, user_id) SELECT ID, Row, Column, Captio' at line 1"
I wonder if the field name Row and Column is keyword. The color of these two word become red.

Upvotes: 0

Views: 433

Answers (1)

jarlh
jarlh

Reputation: 44805

At least in ANSI SQL row and column are reserved words, so these column names need to be delimited with double quotes as "Row" and "Column":

INSERT INTO metro (ID, "Row", "Column", Caption, link, Ico, Size, Color, user_id)
SELECT ID, "Row", "Column", Caption, link, Ico, Size, Color, '$user_id' FROM main

Perhaps MySQL wants back-ticks instead of double-quotes?

Upvotes: 1

Related Questions