Solved

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

Posted on 2014-12-21
4
109 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 500 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
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 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 …

816 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

10 Experts available now in Live!

Get 1:1 Help Now