Solved

PDO mismatched bound columns

Posted on 2014-04-24
18
394 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 109

Expert Comment

by:Ray Paseur
ID: 40021742
In this part:

... :ean, 2, :mpn, ...
What does the "2" signify?
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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 55

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 109

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 109

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 55

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 55

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 109

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 109

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 109

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQl Query to find x consecutive Nbrs in a Table 30 95
MVC - procedural PHP 10 34
PHP Curl to output a url 7 46
TCPDF - Create PDF from Form Values and Link to PDF Download 7 24
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to dynamically set the form action using jQuery.

790 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