PHP, Error 1136 : Column count doesn't match value count at row 1 [duplicate]

This question already has answers here: PHP, MySQL error: Column count doesn't match value count at row 1 (3 answers) Closed 9 years ago.

I get this Exception:

Error 1136 : Column count doesn't match value count at row 1

Structure of the table :

create table gb_entries (
    id int(4) not null auto_increment,
    username varchar(40) not null,
    name varchar(40),
    gender varchar(40),
    dob int(40),
    email varchar(40),
    primary key (id) 
);

With this PHP code:

// Add a new entry to the database
function addEntry($username, $name, $gender, $dob, $email) {
  $connection = mysql_open();
  $insert = "insert into gb_entries " . 
      "values ('$username', '$name',开发者_如何学运维 '$gender', '$dob', '$email')";
  $result = @ mysql_query ($insert, $connection)
      or showerror();
  mysql_close($connection)
      or showerror();
}

// Return an array of database entries that contain $name anad $email
function getEntries($username,$name,$gender,$dob,$email) {
  // Sanitise user input to prevent SQL injection attacks
  $username = mysql_escape_string($username);
  $name = mysql_escape_string($name);
  $gender = mysql_escape_string($gender);
  $dob = mysql_escape_string($dob);
  $email = mysql_escape_string($email);


  // Open connection and select database
  $connection = mysql_open();

  // Construct query
  $query = 
    "select username, name, gender, dob, email from gb_entries where 0=0 ";
  if (! empty($username)) {
      $query .= "AND username LIKE '%$username%' ";
  }
   if (! empty($name)) {
      $query .= "AND name LIKE '%$name%' ";
  }
  if (! empty($gender)) {
      $query .= "AND gender LIKE '%$gender%' ";
  }
  if (! empty($dob)) {
      $query .= "AND dob LIKE '%$dob%' ";
  }
  if (! empty($email)) {
      $query .= "AND email LIKE '%$email%' ";
  }


  $query .= "ORDER BY id";
  // echo $query;

  // Execute query
  $result = @ mysql_query($query, $connection)
      or showerror();

  // Transform the result set to an array (for Smarty)
  $entries = array();
  while ($row = mysql_fetch_array($result)) {
      $entries[] = $row;
  }

  mysql_close($connection)
      or showerror();
  return $entries;
}

What does the Exception mean?

---

As it says, the column count doesn't match the value count. You're providing five values on a six column table. Since you're not providing a value for id, as it's auto increment, it errors out - you need to specify the specific columns you're inserting into:

$insert = "insert into gb_entries (username, name, gender, dob, email) " . 
      "values ('$username', '$name', '$gender', '$dob', '$email')"

Also, I really hate that WHERE 0=0 line. I know why you're doing it that way, but I personally find it cleaner to do something like this (warning: air code!):

$query = "select username, name, gender, dob, email from gb_entries ";

$where = array();
if (! empty($username)) {
      $where[] = "username LIKE '%$username%'"; // add each condition to an array
// repeat for other conditions

// create WHERE clause by combining where clauses, 
// adding ' AND ' between conditions, 
// and append this to the query if there are any conditions 
if (count($where) > 0) {
  $query .= "WHERE " . implode($where, " AND "); 
}

This is personal preference, as the query optimizer would surely strip out the 0=0 on it's own and so it wouldn't have a performance impact, but I just like my SQL to have as few hacks as possible.

---

If the error is occurring when trying to insert a row to your table, try specifying the list of fields, in the insert query -- this way, the number of data in the values clause will match the number of expected columns.

Else, MySQL expects six columns : it expects the id column -- for which you didn't specify a value.

Basically, instead of this :

$insert = "insert into gb_entries " . 
  "values ('$username', '$name', '$gender', '$dob', '$email')";

Use something like that :

$insert = "insert into gb_entries (username, name, gender, dob, email) " . 
  "values ('$username', '$name', '$gender', '$dob', '$email')";

---

I had a similar problem. The column count was correct. the problem was that i was trying to save a String (the value had quotes around it) in an INT field. So your problem is probably coming from the single quotes you have around the '$dob'. I know, the mysql error generated doesn't make sense..

funny thing, I had the same problem again.. and found my own answer here (quite embarrassingly) It's an UNEXPECTED Data problem (sounds like better error msg to me). I really think, that error message should be looked at again

---

Does modifying this line help?

$insert = "insert into gb_entries (username, name, gender, dob, email) " . 
      "values ('$username', '$name', '$gender', '$dob', '$email')";