How to INSERT and UPDATE with PDO in one query?

Question

I have the following INSERT snippet that works fine:

        $sql = "INSERT INTO `rating`(`business_id`, `user_id`, `quality`, `service`, `value`) VALUES (?,?,?,?,?)";
        $query = $dbh->prepare($sql);
        $query->bindParam(1, $business_id, PDO::PARAM_STR, 255);
        $query->bindParam(2, $user_id, PDO::PARAM_STR, 255);
        $query->bindParam(3, $quality, PDO::PARAM_STR, 255);
        $query->bindParam(4, $service, PDO::PARAM_STR, 255);
        $query->bindParam(5, $value, PDO::PARAM_STR, 255);
        $query->execute();

However I would like to update another table if the insert was successful.

I thought this would do it:

if ($stmt->execute()) {
        $sql      = "UPDATE users SET prestige = prestige + 5";
        $query    = $dbh->prepare($sql);
        $query->execute();
        }

But no luck, can anyone point me in the right direction?

Final Code that works:

        if ($query->execute()) {
            $sql      = "UPDATE user SET prestige = prestige + 250
            WHERE id = {$user_id}";
            $query = $dbh->query($sql);
        }

`if ($query->execute()) {` replacing that from `$query->execute();` that should do it. You will need a `WHERE` clause though. Otherwise, that will update all your rows. Plus doing `$query = $dbh->query($sql);` — Funk Forty Niner, Jan 22 '15 at 17:03
I think this http://stackoverflow.com/questions/6346674/pdo-support-for-multiple-queries-pdo-mysql-pdo-mysqlnd is similar — Dave Goten, Jan 22 '15 at 17:06
You dont need prepare just execute it directly if you dont have params — Mihai, Jan 22 '15 at 17:06
@Mihai Yeah you're right, I just spotted that now. OP just needs to do `$query = $dbh->query($sql);` — Funk Forty Niner, Jan 22 '15 at 17:08
@Mihai OP's not responding, so maybe they've gone out to get us all some lunch. If you're up to giving an answer, be my guest ;-) You've basically set the pace. — Funk Forty Niner, Jan 22 '15 at 17:13
Sorry, internet died on me! @Fred-ii-, so is my original code close? I just need to add a WHERE clause and add `$query = $dbh->query($sql);`? — potts, Jan 23 '15 at 09:59

Mihai · Accepted Answer · 2015-01-23T10:01:00.457

1

if ($stmt->execute()) {
        $sql      = "UPDATE users SET prestige = prestige + 5";
        $query = $dbh->query($sql);

        }

Just use query since you dont need prepared statements.

edited Jan 23 '15 at 10:01

answered Jan 22 '15 at 17:33

Mihai

26,325
7
66
81

1

I'm slightly confused as to what I would include in the foreach section with this... :/ – potts Jan 23 '15 at 10:00
@potts Oops there an UPDATE. – Mihai Jan 23 '15 at 10:00
1

Thank you. I've updated the original post with the final answer that works :-) – potts Jan 23 '15 at 10:56

score 0 · Answer 2 · answered Jan 22 '15 at 17:16

execute returns TRUE on success or FALSE on failure. so this code must work. i think your problem is 'if ($stmt->execute())' you should check 'if ($query->execute())'

    $sql = "INSERT INTO `rating`(`business_id`, `user_id`, `quality`, `service`, `value`) VALUES (?,?,?,?,?)";
    $query = $dbh->prepare($sql);
    $query->bindParam(1, $business_id, PDO::PARAM_STR, 255);
    $query->bindParam(2, $user_id, PDO::PARAM_STR, 255);
    $query->bindParam(3, $quality, PDO::PARAM_STR, 255);
    $query->bindParam(4, $service, PDO::PARAM_STR, 255);
    $query->bindParam(5, $value, PDO::PARAM_STR, 255);

    if ($query->execute()) {
        $sql      = "UPDATE users SET prestige = prestige + 5";
        $query    = $dbh->prepare($sql);
        $query->execute();
    }

How to INSERT and UPDATE with PDO in one query?

2 Answers2