PDO Update not replacing the placeholders in a prepared statement

Question

Im trying to update a table with the following code. If I change WHERE temp_booking_id = ':temp_booking_id'"); to use the actual, current session temp_id, the query will run, but adds the placeholders into the table (e.g.: check-out) as the value.

$data is holding the correct values, but is not replacing the placeholders.

Been staring at this for hours and can't for the life of me work out what the problem is, and looked around but haven't found a solution.

PDOStatement:errorInfo() is returning

PDOStatement::errorInfo(): Array ( [0] => 00000 )

and if I remove the inverted commas around the placeholders, it returns

PDOStatement::errorInfo(): Array ( [0] => HY093 )

Any ideas?

try {
  $data = array(
    'temp_booking_id' => $_SESSION['temp_id'],
    'check_in' => $in,
    'check_out' => $out, 
    'adults' => $a,
    'children1' => $c1,
    'children2' => $c2,
    'infants' => $i,
    'cots' => $c,
    'promo_code' => $pc
 );

 $STH = $DBH->prepare("UPDATE b_temp_booking 
   SET check_in = ':check_in',
   check_out = ':check_out',
   adults = ':adults',
   children1 = ':children1',
   children2 = ':children2',
   infants = ':infants',
   cots = ':cots',
   promo_code = ':promo_code' 
   WHERE temp_booking_id = ':temp_booking_id'");

 $STH->execute($data);

 echo "\nPDOStatement::errorInfo():\n";
 $arr = $STH->errorInfo();
 print_r($arr);

} catch(PDOException $e) {
  echo 'ERROR: ' . $e->getMessage();
}

Answer 1

Hmmm, it seems that your SQL statement doesn't require the single quotes. For example, you can try running this block instead:

   $STH = $DBH->prepare("UPDATE b_temp_booking 
   SET check_in = :check_in,
   check_out = :check_out,
   adults = :adults,
   children1 = :children1,
   children2 = :children2,
   infants = :infants,
   cots = :cots,
   promo_code = :promo_code 
   WHERE temp_booking_id = :temp_booking_id");

Check out the PHP manual on PDO prepared statements: http://www.php.net/manual/en/pdo.prepared-statements.php It looks like it here that the quotes are not necessary around the named placeholders.

Also, try following their example where they use the bindParam() method:

$STH->bindParam(':temp_booking_id', $temp_booking_id);

$temp_booking_id = $_SESSION['temp_id']; // Not sure how binding the environment variable will work, so decoupling it.

$STH->bindParam(':check_in', $in);
$STH->bindParam(':check_out', $out); 
$STH->bindParam(':adults', $a);
$STH->bindParam(':children1', $c1);
$STH->bindParam(':children2', $c2);
$STH->bindParam(':infants', $i);
$STH->bindParam(':cots', $c);
$STH->bindParam(':promo_code', $pc);

When you are ready to execute, you can run the following line:

$STH->execute();

Check it out and see if binding the parameters is what you're looking for.