Run 2 queries using PDO

I have an insert statement on a page like

$r_date = date('Y-m-d', strtotime(str_replace('-', '/', $_POST['r_date'])));
...


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

try {
 
$sql = "INSERT INTO `reviews` (
  `r_date`,
  ...
  ) VALUES (
  :r_date,
  ...)";
  
$stmt = $dbh->prepare($sql);

  
  $stmt->execute(array(
    ':r_date' => $r_date,
    ...
  ));
} catch(PDOException $e) {
  echo 'Error: ' . $e->getMessage();
}

Id like to run another query here (UPDATE providers SET r_count = r_count + 1 WHERE id` = $p_id) in addition to this one, what is the best way to do this?

Well, you only want to add one to the review count once the insertion has successfully happened. There’s two ways to do this securely.

The first is to create a MySQL trigger to automatically increment the review count AFTER a successful insert has happened in the reviews relation. You can read more about MySQL triggers from here (this will require extra privileges though, which may not be an option in a shared hosting environment).

The second is to create a transaction. Transactions with the PDO API is dead simple. It basically involves setting PDO’s error handling to throw exceptions, and then simple invoke the beginTransaction() method to start the transaction, then perform your queries, and then invoke the commit() method. Wrap this in a try…catch block, and if an error occurs in one of your queries, the catch block will be called where you’ll invoke the rollBack() method.

For example:

<?php
// $dbh = new PDO(...);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 
try
{
    $dbh->beginTransaction();
 
    // $dbh->prepare("insert query here...");
    // $dbh->exec("update query here...");
 
    $dbh->commit();
}
catch(PDOException $e) {
    $dbh->rollBack();
}

You can read more about transactions and PDO’s API here.