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());

Answer 1

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.

Answer 2

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.