db_insert() execute() without auto_increment primary key is returning 0

Question

I'm trying to determine whether a database insert query was successful or not by checking the return value of execute(), but it seems to be returning 0 even though I can see the data has been inserted into the database. The table I'm adding data to has a primary key but it's not set to auto increment.

$result = db_insert('my_table')
                    ->fields(array(
                      'user_id' => $user_id,
                      'my_value' => $num,
                    ))
                    ->execute();
print_r($result); //Prints 0, even when successful

How can I check to see if the db_insert() was successful or not if I'm not using an auto increment primary key?

Many thanks

Answer 1

That makes sense - if you haven't got an auto-increment field on the table, then you already have the ID that you're inserting, so it would make no sense for an arbitrary column (or primary key, or whatever) value to be returned by PDO.

If the query fails it will throw an exception. That's the best (probably only) way to check that the insert was successful in your case, short of running a separate query.

try {
  db_insert('...')->execute();
  // No exception thrown; PDO thinks the record was inserted correctly.
}
catch (PDOException $e) {
  // Query failed; recover based on $e->getMessage()
}