MySQL Error: "The number of columns does not match the number of values ​​in row 1" - help for beginners

Question

mainly used php and mysql. I'm a beginner.

What I'm trying to do is register the user in my database in order to save the form input in my user_tb.

get this error when I try to paste the values ​​into the form:

"The number of columns does not match the number of values ​​in row 1"

I thought this was because I did not insert the value of user_id (which is an automatic increment), so I tried to insert '' into my query for user_id, but still no luck.

here is the request:

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

will it help. If you need any other code, just say it.

to make sure the inserts should not be in the same order as the fields in the table?

many thanks,

Answer 1

You are missing one value.

For queries that are long with so many columns (and if you insert only one row), I would 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(); 

Answer 2

You are missing the value for user_email .

 $query = "INSERT INTO users_tb ( user_status, user_gender, user_firstname, user_surname, student_number, user_email, user_dob, user_name, user_pass ) VALUES ( '$status', '$gender', '$firstname', '$surname', '$hnumber', '$email', -- <--- you forgot this! '$dob', '$username', '$password' )"; 

And just a reminder: you should avoid values ​​using mysql_real_escape_string if you are not already doing this.

Answer 3

Mark Byers answered the problem, but did not address this question:

to make sure the inserts should not be in the same order as the fields in the table?

No, they should not be in the same order as the columns in the table, but the list of columns and the list of your values ​​must match both in quantity and in data type. You have a problem that Mark has noticed. you are missing the value for user_email , which means that the dob value dob trying to get into this column instead. MySQL sees that it is not enough for the columns that you specify and report an error to you.

Column count doesn't match value count is actually a pretty clear message, which is unusual for database engines. :)

Answer 4

You can use this simple function to create a request from the $ _POST array and a list of allowed fields:

 function dbSet($fields) { $set=''; foreach ($fields as $field) { if (isset($_POST[$field])) { $set.="`$field`='".mysql_real_escape_string($_POST[$field])."', "; } } return substr($set, 0, -2); } 

used as

 $table = "users_tb"; $fields = explode(" ","user_status user_gender user_firstname user_surname student_number user_email user_dob user_name user_pass"); $query = "INSERT INTO $table SET ".dbSet($fields); mysql_query($query) or trigger_error(mysql_error()." in ".$query); 

of course, it is assumed that the field names of the HTML form correspond to the field names of the SQL table, which is very convenient to consider

Answer 5

you have 10 fields that you want to insert, but you provide only 9 values

Answer 6

You specify 10 columns, but you only have 9 values.