Solved

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

Posted on 2014-12-21
4
123 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
[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
  • 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article discusses how to create an extensible mechanism for linked drop downs.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
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 …

635 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