Solved

PDO mismatched bound columns

Posted on 2014-04-24
18
387 Views
Last Modified: 2014-05-21
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
Comment
Question by:Gary
  • 7
  • 6
  • 3
  • +1
18 Comments
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40021690
enabled does not have an execute?
0
 
LVL 58

Author Comment

by:Gary
ID: 40021700
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
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40021742
In this part:

... :ean, 2, :mpn, ...
What does the "2" signify?
0
 
LVL 58

Author Comment

by:Gary
ID: 40021793
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
 
LVL 52

Expert Comment

by:Julian Hansen
ID: 40022076
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
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40022364
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
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40022406
@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
 
LVL 58

Author Comment

by:Gary
ID: 40022644
Even when I change it to
':sku' => $product->ITEM

...I get the same error
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 52

Expert Comment

by:Julian Hansen
ID: 40022700
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
 
LVL 58

Author Comment

by:Gary
ID: 40022750
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
 
LVL 52

Expert Comment

by:Julian Hansen
ID: 40022816
Same error.
Just to clarify - you are getting the error running the code in comment 40022076?

Because that is just a single record?
0
 
LVL 58

Accepted Solution

by:
Gary earned 0 total points
ID: 40023297
Found the problem, I had another prepare being assigned to $statement under certain conditions ergo nullifying the original prepared $statement

D'oh!
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40023747
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
 
LVL 58

Author Comment

by:Gary
ID: 40023980
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
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40024545
A thousand lines of code is a certain form of code smell.  Might be time to refactor :-)
0
 
LVL 58

Author Comment

by:Gary
ID: 40024583
That's what I was trying to do ;o)
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40029516
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

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now