The dreaded 1136 mySQL error!

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
bertles86
Forum Newbie
Posts: 15
Joined: Wed Jan 26, 2011 3:20 pm

The dreaded 1136 mySQL error!

Post 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.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: The dreaded 1136 mySQL error!

Post by John Cartwright »

Can you post the entire query please.
bertles86
Forum Newbie
Posts: 15
Joined: Wed Jan 26, 2011 3:20 pm

Re: The dreaded 1136 mySQL error!

Post 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','',''),
Last edited by bertles86 on Thu Jan 27, 2011 4:42 pm, edited 1 time in total.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: The dreaded 1136 mySQL error!

Post 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.
bertles86
Forum Newbie
Posts: 15
Joined: Wed Jan 26, 2011 3:20 pm

Re: The dreaded 1136 mySQL error!

Post 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?
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: The dreaded 1136 mySQL error!

Post by Benjamin »

The missing single quotes are on invoice_address_1 and invoice_address_2.

Nevermind, maybe it's the extra , at the end.
bertles86
Forum Newbie
Posts: 15
Joined: Wed Jan 26, 2011 3:20 pm

Re: The dreaded 1136 mySQL error!

Post 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?
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: The dreaded 1136 mySQL error!

Post 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.
bertles86
Forum Newbie
Posts: 15
Joined: Wed Jan 26, 2011 3:20 pm

Re: The dreaded 1136 mySQL error!

Post 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
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: The dreaded 1136 mySQL error!

Post 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?
bertles86
Forum Newbie
Posts: 15
Joined: Wed Jan 26, 2011 3:20 pm

Re: The dreaded 1136 mySQL error!

Post 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?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: The dreaded 1136 mySQL error!

Post 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().
Post Reply