PHP array to mysql BD... missing array fields

Question

Need your help.

I need to update the array data in the mysql table. my problem is that there are some array values which dont have "photo" coloum (check 4th field in the array) because of that my query is failing with error "Column count doesn't match value count at row 1" below is what im trying.

    $dept = $job->user();
$sql = array(); 
    foreach( $dept as $row ) {
       $sql[] = '('.$row['dob'].', "'.($row['name']).'", "'.($row['role']).'"
 "'.$row['email'].'", "'.($row['photo']).'" )';
    }
    mysql_query('INSERT INTO cast (dob, name, role, email, photo) VALUES '.implode(',', $sql)) or die(mysql_error());

Array Data

array
  0 => 
    array
      'dob' => string '01121978'
      'name' => string 'Ram Avtar'
      'role' => string 'Inspector'
      'email' => string '[email protected]'
      'photo' => string ' '
  1 => 
    array
      'dob' => string '15021978'
      'name' => string 'Suresh Babu'
      'role' => string 'Constable'
      'email' => string '[email protected]'
      'photo' => string ' ' 
  2 => 
    array
      'dob' => string '11111965'
      'name' => string 'Dean'
      'role' => string 'Inspector'
      'email' => string '[email protected]'
      'photo' => string ' '
  3 => 
    array
      'dob' => string '10061979'
      'name' => string 'Rohit Shette'
      'role' => string 'Sergeant'
      'email' => string ' '
      'photo' => string ' '
  4 => 
    array
      'dob' => string '15081979'
      'name' => string 'Ian'
      'role' => string 'warden'
      'email' => string ' '

table structure

 CREATE TABLE user(
      id INT(5) NOT NULL AUTO_INCREMENT,
      dob TEXT NOT NULL,
      name TEXT NOT NULL,
      role TEXT DEFAULT NULL,
      email TEXT DEFAULT NULL,
      photo TEXT DEFAULT NULL
    )
    ENGINE = INNODB
    CHARACTER SET latin1
    COLLATE latin1_swedish_ci;

Answer 1

The actual problem is that you missed a comma.

Change:

echo $sql[] = '('.$row['dob'].', "'.($row['name']).'", "'.($row['role']).'"
  "'.$row['email'].'", "'.($row['photo']).'" )';

To:

$sql[] = "('{$row['dob']}', '{$row['name']}', '{$row['role']}',
 '{$row['email']}', '{$row['photo']}')";                  // ^^^  Here is the missing comma

The missing values will not cause a problem, they will just cause an empty string to be inserted, because the string is quoted. However, you may wish to test to make sure the key exists in the array array before you try to use it, to avoid any nasty E_NOTICEs.

Also, make sure you properly escape your data before you use it in a query - you don't want a visit from Bobby Tables...

Answer 2

First - if that's your real code I think you're missing a comma after $row['role']. Also - why not check if array key exists?

Try something like (not tested, just to give you an idea)

$dept = $job->user();
$sql = array(); 
    foreach( $dept as $row ) {
       echo $sql[] = '('
       . (array_key_exists('dob', $row) ? $row['dob'] : 'null') . ', "'
       . (array_key_exists('name', $row) ? $row['name'] : 'null') . '", "'
       . (array_key_exists('role', $row) ? $row['role'] : 'null') . '", "'
       . (array_key_exists('email', $row) ? $row['email'] : 'null') . '", "'
       . (array_key_exists('photo', $row) ? $row['photo'] : 'null') . '" )';
    }
    mysql_query('INSERT INTO cast (dob, name, role, email, photo) VALUES '.implode(',', $sql)) or die(mysql_error());