Solved

PDO mismatched bound columns

Posted on 2014-04-24
18
381 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 51

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 51

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 51

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

760 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

17 Experts available now in Live!

Get 1:1 Help Now