Page 1 of 1

The dreaded 1136 mySQL error!

Posted: Thu Jan 27, 2011 4:12 pm
by bertles86
Hi folks,

I've got an INSERT statement which just won't work, I keep getting the dreaded:
ERROR 1136: Column count doesn't match value count at row 45
Now I've checked and triple checked the row in question and the values I'm inserting are exactly the right amount for the table, so I'm at a loss!

Here's the create table:

Code: Select all

CREATE TABLE client (
client_id int(11) NOT NULL auto_increment,
second_name varchar(255) NOT NULL,
first_name varchar(255) NOT NULL,
invoice_address_1 varchar(255) NOT NULL,
invoice_address_2 varchar(255) NOT NULL,
invoice_address_3 varchar(255) NOT NULL,
invoice_town_city varchar(255) NOT NULL,
invoice_postcode varchar(7) NOT NULL,
mobile_number int(20) NOT NULL,
landline_number int(20),
email varchar(255) NOT NULL,
company_name varchar(255) NOT NULL,
notes text(1024),
contact_date date NOT NULL,
PRIMARY KEY (client_id)
);
Row 45 looks like this:

Code: Select all

('','Smith','John','4 Upper Road','','Borham','London','N10 2XX','07771 111 2222','','john.smith@mail.com','N/A','',''),
Obviously not the real data but you get the idea...the ending comma is as there is a row 46 and more.

Any clue at all, what's going on? A bug?

Thanks for any help.

Re: The dreaded 1136 mySQL error!

Posted: Thu Jan 27, 2011 4:30 pm
by John Cartwright
Can you post the entire query please.

Re: The dreaded 1136 mySQL error!

Posted: Thu Jan 27, 2011 4:41 pm
by bertles86

Code: Select all

INSERT INTO client (
client_id,
second_name,
first_name,
invoice_address_1,
invoice_address_2,
invoice_address_3,
invoice_town_city,
invoice_postcode,
primary_number,
alt_number,
email,
company_name,
notes,
contact_date
)
VALUES

('','Steel','Marty','Ashfield Street, 2','','Acton','London','NW4 2XX','07900 111 222','','','N/A','',''),

Re: The dreaded 1136 mySQL error!

Posted: Thu Jan 27, 2011 4:42 pm
by Benjamin
You may not be missing a field. You may be missing a comma or single quote. It's best to write code that will write queries on your behalf based on data you pass them. It's a waste of time to write these long queries, spend time debugging them and modifying them when better solutions exist.

EDIT: You've got an extra ( in there.

Re: The dreaded 1136 mySQL error!

Posted: Thu Jan 27, 2011 4:46 pm
by bertles86
Yup that was typo on me copying over the query to here, it's not in the actual query. What's my best option for populating en-masse then? I've learned SQL originally from books using notepad and the command line...

But in the meantime, any clues on this statement so I can get the table populated initially to test queries?

Re: The dreaded 1136 mySQL error!

Posted: Thu Jan 27, 2011 4:50 pm
by Benjamin
The missing single quotes are on invoice_address_1 and invoice_address_2.

Nevermind, maybe it's the extra , at the end.

Re: The dreaded 1136 mySQL error!

Posted: Thu Jan 27, 2011 4:59 pm
by bertles86
Sorry Benjamin, I'm going mad I can't see the missing quotes on invoice_address_1 and 2...could you bold them for me?

Re: The dreaded 1136 mySQL error!

Posted: Fri Jan 28, 2011 1:31 am
by Weirdan
Stab in the dark: do you escape the variable data going into query? If you don't any data containing single quote would break your query.

Re: The dreaded 1136 mySQL error!

Posted: Fri Jan 28, 2011 1:07 pm
by bertles86
Sorry Weirdan you've lost me! I'm trying to write an input statement into the mySQL command line and my VALUES match the INSERT INTO fields exactly in quantity of columns. Is this some sort of bug in mySQL do you think? I'm by no means a noob at sql but whenever I've encountered this error it was simply a syntax error or the wrong number of columns...please help folks :D

Re: The dreaded 1136 mySQL error!

Posted: Fri Jan 28, 2011 1:21 pm
by John Cartwright
bertles86 wrote:Sorry Weirdan you've lost me! I'm trying to write an input statement into the mySQL command line and my VALUES match the INSERT INTO fields exactly in quantity of columns. Is this some sort of bug in mySQL do you think? I'm by no means a noob at sql but whenever I've encountered this error it was simply a syntax error or the wrong number of columns...please help folks :D
What Weirdan was hinting at is do you pass all your variables through mysql_real_escape_string()? --or-- do you insert the values unescaped?

Re: The dreaded 1136 mySQL error!

Posted: Fri Jan 28, 2011 1:26 pm
by bertles86
Ah ha! No I do not, would that make a difference? That is PHP though no? Why would that affect the SQL statement?

Re: The dreaded 1136 mySQL error!

Posted: Fri Jan 28, 2011 1:37 pm
by John Cartwright
bertles86 wrote:Ah ha! No I do not, would that make a difference? That is PHP though no? Why would that affect the SQL statement?
Let me just repeat what Weirdan said again.
If you don't any data containing single quote would break your query.
... quote being among many characters you should escape by using mysql_real_escape_string(). As a general rule of thumb, all input going to a query needs to pass through mysql_real_escape_string().