Solved

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

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone 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

Suggested Solutions

Title # Comments Views Activity
Restricting the scope of variables to private in OOP 4 22
What if i make webbased alternative for MS Access 5 71
Inserting data into database 10 36
jQuery force form POST 7 51
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
The viewer will learn how to dynamically set the form action using jQuery.
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 …

820 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