0

I am attempting to UPDATE records within my MySQL database.

I build the prepared statement using arrays / loops:

$sql =  "UPDATE table01 SET ";  
        foreach($values as $value)
        {
        $sql .="$value = :$value, ";
        }
        $sql = rtrim($sql,', ');    
        $sql .=" WHERE id = '$id'";

then I prepare this statement:

try 
{
$pdo = new PDO('mysql:host=localhost; dbname=db01', $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $pdo->prepare($sql);

Then I bind each of the tokens / placeholders in the prepared statement to the values to be inserted using another loop:

foreach(array_combine($values, $variables) as $value=>$variable)
{
$stmt->bindParam(':$value', $variable);
}

$stmt->execute();

where $values is an array of column headers in the database (therefore ':$value' is a set of token names corresponding to them) and $variables is an array of variables containing data to be stored.

When I run this, I am given the error:

Error: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

But as you can see I'm forming them from arrays - I've done counts on the arrays to ensure the elements match 1:1 and they do:

FYI:

$count1 = count($values);
$count2 = count($variables);

echo $count1;
echo $count2;

gives: 7575

(they both have 75 elements in them)

Cœur
  • 37,241
  • 25
  • 195
  • 267
Gideon
  • 1,878
  • 4
  • 40
  • 71

1 Answers1

2

Single quotes don't interpolate variables.

$stmt->bindParam(':$value', $variable);

should be

$stmt->bindParam(":$value", $variable);
El Yobo
  • 14,823
  • 5
  • 60
  • 78
  • Wow thanks, I guess every day's a school day! Yes in the example I was learning bindParam from they wern't using variables for both - makes sense. Thanks! – Gideon Oct 25 '12 at 00:22
  • @JohnWoo, it's nothing specific to prepared statements; it's just that the name of the variable you're trying to bind will not match if you don't correctly handle the variable. You can use whatever type of quote you want. – El Yobo Oct 25 '12 at 00:24
  • Needless to say @ElYobo this fixed my issue! I'll accept it in 8 mins when it let's me :) You are fast! – Gideon Oct 25 '12 at 00:24