CODEWITHSUNDEEP
phpmysqlpdoinsert
potts
potts

Reputation: 155

How to INSERT and UPDATE with PDO in one query?

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);
        }

Upvotes: 0

Views: 1802

Answers (2)

Mihai
Mihai

Reputation: 26784

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

        }

Just use query since you dont need prepared statements.

Upvotes: 1

Kiyan
Kiyan

Reputation: 2193

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();
    }

Upvotes: 0

Related Questions