How to add in data in a table that already contains a foreign key

Question

I'm attempting to create a query in phpMyadmin that would allow me to insert a new row of data in my product table, and I am having difficulty doing so because of the foreign key constraint.

I have researched this issue for quite some time and I thought I had the correct syntax down, but I am still getting a generic message saying that there is an issue with my syntax:

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 'British Bus Modelâ€™, ( SELECT productLine FROM productlines WHERE product' at line 3

Below is the code that I am using:

INSERT INTO products (productCode, productName, productLine, productScale, productVendor, productDescription, quantityInStock, buyPrice, MSRP)

VALUES (  ‘S72_3213’ , ‘Double-Decker British Bus Model’, 
( SELECT productLine 
FROM productlines 
WHERE productLine = ‘Double-Decker Buses’)  , 1:33 ,  ‘Classic Bus Models’ , ‘testing’ ,  150,  50.00 ,  90.00  );

I'm basically trying to enter new data for the product except for the productLine which should be pulled from the productlines table. Any advice on what is wrong with my syntax would be much appreciated. Thank you!!

Thanks for the feedback. It seems to be an error specifically for line 3: Forgot to include the message. The error mesage is: #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 'British Bus Modelâ€™, ( SELECT productLine FROM productlines WHERE product' at line 3 — jcoding, Mar 23 '15 at 23:00
Does running `SELECT productLine FROM productlines WHERE productLine = ‘Double-Decker Buses’` by itself work? — legohead, Mar 23 '15 at 23:05
It doesn't. Strange thing is that the attribute 'productLine' , the table 'productlines' and the row data for productLine 'Double-Decker Buses' are all spelled correctly with proper upper/lower cases. — jcoding, Mar 23 '15 at 23:17
Without looking at your tables + data I don't think I can help you there. However once you solve that your original query should work so good luck! — legohead, Mar 23 '15 at 23:19
Based on your error message, it looks like PHP is misrendering your single quotes, causing the syntax error. Maybe some of the info at this question could help: http://stackoverflow.com/questions/1344692/i-need-help-fixing-broken-utf8-encoding — Paul Griffin, Mar 24 '15 at 00:26
Those single quotes--‘S72_3213’--aren't single quotes. Single quotes for SQL string literals look like this--'S72_3213'. — Mike Sherrill 'Cat Recall', Mar 24 '15 at 01:49
What's the point of using this subselect instead of the literal value `'Double-Decker Buses'`: `SELECT productLine FROM productlines WHERE productLine = ‘Double-Decker Buses’`? — Mike Sherrill 'Cat Recall', Mar 24 '15 at 01:52

score 0 · Answer 1 · answered Mar 24 '15 at 02:13

SELECT A FROM TBL WHERE A='xxx'

This is bad SQL, does not make any sense, this is the first problem. Another one is the 1:33....I do not understand what it is. Here is my suggestion

INSERT INTO products (productCode, productName, productLine, productScale, productVendor, productDescription, quantityInStock, buyPrice, MSRP)
    SELECT ‘S72_3213’ , ‘Double-Decker British Bus Model’, productLine, 1:33 ,  ‘Classic Bus Models’ , ‘testing’ ,  150,  50.00 ,  90.00 FROM productlines 
            WHERE OTHER_VAR = ‘Double-Decker Buses’

Will probably also fail because no quotes around `1:33` (and wrong quotes alltogether). Also, what is OTHER_VAR supposed to be? — , Mar 24 '15 at 15:33

score 0 · Answer 2 · answered Mar 24 '15 at 15:23

Other issues aside, as @Mike Sherrill noted, the reason you are getting a syntax error is because your single quotes are not actual single quotes.

Here is a SQLFiddle with a side-by-side comparison. The top query uses the single quotes from your query, which are causing the entire fiddle not to run because the SQL cannot be parsed. The bottom query uses true single quotes, and will run properly if you comment out or remove the top query.

Change your problematic single quotes (‘’) to SQL-appropriate single quotes ('') and you should be able to get past this syntax error.

score 0 · Answer 3 · answered Mar 24 '15 at 15:41

Change your query to

INSERT INTO products 
  (productCode, productName, productLine, productScale, productVendor, productDescription, quantityInStock, buyPrice, MSRP)
VALUES 
  ('S72_3213', 'Double-Decker British Bus Model', 'Double-Decker Buses', '1:33', 'Classic Bus Models', 'testing', 150, 50.00, 90.00);

and you should be good to go. You had the wrong quotes, no quotes around 1:33 and an unneccessary subselect.

If your query works you might consider using a "productlines_id" instead of the name of the productline (and, depending on your db structure a "productvendor_id")

How to add in data in a table that already contains a foreign key

3 Answers3