insert data from select query results and adding a new column

Question

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.

I only found the table "metro" don't update. the webpage don't have any error message. Some post recommend using function bindParm. but I use procedural style. I don't understand how to write procedural style for bindParm. It seem bindParm are very complex. The status: The table "users" can be updated. $row["user_id]" can be assigned. but the table "metro" have no changed. — Kenneth Chan
– Kenneth Chan, Commented Jan 25, 2016 at 11:36

jarlh · Accepted Answer · 2016-01-25 12:28:41Z

1

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?

answered Jan 25, 2016 at 12:28

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Kenneth Chan Over a year ago

I just change the field name "Row" to "myRow" and "Column" to "myCol". but it still not ok. then, I try to run SQL in phpmyAdmin. the error message show ID is unique(since the table "metro" already have data which is same as the main). now I am finding method to cancel "ID" be unique. seem it may be ok after that.

Kenneth Chan Over a year ago

Thanks All. After I delete ID as primary key and index. The INSERT statement can be run.

user2363025 Over a year ago

@jarlh if you get a chance, I am having an issue with an insert query: stackoverflow.com/questions/34989107/…

jarlh Over a year ago

@user2363025, sorry, I don't know php (or whatever it is...).

Collectives™ on Stack Overflow

insert data from select query results and adding a new column

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related