Binding

Binding

Binding Values, Binding Parameters and Types

execute([...]) binds every value as PDO::PARAM_STR, which is usually fine because MySQL 524 converts '10' to 10. To choose the type, bind explicitly. bindValue() copies a value now, with a type: PARAM_INT, PARAM_STR, PARAM_BOOL, PARAM_NULL or PARAM_LOB (Subsection 4.16.13). bindParam() binds a variable by reference and reads it each time execute() runs, which suits a statement prepared once and executed in a loop:

bindParam reads its variables at every execute()PHP

$st = $pdo->prepare('UPDATE products SET stock = :stock WHERE sku = :sku');
$st->bindParam('stock', $stock, PDO::PARAM_INT);
$st->bindParam('sku', $sku);
foreach (['BK-SQL-02' => 5, 'BK-UX-01' => 9] as $sku => $stock) {
  $st->execute();                              // this iteration's $sku and $stock
}

The reference is also the trap: foreach ($params as $k => $v) $st->bindParam($k, $v); leaves every marker holding the last $v (against sku = :a OR sku = :b it found only the second product), so use bindValue() there. Under emulation the type decides quoting, which is why LIMIT ? works there only with PARAM_INT.

MENU