Solved

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

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
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…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
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 …

752 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