5

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();
}
Taz
  • 3,718
  • 2
  • 37
  • 59
Stucks_
  • 53
  • 1
  • 3
  • And mind you, there is a good reason you don't want to make it work in quotes. How would I get the string `:this_starts_with_a_colon` in the database with a static query otherwise? `HY093` indicates something else wrong, but usually we get a description to go with it and not just the code..... Hmm, have you tried `$DBH->setAttribute(PDO::ATTR_EMULATE_PREPARES,false)`? – Wrikken Jul 04 '12 at 20:18
  • Oh, and if that doesn't help, what does `$STH->debugDumpParams();` say? – Wrikken Jul 04 '12 at 20:21

1 Answers1

5

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.

Stegrex
  • 4,004
  • 1
  • 17
  • 19
  • Well... I'm stumped. I rewrote it all from scratch, first off using bindParam instead of the array, and it works! I then replaced the BindParam's with the array, which worked. But if I strip out the quotes from the old query, so it seemingly matches the new one, it throws the error. Two seemingly identical pieces of code, one works, one doesn't. Weird, there has to be something different, but I'll be damned if I can see it. Anyway, thanks for the help guys. – Stucks_ Jul 05 '12 at 13:07
  • 3
    IIRC, the array keys must include the colon before them to be paired with the placeholders in the query, which is where you might have gone wrong. – Bojangles Jul 07 '12 at 09:15