PHP ~ Column count doesn't match value count at row 1

Question

I'm creating a registration and I'm using just straight PHP not JavaScript to send my form to the MySQL database, so everything is working fine, no syntax error or anything but I fill out all my information and click 'Register' and it returns a message saying 'Column count doesn't match value count at row 1'.

I'm only 14 so this is pretty confusing for me does anyone have a solution?

This is my INSERT INTO code:

 $sql = mysql_query("INSERT INTO users(firstname, lastname, email, password, day, month, year, gender) 
 VALUES('$firstname','$lastname','$email','$db_password','$day','$month','$year')")  
 or die (mysql_error());

Probably a result of an `INSERT` statement having more or fewer values inside the `VALUES()` than listed in the columns `()`. Post your code and we'll have a look. — Michael Berkowski, Aug 16 '12 at 14:17
It most likely means that the amount of values do not match up to the amount of columns (ie. `INSERT INTO tbl (val1) VALUES (1, 2)`). If you are not specifying which columns to insert into, you should do so. — Kermit, Aug 16 '12 at 14:19
There are many duplicates for this question. Did you search on Stackoverflow before asking? — Jocelyn, Aug 16 '12 at 14:29
possible duplicate of [Column count doesn't match value count at row 1](http://stackoverflow.com/questions/5931900/column-count-doesnt-match-value-count-at-row-1) — KingCrunch, Aug 17 '12 at 14:17

score 24 · Accepted Answer · answered Aug 16 '12 at 14:27

You are trying to insert 7 values into 8 columns - you are missing the insertion of gender.

The correct code would be:

$sql = mysql_query("INSERT INTO users(firstname, lastname, email, password, day, month, year, gender) 
VALUES('$firstname','$lastname','$email','$db_password','$day','$month','$year', '$gender')")  
or die (mysql_error());

By the way, if you are not already doing it, I highly recommend escaping the strings first, before passing them to the query like so:

$firstname=mysql_real_escape_string($firstname)

You should do this with all variables above. Here you can find more about the escape function.

Bart Friederichs · Answer 2 · 2012-08-16T15:05:22.540

1

With your code there, I see you forget to insert $gender.

When inserting data into a MySQL table, you will have to specify which data goes into which column. You do this by specifying the column names before the VALUES part:

INSERT INTO tblA (col1, col2) VALUES ('value1','value2');

If you omit that information, MySQL will expect all columns:

If your table is like this:

 CREATE TABLE tblA (
     col1 INT,
     col2 INT 
 );

You can insert information like this:

INSERT INTO tblA VALUES ('value1', 'value2');

If you omit column names and do no specify values for all columns, MySQL will give the "Column count doesn't match value count at row" error will occur, as MySQL doesn't know what to put in the missing columns. With the table structure as above,

INSERT INTO tblA VALUES ('value1');

will result in that error.

In non-strict mode, MySQL will insert default values for omitted column names.

edited Aug 16 '12 at 15:05

answered Aug 16 '12 at 14:25

Bart Friederichs

33,050
15
95
195

Not quite - [Here](http://dev.mysql.com/doc/refman/5.1/en/insert.html) you can find that you do not need to provide data for _every_ column - it can either stay empty or automatically be filled with the default of the column. – Dennis Hackethal Aug 16 '12 at 14:39
@Charles, you're absolutely right, but I tried to not give too much information in my answer. ;) – Bart Friederichs Aug 16 '12 at 14:47
@Charles, only in non-strict mode. I tried it, and I got the error, even with defaults set. – Bart Friederichs Aug 16 '12 at 15:03
ahh that's interesting, thank you! Seems like I have only worked in non-strict mode. – Dennis Hackethal Aug 16 '12 at 15:04

score 0 · Answer 3 · answered Aug 16 '12 at 14:28

0

You have missed a value for gender column (the last one)

answered Aug 16 '12 at 14:28

Mariusz Sakowski

3,232
14
21

score -1 · Answer 4 · answered Aug 16 '12 at 14:31

You are completely missing the gender value:

$sql = mysql_query("INSERT INTO users(firstname, lastname, email, password, day, month, year, gender) 
VALUES('$firstname','$lastname','$email','$db_password','$day','$month','$year', 'gender goes here')") 
or die (mysql_error());

PHP ~ Column count doesn't match value count at row 1

4 Answers4

Linked

Related