MySQLI nested Prepared Statements

Codes here !

Moderators: macek, egami, gesf

Post Reply
User avatar
Nullsig
php-forum Fan User
php-forum Fan User
Posts: 981
Joined: Thu Feb 17, 2011 6:52 am
Location: Racine, WI

MySQLI nested Prepared Statements

Post by Nullsig » Tue Jul 17, 2012 7:58 am

Is there a way to nest prepared statements without pulling the entire result set from the higher order queries into memory on the PHP server?

Basically this is what I am doing:

Code: Select all

$stateStmt->bind_param('i',$repID);
if($stateStmt->execute()){
	$stateStmt->bind_result($currentState);
	while($stateStmt->fetch()){
		$stateUsed = false; //flag since we only echo out the state once
		
		$arg3 = $currentState . "%";
		
		//pull a list of cities for the current state
		$cityStmt->bind_param('is',$repID,$arg3);
		if($cityStmt->execute()){
			$cityStmt->bind_result($igcode,$currentCity);
			while($cityStmt->fetch()){
				$cityUsed = false; //flag since we only echo out the city once.
The $stateStmt query and loop execute correctly. As soon as I go to execute the second statement "$cityStmt->execute()" I get a "Commands out of Sync" error.

User avatar
Nullsig
php-forum Fan User
php-forum Fan User
Posts: 981
Joined: Thu Feb 17, 2011 6:52 am
Location: Racine, WI

Re: MySQLI nested Prepared Statements

Post by Nullsig » Tue Jul 17, 2012 8:06 am

Answered my own question.

After the each prepared statement execution before the bind result (preferably) you need to use $stmt->store_result();

Post Reply

Who is online

Users browsing this forum: Bing [Bot], Google [Bot] and 1 guest