How to do a PHP PDO insert

Question

I'm trying to insert in to a database using PDO.

Here is my code:

$SQLinsert = $odb->prepare("INSERT INTO `sites` VALUES(NULL, :site, :username)");
$SQLinsert -> execute(array(':site' => $site, ':username' => $user));

I added the PDO error reporting and I get this error:

Array ( [0] => 00000 [1] => [2] => )

Aren't the field names obligatory? Otherwise: http://stackoverflow.com/questions/3726505/how-to-squeeze-error-message-out-of-pdo — Marcel Korpel, Dec 13 '13 at 16:29
@MarcelKorpel: You can leave out the field names in MySQL. It assumes you are passing in all the fields in the order they are defined in the table. — gen_Eric, Dec 13 '13 at 16:31

score 5 · Accepted Answer · answered Dec 13 '13 at 16:31

PDO doesn't give error messages when the SQL is wrong. You can use errorInfo to get SQL errors:

if ($SQLinsert -> execute(array(':site' => $site, ':username' => $user))) {
    // ok
} else {
    print_r($odb->errorInfo());
}

My guess on your SQL by the way is that you have more columns than those three. If that's the case, add the column names to make it work:

INSERT INTO `sites` (col1, site, username) VALUES(NULL, :site, :username)

score 0 · Answer 2 · answered Dec 13 '13 at 17:27

You can use php try catch block with get_message() method.

  try{ 
        $SQLinsert = $odb -> prepare("INSERT INTO `sites` VALUES(NULL, :site, :username)");
        $SQLinsert -> execute(array(':site' => $site, ':username' => $user));
    } 
    catch(PDOException $exception){ 
        print  $exception->getMessage(); 
    }

Or you need following attribute in your db connection

$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

How to do a PHP PDO insert

2 Answers2