PHP PDO - Multiple Inserts in one PDO Prepare doesn't catch errors

Question

I'm passing multiple INSERT statements in one PDO prepare statement and the execute works; however, if any of the inserts fail the the execute() always returns TRUE and catch() is never invoked.

$query = '
INSERT INTO resources SET
    resource_type_id = :audio_resource_type_id
    , resource_status_id = :resource_status_id
    , is_hosted = :is_hosted
    , category_id = :category_id
    , serve_url = :audio_serve_url
    , title = :title;

SET @audio_id = LAST_INSERT_ID();

INSERT INTO resources SET
    resource_type_id = :thumbnail_resource_type_id
    , resource_status_id = :resource_status_id
    , category_id = :category_id
    , serve_url = :thumbnail_serve_url
    , parent_resource_id = @audio_id;';

if ($audio_duration) {
    $query .= '     
    INSERT INTO audio_duration SET
    audio_id = @audio_id
    , duration_seconds = :audio_duration';
}

try {
    $sth = $dbi->dbh->prepare($query);
    $sth->bindParam('audio_resource_type_id', $this->resource_types['audio']);
    $sth->bindParam('thumbnail_resource_type_id', $this->resource_types['THUMBNAIL']);
    $sth->bindParam('resource_status_id', $resource_status_id);
    $sth->bindParam('category_id', $this->category_id);
    $sth->bindParam('audio_serve_url', $audio_serve_url);
    $sth->bindParam('thumbnail_serve_url', $thumbnail_serve_url);
    $sth->bindParam('title', $title);
    $sth->bindParam('audio_duration', $audio_duration);

    $sth->execute();

} catch (PDOException $e) {
    print "Error!: " . $e->getMessage();
    log($e->getMessage());
    alert($e->getMessage());
    return FALSE;
}

Thank you for any thoughts on this.

Duplicate of [PDO query fails but I can't see any errors. How to get an error message from PDO?](http://stackoverflow.com/questions/15990857/reference-frequently-asked-questions-about-pdo#15990858) — Your Common Sense, Apr 22 '13 at 20:11

score 0 · Answer 1 · answered Apr 22 '13 at 20:03

0

Just want to point out this..

To stop PDO from silently failing, you can set the error mode on the PDO connection.

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

There is also PDO::ERRMODE_WARNING if you want errors but still continue.

answered Apr 22 '13 at 20:03

auicsc

297
1
3
14

I am setting the ERRMODE_EXCEPTION when creating my db connection. Even the $return from execute() returns TRUE; but when I look at the MYSQL logs, I can see the query failed. I am using a database class and the errors are caught on other queries, it's just this one with multiple inserts that's causing me grief. – Kate Apr 22 '13 at 20:20
Any reason you are running all your queries in 1 statement? – Your Common Sense Apr 22 '13 at 20:21
High traffic site and I'm trying to reduce the amount of times I'm communicating with the db. – Kate Apr 22 '13 at 20:24
@Kate this amount means nothing compared to really slow things. – Your Common Sense Apr 22 '13 at 20:33
If it was passed only a couple of times I would agree; but, what if it's passed a couple thousand times every hour, also, keeping in mind there are other queries going through as well. – Kate Apr 22 '13 at 20:39

PHP PDO - Multiple Inserts in one PDO Prepare doesn't catch errors

1 Answers1