PHP SQL Query not working after I add a few more columns to insert into

Question

I'm new here so let me know if I'm doing something wrong or am supposed to do something that I wasn't aware of!

My question: I am running an sql query through PHP and everything is fine. It's the following query:

$query = "INSERT INTO products ( name, type, brand, price, discount, dateAdded, categories, status, description, code, weight, insurance, frame, fork, transmission, brakes, isDiscount, isPromo, gender ) VALUES ( '{$name}' , '{$sub}' , '{$status}' , '{$brand}' , '{$price}' , '{$discount}' , NOW() , '{$category}' , '{$status}' , '{$description}' , '{$id}' , '{$weight}' , '{$insurance}' , '{$frame}' , '{$fork}' , '{$transmission}' , '{$brakes}' , '{$is_discount}' , '{$promo}' , '{$gender}' )";

But now, I added a few more columns to it and it stopped working... Does anyone see whatever it is that I'm missing? The new query is:

$query = "INSERT INTO products ( name, type, brand, price, discount, dateAdded, categories, status, description, code, weight, insurance, frame, fork, transmission, brakes, isDiscount, isPromo, gender, frontlight, backlight, stem, seatpost, lock, year ) VALUES ( '{$name}' , '{$sub}' , '{$status}' , '{$brand}' , '{$price}' , '{$discount}' , NOW() , '{$category}' , '{$status}' , '{$description}' , '{$id}' , '{$weight}' , '{$insurance}' , '{$frame}' , '{$fork}' , '{$transmission}' , '{$brakes}' , '{$is_discount}' , '{$promo}' , '{$gender}' , '{$front}' , '{$back}' , '{$stem}' , '{$seat}' , '{$lock}' , '{$year}' )";

It's really weird, since there's really no difference aside from a few extra columns to insert into! Thanks in advance!!

EDIT: I can't upvote yet because I don't have enough reputation. So, I would like to but can't, sorry! From what I've seen on stackoverflow you guys really appreciate the upvotes and I can understand that :)

EDIT: Thanks a lot everyone. I'm still new to MySQL and wasn't aware of lock being a reserved word. I changed that field's name and now everything works fine. What's the best way for me to give credit to everyone who helped??

The error description states: 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 'lock, year ) VALUES ( 'test' , 'test' , 'test' , '0' , '0' , NOW() , 'test' , 'o' at line 1 — Galadre
– Galadre, Commented Jun 20, 2012 at 10:33

juergen d · Accepted Answer · 2012-06-20 10:33:08Z

4

You have to escape lock since it is a reserved word:

INSERT INTO ... `lock`, year) VALUES (...)

And as in Marco's answer stated: You have more fields in your VALUES brackets than you define columns to insert. The error is here:

INSERT INTO products (name, type, ... ) 
VALUES ( '{$name}' , '{$sub}' , '{$status}', ...)";

Either you need to add another column name before or after type or remove one from your values list: either sub or status

edited Jun 20, 2012 at 10:33

answered Jun 20, 2012 at 10:25

juergen d

205k40 gold badges305 silver badges377 bronze badges

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

3 Comments

Galadre Over a year ago

I tried escaping it and it still doesn't work. But if I revert back to the initial query (without frontlight, backlight, stem, seatpost, lock and year) it does work... I fixed the amount of fields and values, they match again. Nice catch on that one.

juergen d Over a year ago

Can you put your table create script and the insert statement with values on SQLFiddle? Then I can check it.

Galadre Over a year ago

Thanks a lot juergen. I was creating the script on SQLFiddle but before I was done I tried it with a different field name for lock and now everything works fine.

Marco · Accepted Answer · 2012-06-20 10:34:35Z

3

First problem: you have 25 fields in INSERT INTO and 26 in VALUES.
Second: every field is included in ': are you sure? For example year should be an integer in my mind but you enclose it with '.
Third: there is some field name that is a reserved-word. You can take a look at MySql reserved words link

edited Jun 20, 2012 at 10:34

answered Jun 20, 2012 at 10:25

Marco

57.7k15 gold badges135 silver badges159 bronze badges

8 Comments

Cranio Over a year ago

If there is an auto increment, first problem should not be an issue.

Cranio Over a year ago

Don't understand secondo problem, btw.

Marco Over a year ago

@Cranio: yes, there is a problem, because you should pass a NULL for that!! If you declare 25 fields, you must pass 25 values!!

Marco Over a year ago

@Cranio: second problem is OP is enclosing integers in quotes... year in my mind is an int, not a string...

Cranio Over a year ago

There's nothing syntactically wrong in including integers in quotes, as far as we have a numeric field in MySQL.

|

acqu13sce · Accepted Answer · 2012-06-20 10:26:26Z

2

The colum list has 25 items but the values list has 26

I think it's the status field in the values list that isn't needed

answered Jun 20, 2012 at 10:26

acqu13sce

3,8194 gold badges27 silver badges32 bronze badges

Comments

Aelios · Accepted Answer · 2012-06-20 11:21:33Z

You forget sub field :

name, 
sub,
type, 
brand, 
price, 
discount, 
dateAdded, 
categories,
status, 
description, 
code, 
weight, 
insurance, 
frame, 
fork, 
transmission, 
brakes, 
isDiscount, 
isPromo, 
gender, 
frontlight, 
backlight, 
stem, 
seatpost, 
lock, 
year

'{$name}' , 
'{$sub}' , 
'{$status}' , 
'{$brand}' , 
'{$price}' , 
'{$discount}' , 
NOW() , 
'{$category}' ,
'{$status}' , 
'{$description}' , 
'{$id}' , 
'{$weight}' , 
'{$insurance}' , 
'{$frame}' , 
'{$fork}' , 
'{$transmission}' , 
'{$brakes}' , 
'{$is_discount}' , 
'{$promo}' , 
'{$gender}' , 
'{$front}' , 
'{$back}' , 
'{$stem}' , 
'{$seat}' , 
'{$lock}' , 
'{$year}'

your request will be :

$query = "INSERT INTO products ( name, sub, type, brand, price, discount, dateAdded, categories, status, description, code, weight, insurance, frame, fork, transmission, brakes, isDiscount, isPromo, gender, frontlight, backlight, stem, seatpost, lock, year ) VALUES ( '{$name}' , '{$sub}' , '{$status}' , '{$brand}' , '{$price}' , '{$discount}' , NOW() , '{$category}' , '{$status}' , '{$description}' , '{$id}' , '{$weight}' , '{$insurance}' , '{$frame}' , '{$fork}' , '{$transmission}' , '{$brakes}' , '{$is_discount}' , '{$promo}' , '{$gender}' , '{$front}' , '{$back}' , '{$stem}' , '{$seat}' , '{$lock}' , '{$year}' )";

Cranio · Accepted Answer · 2012-06-20 10:25:17Z

0

Maybe because year as a field name is also a keyword?

Try to use backticks like `year`

answered Jun 20, 2012 at 10:25

Cranio

9,8764 gold badges37 silver badges55 bronze badges

2 Comments

Marco Over a year ago

No: year is not a reserved word: take a look here

Galadre Over a year ago

It was lock that's a reserved keyword and causing the problem. However, I did try enclosing lock in backticks and it still gave the error. I changed the field's name now and everything works fine... but still, weird that I had to resort to that.

Collectives™ on Stack Overflow

PHP SQL Query not working after I add a few more columns to insert into

5 Answers 5

3 Comments

8 Comments

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

8 Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related