MySQL error: "Column count doesn't match value count at row 1" - beginner help

Question

basically, php & MySQL being used. I am a beginner.

What I am trying to do is registering a user to my database, so storing the form input to my users_tb.

get this error when I try inserting the values into the form:

"Column count doesn't match value count at row 1"

I thought it was because I wasn't inserting the user_id value (which is auto increment), so I tried inserting '' in my query for the user_id, but still no luck.

here is the query:

$query = "INSERT INTO users_tb (user_id, user_status, user_gender, user_firstname, user_surname, student_number,
    user_email, user_dob, user_name, user_pass) 
    VALUES('','$status','$gender','$firstname','$surname','$hnumber','$dob','$username','$password')";
    mysql_query($query) or die(mysql_error());
    mysql_close();

whether that helps. If you need any other code just say.

just to make sure though, the inserts don't have to be in same order the fields are in the table do they?

many thanks,

Answer 1

You're missing one value.

For queries this long with so many columns (and if you're inserting just one row), I'd suggest using the following INSERT syntax, which is much easier to read and less likely to cause problems.

$query = "INSERT INTO users_tb SET
          user_status    = '". mysql_real_escape_string($status) ."',
          user_gender    = '". mysql_real_escape_string($gender) ."',
          user_firstname = '". mysql_real_escape_string($firstname) ."',
          user_surname   = '". mysql_real_escape_string($surname) ."',
          student_number = '". mysql_real_escape_string($hnumber) ."',
          user_email     = '". mysql_real_escape_string($email) ."',
          user_dob       = '". mysql_real_escape_string($dob) ."',
          user_name      = '". mysql_real_escape_string($username) ."',
          user_pass      = '". mysql_real_escape_string($password) ."'";
mysql_query($query) or die(mysql_error());
mysql_close();