PHP Mysql PDO number of bound variables does not match number of tokens [closed]

Question

I've looked around here but can't seem to find an answer to my problem.

This is the first time I've used PDO and so am a complete newbie to it.

I have a load of data split into 2 tables and want to merge them into one, there are other ways of doing this, but without going into the complicated reasons why I am trying to do it this way...

I generate a recordset of the table I want to copy data from

construct my statement

run it in a loop

but I get the following error

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

I've gone through and triple checked I have the same amount of variables so why "tokens don't match I dont know" like I said am very new to this so am probably missing something the pros would consider as obvious.

It's probably worth mentioning that I am not adding to every single column in the table, there are other columns but I have left them out of the prepared statement... Heres my code:

//$dbh = new PDO($hostname_Seriously,  $DB_USER, $DB_PASSWORD);
$dbh = new PDO('mysql:host=localhost;dbname=seriouslysoulful_summers', $username_Seriously, $password_Seriously);
$stmt = $dbh->prepare("INSERT INTO records_rec (oldid_rec, firstname_rec, artist_rec, aside_rec, bside_rec, label_rec, condition_rec, genere_rec, price_rec, collection_rec, active_rec, info_rec, notes_rec, order_rec, alabelimage_rec, blabelimage_rec, asound_rec, bsound_rec, featured_rec, format_rec) 
VALUES (:oldid_rec, :firstname_rec, :artist_rec, :aside_rec, :bside_rec, :label_rec, :condition_rec, :genere_rec, :price_rec, :collection_rec, :active_rec, :info_rec, :notes_rec, :order_rec, :alabelimage_rec, :blabelimage_rec, asound_rec, bsound_rec, :featured_rec, :format_rec)");
$stmt->bindParam(':oldid_rec', $id);
$stmt->bindParam(':firstname_rec', $firstname);
$stmt->bindParam(':artist_rec', $artist);
$stmt->bindParam(':aside_rec',$aside);
$stmt->bindParam(':bside_rec',$bside);
$stmt->bindParam(':label_rec',$label);
$stmt->bindParam(':condition_rec',$condition);
$stmt->bindParam(':genere_rec',$genere);
$stmt->bindParam(':price_rec',$price);
$stmt->bindParam(':collection_rec',$collection);
$stmt->bindParam(':active_rec',$active);
$stmt->bindParam(':info_rec',$info);
$stmt->bindParam(':notes_rec',$notes);
$stmt->bindParam(':order_rec',$order);
$stmt->bindParam(':alabelimage_rec',$alabel);
$stmt->bindParam(':blabelimage_rec',$blabel);
$stmt->bindParam(':asound_rec',$asound);
$stmt->bindParam(':bsound_rec',$bsound);
$stmt->bindParam(':featured_rec',$featured);
$stmt->bindParam(':format_rec',$format);
$reccount = 0;
//do{
$id = $row_rs_original['id_prod'];
$firstname = 
mysql_real_escape_string($row_rs_original['firstname_prod']);
$artist = mysql_real_escape_string($row_rs_original['artist_prod']);
$aside = mysql_real_escape_string($row_rs_original['a_side_prod']);
$bside = mysql_real_escape_string($row_rs_original['b_side_prod']);
$label = mysql_real_escape_string($row_rs_original['label_prod']);
$condition = mysql_real_escape_string($row_rs_original['condition_prod']);
$genere = $row_rs_original['genre_prod'];
$price = $row_rs_original['price_prod'];
$collection = mysql_real_escape_string($row_rs_original['collection_prod']);
$active = $row_rs_original['active_prod'];
$info = mysql_real_escape_string($row_rs_original['info_prod']);
$notes = mysql_real_escape_string($row_rs_original['notes_prod']);
$order = $row_rs_original['order_prod'];
$alabel = mysql_real_escape_string($row_rs_original['labelimage_A_prod']);
$blabel = mysql_real_escape_string($row_rs_original['labelimage_B_prod']);
$asound = mysql_real_escape_string($row_rs_original['soundfile_A_prod']);
$bsound = mysql_real_escape_string($row_rs_original['soundfile_B_prod']);
$featured = $row_rs_original['featured_prod'];
$format = $row_rs_original['format_prod'];

$stmt->execute();

    $reccount = $reccount +1;
//} while ($row_rs_original = mysql_fetch_assoc($rs_original));
echo($reccount." - records added...");

Steve · Accepted Answer · 2012-02-17 16:30:21Z

Looks like Mark Baker already answered your question, but I wanted to add a couple of tips that have helped me out a lot.

PDO Doesn't Need mysql_escape_string
As long as everything going into your query that deals with user input is using a prepared statement (like you are above) you don't need to escape the input with mysql_real_escape_string[1].

// Don't worry about SQL injection since all of the user 
// defined inputs are being escaped by the PDO package
$sql = "INSERT INTO "
     .   "`users` "
     . "SET "
     .   "`name` = :name";

$query = $pdo->prepare($sql);
$query->bindParam(':name', $name);
$query->execute();

But DO be aware that SQL injection is still possible if you aren't binding user input:

// SQL injection can totally happen here
$sql = "INSERT INTO "
     .   "`users` "
     . "SET "
     .   "`name` = $name";

$query = $pdo->prepare($sql);
$query->execute();

[1] http://www.php.net/manual/en/pdo.prepared-statements.php

Try to Make Your SQL as Short as Possible
For simple SQL statements, the shorter it is, the easier it is to maintain and you're less likely to make mistakes. You could use an alternative INSERT syntax[2]:

INSERT INTO 
  `users`
SET
  `name` = 'Steve';

is equivalent to:

INSERT INTO 
  `users`
  (
    `name`
  )
  VALUES
  (
    'Steve'
  );

That means for big statements like yours, you can effectively half its size because you don't need to repeat all of the column names:

$sql  = "INSERT INTO "
      .   "`records_rec` "
      . "SET "
      .   "`oldid_rec`       = :oldid_rec, "
      .   "`firstname_rec`   = :firstname_rec, " 
      .   "`artist_rec`      = :artist_rec, " 
      .   "`aside_rec`       = :aside_rec, "
      .   "`bside_rec`       = :bside_rec, "
      .   "`label_rec`       = :label_rec, "
      .   "`condition_rec`   = :condition_rec, " 
      .   "`genere_rec`      = :genere_rec, "
      .   "`price_rec`       = :price_rec, "
      .   "`collection_rec`  = :collection_rec, "
      .   "`active_rec`      = :active_rec, "
      .   "`info_rec`        = :info_rec, "
      .   "`notes_rec`       = :notes_rec, "
      .   "`order_rec`       = :order_rec, "
      .   "`alabelimage_rec` = :alabelimage_rec, "
      .   "`blabelimage_rec` = :blabelimage_rec, "
      .   "`asound_rec`      = :asound_rec, "
      .   "`bsound_rec`      = :bsound_rec, "
      .   "`featured_rec`    = :featured_rec, "
      .   "`format_rec`      = :format_rec";

$dbh = new PDO(<info goes here>);
$stmt = $dbh->prepare($sql); 

// Bind your params here...

[2] http://dev.mysql.com/doc/refman/5.5/en/insert.html

Do Make Your SQL Statements Multi-line and Pretty

I started formatting my SQL statements to be multi-lined (like above) and ever since I've had WAY fewer errors like this. It does take up a lot of room, but I think in the end it's worth it. By making everything line up, it makes errors stick out like a sore thumb.

Happy coding!

Mark Baker · Accepted Answer · 2012-02-17 15:51:04Z

11

Missing colons

:blabelimage_rec, **:**asound_rec, **:**bsound_rec, :featured_rec, :format_rec

answered Feb 17, 2012 at 15:51

Mark Baker

213k34 gold badges354 silver badges390 bronze badges

Collectives™ on Stack Overflow

PHP Mysql PDO number of bound variables does not match number of tokens [closed]

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related