Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Transitioning to PDO/MySQL.  Need help  with INSERT statement.

Posted on 2014-12-21
4
Medium Priority
?
131 Views
Last Modified: 2014-12-21
I am having an issue with this insert statement.    Do I have the params set up right?   How do I handle a DUPLICATE KEY?

I am getting the following error:
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

$query4 = $conn->query('SELECT subcatidexp1, catidexp1 FROM download');

while ($row4 = $query4->fetch(PDO::FETCH_ASSOC))
{

if ($row4['subcatidexp1']==0) { 
continue; 
} else if ($row4['subcatidexp1']=="") { 
continue; 
} else {



$statement = $conn->prepare('INSERT INTO category (category_id,parent_id) VALUES(:catid,:parentid)');
$params = array(':catid'=>$row4['subcatidexp1']);
$params = array(':parentid'=>$row4['catidexp1']);
$statement->execute($params); 


}
}

Open in new window

0
Comment
Question by:lawrence_dev
  • 3
4 Comments
 
LVL 58

Accepted Solution

by:
Gary earned 2000 total points
ID: 40512275
You are overriding your array (resetting), assign your placeholders in the same array (I've also moved your prepare statement outside the loop - repeatedly setting the prepare statement just slows your code and insert down)

$query4 = $conn->query('SELECT subcatidexp1, catidexp1 FROM download');
$statement = $conn->prepare('INSERT INTO category (category_id,parent_id) VALUES(:catid,:parentid)');

while ($row4 = $query4->fetch(PDO::FETCH_ASSOC)){
	if ($row4['subcatidexp1']==0) { 
		continue; 
	} elseif ($row4['subcatidexp1']=="") { 
		continue;
	} else {
		$params = array(':catid'=>$row4['subcatidexp1'],':parentid'=>$row4['catidexp1']);
		$statement->execute($params); 
	}
}

Open in new window

0
 
LVL 58

Expert Comment

by:Gary
ID: 40512277
Duplicate key is an whole other ball park
You need to look at ON DUPLICATE KEY
But since this question isn't really about that I will leave it for the next question ;o)
0
 

Author Closing Comment

by:lawrence_dev
ID: 40512284
Thanks Gary.  Beginning to understand this!  It is a little different than previous PHP/MySQL,  Thanks again for your help!
0
 
LVL 58

Expert Comment

by:Gary
ID: 40512289
It's different but better and if you ever move to a different DB you can use the same code logic for MS-SQL etc.

Took me a week or two to get my head around everything. But was glad I went the PDO route in the end - research it online and you'll find PDO is the preferred method.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
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.
Suggested Courses

963 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