• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 407
  • Last Modified:

PDO mismatched bound columns

I've looked through this 12 times and cannot see it, I am getting a Invalid parameter number: number of bound variables does not match number of tokens

Here's the prepare statement
$statement = $conn->prepare("INSERT INTO products (title, cost, price, rrp, sku, weight, main_image, manufacturer_id, ean, enabled, mpn, product_url, forwho) 
	VALUES (:title, :cost, :price, :rrp, :sku, :weight, :main_image, :manufacturer_id, :ean, 2, :mpn, :product_url, :forwho)
	ON DUPLICATE KEY UPDATE cost=values(cost), price=values(price), rrp=values(rrp), weight=values(weight), main_image=values(main_image), ean=values(ean)");

Open in new window


And here's the execute statement
$statement->execute(array(
	':title' => $product->NAME,
	':cost' =>$cost,
	':price' =>$price,
	':rrp' => $product->RRP,
	':sku' => $product['ITEM'],
	':weight' => $product->WEIGHT,
	':main_image' => "/".substr($product->IMAGE,0,1)."/".substr($product->IMAGE,1,1)."/".$product->IMAGE,
	':manufacturer_id' => $product->MANUFACTURER_ID,
	':ean' => $product->EAN,
	':mpn' => $product->MODEL,
	':product_url' => $product_url,
	':forwho' => $product->FORWHO
));

Open in new window


As far as I can see everything matches up.
0
Gary
Asked:
Gary
  • 7
  • 6
  • 3
  • +1
1 Solution
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
enabled does not have an execute?
0
 
GaryAuthor Commented:
enabled is a set value - there is no bound parameter.
This worked fine previously, just removed a few parameters - don't know what I have screwed up
0
 
Ray PaseurCommented:
In this part:

... :ean, 2, :mpn, ...
What does the "2" signify?
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
GaryAuthor Commented:
The 2 represents the enabled part
Even when I take that bit out (just to be sure even though it worked before) I still get the same error
Just to confirm can you see a difference in what is bound and what is parameterized?

The weird thing is I am getting some new products inserted fine, so I assume that bit is fine and so I assume it is burping out on the duplicate key update. (and this may give me the clue to look at tomorrow when I am sober)
0
 
Julian HansenCommented:
I am confused by this line

:sku' => $product['ITEM'],

Using an object as an array

If I change that to
:sku' => $product->ITEM,

Then the code as posted executes perfectly and record is created.

Naturally I had to create my own Product class and popuplate with data as well as my own Product table - so there might be some discrepencies there. Here is my full code listing
<?php
define('gb_hostname' , 'localhost');
define('gb_user' , 'user');
define('gb_password' , 'password');
define('gb_dbport' , '3306');
define('gb_database' , 'ee');

$dsn = 'mysql:host=' . gb_hostname . ';port=' . gb_dbport . ';dbname=' . gb_database;
$conn = new PDO($dsn, gb_user, gb_password, array( PDO::ATTR_PERSISTENT => false));      
class Product
{
  public $NAME = 'Product Name';
  public $RRP = 'RRP';
  public $WEIGHT = '100';
  public $IMAGE = 'ABCD.JPG';
  public $MANUFACTURER_ID = '999';
  public $EAN = 'EAN';
  public $MODEL = 'ABC123';
  public $FORWHO = 'Client';
  public $ITEM = 'The Item';
}
$product = new Product();
$product_url = 'product.html';
$cost = 10.50;
$price = 15.99;
$statement = $conn->prepare("
  INSERT INTO products (
    title, 
    cost, 
    price, 
    rrp, 
    sku, 
    weight, 
    main_image, 
    manufacturer_id, 
    ean, 
    enabled, 
    mpn, 
    product_url, 
    forwho) 
  VALUES (
    :title, 
    :cost, 
    :price, 
    :rrp, 
    :sku, 
    :weight, 
    :main_image, 
    :manufacturer_id, 
    :ean, 
    2, 
    :mpn, 
    :product_url, 
    :forwho)
  ON DUPLICATE KEY UPDATE cost=values(cost), price=values(price), rrp=values(rrp), weight=values(weight), main_image=values(main_image), ean=values(ean)");

$statement->execute(array(
  ':title' => $product->NAME,
  ':cost' =>$cost,
  ':price' =>$price,
  ':rrp' => $product->RRP,
  ':sku' => $product->ITEM,
  ':weight' => $product->WEIGHT,
  ':main_image' => "/".substr($product->IMAGE,0,1)."/".substr($product->IMAGE,1,1)."/".$product->IMAGE,
  ':manufacturer_id' => $product->MANUFACTURER_ID,
  ':ean' => $product->EAN,
  ':mpn' => $product->MODEL,
  ':product_url' => $product_url,
  ':forwho' => $product->FORWHO
));
?>

Open in new window

Table definition
CREATE TABLE `products` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(20) default NULL,
  `cost` decimal(8,2) default NULL,
  `price` decimal(8,2) default NULL,
  `rrp` varchar(100) default NULL,
  `sku` varchar(20) default NULL,
  `weight` decimal(8,2) default NULL,
  `main_image` varchar(100) default NULL,
  `manufacturer_id` int(11) default NULL,
  `ean` varchar(20) default NULL,
  `enabled` int(11) default NULL,
  `mpn` varchar(20) default NULL,
  `product_url` varchar(20) default NULL,
  `forwho` varchar(20) default NULL,
  PRIMARY KEY  (`id`)
)

Open in new window

0
 
Ray PaseurCommented:
Looking at these lines:

':rrp' => $product->RRP,
':sku' => $product['ITEM'],

I am sure there is some confusion -- $product is either an object or an array, but not both.  This would have been expected to raise a fatal error, as shown in this SSCCE:
http://iconoun.com/demo/temp_garyc123.php

<?php // demo/temp_garyc123.php

/**
 * SEE: http://www.experts-exchange.com/Programming/Languages/Scripting/PHP/Q_28419791.html
 */

// CREATE A PRODUCT OBJECT
$product = new StdClass;
$product->RRP  = 'rrp';
$product->ITEM = 'item';

// USE THE OBJECT IN A MIXED FASHION
$arr = array
( ':rrp' => $product->RRP
, ':sku' => $product['ITEM']
)
;

// SHOW THE DATA CONSTRUCTS
var_dump($product);
var_dump($arr);

Open in new window

If we see no fatal error from PHP, we can believe that there is a chance that the code shown here is not getting executed at all.  You might want to throw in var_dump($product) to check for execution and to visualize the data.
0
 
Ray PaseurCommented:
@julianH: I saw that comment, so I tested the code.  You can test it yourself at the link here:
http://iconoun.com/demo/temp_garyc123.php
0
 
GaryAuthor Commented:
Even when I change it to
':sku' => $product->ITEM

...I get the same error
0
 
Julian HansenCommented:
Weird - do you get the same error running the code I posted earlier?

That worked for me with no errors - if it works for you then we need to look at what is different - if not then it is something environmental.
0
 
GaryAuthor Commented:
Same error.
I'm running through trying to catch the record that causes the error, since it is running through fine to start with, updating/inserting new records correctly- making the error rather unusual
0
 
Julian HansenCommented:
Same error.
Just to clarify - you are getting the error running the code in comment 40022076?

Because that is just a single record?
0
 
GaryAuthor Commented:
Found the problem, I had another prepare being assigned to $statement under certain conditions ergo nullifying the original prepared $statement

D'oh!
0
 
Ray PaseurCommented:
Life among the conditional statements... But I still don't understand why you did not experience a PHP fatal error if your script ever executed the code posted here?
0
 
GaryAuthor Commented:
I remember what happened now...
I used to have the prepare statement inside the loop, so it was getting set on each iteration, realising it was in the wrong place a few days ago I moved it before the loop and hence I hit this error now when trying to update.

Stupid I know!
I have spent hours on this going through the source file checking for invalid syntax, bemusing a seemingly strange error message, saving the file as ANSI, and everything in between!

p.s.
$product['ITEM'] is a node attribute not a value - that is why it is like it is.

p.p.s.
I should have posted the whole code and someone may have seen the error but its about a 1000 lines of code.
0
 
Ray PaseurCommented:
A thousand lines of code is a certain form of code smell.  Might be time to refactor :-)
0
 
GaryAuthor Commented:
That's what I was trying to do ;o)
0
 
Ray PaseurCommented:
Yeah, I get it!  At least we know the code we were looking at was not getting executed.
http://www.experts-exchange.com/Programming/Languages/Scripting/PHP/Q_28419791.html#a40022364

Best regards, ~Ray
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 7
  • 6
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now