Solved

PDO mismatched bound columns

Posted on 2014-04-24
18
396 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 110

Expert Comment

by:Ray Paseur
ID: 40021742
In this part:

... :ean, 2, :mpn, ...
What does the "2" signify?
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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 56

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 110

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 110

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
 
LVL 56

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 56

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 110

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 110

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 110

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
The viewer will learn how to dynamically set the form action using jQuery.
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…

696 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