Column count doesn't match value count error

[Aaron C]

Hi,

I'm trying to do an insert with the following statement:

INSERT INTO user VALUES ( 'ag@ag.com','ag ','Aaron','Chan dler','','','La
Mirada','CA',90 638,714,'','');

and I'm getting the error message "Column count doesn't match value
count at row 1".

My table schema is:
Field Type Null Default
------------------------------------------------
email varchar(50) No
password varchar(50) No
f_name varchar(30) No
l_name varchar(40) No
address_1 varchar(50) Yes NULL
address_2 varchar(50) Yes NULL
city varchar(30) No
state char(2) No
zip mediumint(5) No 0
home_area smallint(3) No 0
home_phone varchar(7) Yes NULL
comments text Yes NULL


I have 12 fields in my table (the first being the primary key), and the
syntax seems correct here. Any ideas why this message could be appearing?

Thanks,
Aaron

[Aggro]

Re: Column count doesn't match value count error

Aaron C wrote:
[color=blue]
> I have 12 fields in my table (the first being the primary key), and the
> syntax seems correct here. Any ideas why this message could be appearing?[/color]

I don't understand that sentence. Do you have 12 or do you have 13
fields? I tested your query with 12 fields and it worked fine. If you
have an extra id field in your table, then your query won't work.
Solution in that case might be giving null as a value for that field in
your insert-sentence.

Here's what I tried:

mysql> create table user(
-> email varchar(50),
-> password varchar(50),
-> f_name varchar(30),
-> l_name varchar(40),
-> address_1 varchar(50),
-> address_2 varchar(50),
-> city varchar(30),
-> state char(2),
-> zip mediumint(5),
-> home_area smallint(3),
-> home_phone varchar(7),
-> comments text
-> );
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO user VALUES (
'ag@ag.com','ag ','Aaron','Chan dler','','','La Mirada','CA',90 638,714,'','');
Query OK, 1 row affected (0.00 sec)

[Aaron C]

Re: Column count doesn't match value count error

Alright, I recreated the table in PHPMyAdmin using:

CREATE TABLE `user` (
`email` VARCHAR( 50 ) NOT NULL ,
`password` VARCHAR( 50 ) NOT NULL ,
`f_name` VARCHAR( 30 ) NOT NULL ,
`l_name` VARCHAR( 30 ) NOT NULL ,
`address_1` VARCHAR( 50 ) NOT NULL ,
`address_2` VARCHAR( 50 ) NOT NULL ,
`city` VARCHAR( 30 ) NOT NULL ,
`state` CHAR( 2 ) NOT NULL ,
`zip` MEDIUMINT( 5 ) NOT NULL ,
`home_area` SMALLINT( 3 ) NOT NULL ,
`home_phone` VARCHAR( 8 ) NOT NULL ,
`comments` TEXT NOT NULL
);

not allowing nulls for the time being.

I then executed the following:
$query = "INSERT INTO user VALUES
('x',password(' x'),'x','x','x' ,'x','x','x',11 ,11,'x','x');";
$result = mysql_query($qu ery);
if(!mysql_num_r ows($result)) echo mysql_error();
else echo "Inserted the data!";

There are 12 rows in the table, and 12 arguments passed to the INSERT
command. The types match, but I'm still getting the message "Column
count doesn't match value count at row 1". What is going on here? This
is maddening!

Thanks,
Aaron



Aggro wrote:[color=blue]
> Aaron C wrote:
>[color=green]
>> I have 12 fields in my table (the first being the primary key), and
>> the syntax seems correct here. Any ideas why this message could be
>> appearing?[/color]
>
>
> I don't understand that sentence. Do you have 12 or do you have 13
> fields? I tested your query with 12 fields and it worked fine. If you
> have an extra id field in your table, then your query won't work.
> Solution in that case might be giving null as a value for that field in
> your insert-sentence.
>
> Here's what I tried:
>
> mysql> create table user(
> -> email varchar(50),
> -> password varchar(50),
> -> f_name varchar(30),
> -> l_name varchar(40),
> -> address_1 varchar(50),
> -> address_2 varchar(50),
> -> city varchar(30),
> -> state char(2),
> -> zip mediumint(5),
> -> home_area smallint(3),
> -> home_phone varchar(7),
> -> comments text
> -> );
> Query OK, 0 rows affected (0.03 sec)
>
> mysql> INSERT INTO user VALUES (
> 'ag@ag.com','ag ','Aaron','Chan dler','','','La
> Mirada','CA',90 638,714,'','');
> Query OK, 1 row affected (0.00 sec)[/color]

[Bill Karwin]

Re: Column count doesn't match value count error

Aaron C wrote:[color=blue]
> INSERT INTO user VALUES ( 'ag@ag.com','ag ','Aaron','Chan dler','','','La
> Mirada','CA',90 638,714,'','');[/color]

I generally recommend naming the fields in an insert statement, in
addition to the values. That way if you add a field or change the order
of the fields your INSERT statement won't break. It sounds like exactly
that has happened in your case; there's actually more fields that the 12
values you have provided.

For example:

INSERT INTO user (email, password, f_name, l_name,
address_1, address_2, city, state, zip,
home_area, home_phone, comments)
VALUES ( 'ag@ag.com', 'ag', 'Aaron', 'Chandler',
'', '', 'La Mirada', 'CA',90638,
714, '', '');

Regards,
Bill K.

[Aggro]

Re: Column count doesn't match value count error

Aaron C wrote:[color=blue]
> Alright, I recreated the table in PHPMyAdmin using:
>
> CREATE TABLE `user` ([/color]
[color=blue]
> $query = "INSERT INTO user VALUES[/color]

I tried that at both of my servers, other is 3.x and other is 4.1.7

And they both worked fine with the example queries you provided. But I
used the mysql command line tool, perhaps you should try that also?

But in case you are tired of fighting with this, you could actually try
to do it this way:

INSERT INTO user(field1, field2, ...) VALUES ('value1', 'value2', ...);

So give name for each field you are going to use. If that also fails,
try doing it again, but this time with less fields. Or start with 1
field, and then add 1 after another untill you get error. That might
help locating the problem.

[Aaron C]

Re: Column count doesn't match value count error

Found out what it was a little bit ago...
The database name wasn't being passed properly to my db_connect()
function. Oops!

Anyway, thanks for the help, both of you.

Aaron



Aggro wrote:[color=blue]
> Aaron C wrote:
>[color=green]
>> Alright, I recreated the table in PHPMyAdmin using:
>>
>> CREATE TABLE `user` ([/color]
>
>[color=green]
>> $query = "INSERT INTO user VALUES[/color]
>
>
> I tried that at both of my servers, other is 3.x and other is 4.1.7
>
> And they both worked fine with the example queries you provided. But I
> used the mysql command line tool, perhaps you should try that also?
>
> But in case you are tired of fighting with this, you could actually try
> to do it this way:
>
> INSERT INTO user(field1, field2, ...) VALUES ('value1', 'value2', ...);
>
> So give name for each field you are going to use. If that also fails,
> try doing it again, but this time with less fields. Or start with 1
> field, and then add 1 after another untill you get error. That might
> help locating the problem.[/color]