Improve company productivity with a Business Account.Sign Up


Attempting to get a result set from PHP's mysqli extension using prepared statements with bound parameters but not using bound results.

Posted on 2011-09-22
Medium Priority
Last Modified: 2013-12-13

I'm trying to get a result set as an array of row arrays from PHP using the mysqli extension, with prepared statements and bound parameters.

It seems that I can must use bound results.

The current code (along with an example usage) is
// [snipped]

	 * Result Binder
	 * Due to the inability of the MySQLi function to retrieve result sets from a prepared statement
	 * without using bound results, this method takes a MySQLi_Result and creates an array of bound results,
	 * allowing MySQLi::fetch() to retrieve a single set of results.
	 * @param MySQLi_Stmt $o_Stmt    The MySQLi_STMT to which we need to bind our results.
	 * @param array       $a_Result  The array that will be populated in response to MySQLi::fetch()
	 * @param array       $a_Columns The array of column names if required.
	 * @TODO Maybe drop mysqli and use PDO! Or create a more solid extension to the MySQLi_STMT class, overloading
	 *       the MySQLi_STMT::execute() method to automatically include this behaviour.
	protected function result_binder(MySQLi_Stmt $o_Stmt, array &$a_Result, array &$a_Columns = Null)
		// Retrieve the metadata.
		$o_MetaData = $o_Stmt->result_metadata();

		// Bind the fields to the $a_Result array.
		$a_MappedFields = array();
				use($o_Stmt, &$a_Result, &$a_MappedFields)
				// Create placeholder for field.
				$a_Result[$o_Field->name] = Null;
				$a_MappedFields[$o_Field->name] = &$a_Result[$o_Field->name];

		// Bind the mapped fields to the result.
		if (count($a_MappedFields) > 0)
			call_user_func_array(array($o_Stmt, 'bind_result'), $a_MappedFields);

			// Return the columns if asked for by having a third parameter.
			if (func_num_args() >= 3)
				$a_Columns = array_keys($a_MappedFields);

	 * Generic handler for prepared statements for stored procedures with bound parameters and automated bound results.
	 * @param string $s_Query       The parameterised query to be executed.
	 * @param string $s_Types       The types for the bound parameters.
	 * @param array  $a_BoundParams An array containing the &$a_Variables to be bound.
	 * @result array The results of the stored procedure.
	protected function handleStoredProcedure($s_Query, $s_Types, array $a_BoundParams)
		$a_Results = $a_SingleRow = array();

		// Prepare the query.
		$o_Stmt = $this->db_read->prepare($s_Query);

		// Add the types to the top of the list for the bind_param call.
		array_unshift($a_BoundParams, $s_Types);
		// Bind the parameters to the prepared query.
		call_user_func_array(array($o_Stmt, 'bind_param'), $a_BoundParams);

		// Execute the bound and prepared statement.
		// Store the results as we want to know if we have any rows to process.

		// Assuming we do, call the result binder and iterate the results.
		if (!!$o_Stmt && $o_Stmt->num_rows > 0)
			// If the columns are required then add $a_Columns as the third parameter.
			$this->result_binder($o_Stmt, $a_SingleRow);
			// Whilst we have a row of data.
				// Add the de-referenced row to the results.
				// This line looks odd, but because MySQLi_STMT::fetch() requires bound results
				// and that this is a generic function, rather than having discrete variables,
				// I've used an array containined references to the bound results.
				// The serialize() call de-references the variables and contains just the values.
				// Unserializing it again, turns it back into a standard array containing unreferenced
				// variables.
				$a_Results[] = unserialize(serialize($a_SingleRow));


		return $a_Results;

	 * Get the data for a single badge
	 * Calls the following stored procedure: {@example Test001.sp_GetSingleBadgeData.sql}
	 * @param int $i_BadgeID The required badge's ID.
	 * @return array The badge data for the required ID. If there is no data, then an empty array is returned.
	public function getBadgeData($i_BadgeID)
		$a_Result = $this->handleStoredProcedure
			'CALL `xxxxxdb`.`sp_GetSingleBadgeData`(?)',

		return isset($a_Result[0]) ? $a_Result[0] : array();

Open in new window

What I want to achieve is to be able to use prepared queries to stored procedures which return a result set.

At this stage the SPs only return a single result set and that is probably going to remain the case for the time being.


Question by:Richard Quadling
  • 2
LVL 31

Expert Comment

by:Olaf Doschke
ID: 36595210
Wonder about php as language of stored procs, that's not possible. But in regard to building an array of a resultset this is a sample function I use:

	public function getUsers()
		$users = array();

		$user = array
			( 'id'	     => 0
			, 'name'        => ''
			, 'mailadress' => ''

		if ($stmt = $this->prepare('SELECT id, name, mailadress FROM users'))
			if ($stmt->num_rows>0)
				$stmt->bind_result($user['id'], $user['name'], $user['mailadress']);
				while ($stmt->fetch())
		return $users;

Open in new window

This method is part of a class derived form mysqli (public class myclass extends mysqli), so $this in the code refers to an instance having inherited mysqli methods.

The essential part is extending the result array $users via the line $users[]=unserialize(serialize($user));

As you can see, $user is an array of a single record bound to by the bind_result() method. I don't know where I picked up the serialize/unserialize, but without it I got the same last record repeated, as without this references are stored in the $users array instead of values, so that in the end without this, the $users array would be filled with repeated values of the last record as each array item of $users references the same $user array items as they were filled with the last fetch().

Bye, Olaf.
LVL 40

Author Comment

by:Richard Quadling
ID: 36595932
You are doing exactly the same technique as I am. Bound results with unserialize(serialize()) to get around the byRef variables.

Stored Procedures are a DB issue. CALL or EXEC (depending upon the SQL server - mysql / mssql / etc.) is fine.

But wanting to promote a prepared statement's response to a result set doesn't seem to be possible - which is stupid IMHO.

I suspect the answer to my issue is that it cannot be done with mysqli.

And using PDO is the way to go.
LVL 31

Accepted Solution

Olaf Doschke earned 2000 total points
ID: 36710682
I see, bind_result() is just binding rowwise, yes. But no fetch funcction or method does fetch a whole resultset in PHP, if I don't overlook somthing there. PDOStatement.fetchall() is doing so.

But that difference does not depend on prepared statements and or stored procs at all. I doubt it will do much of a performance difference, if you need a loop to fetch rows or get all rows at once. Of course it's allowing shorter code.

Bye, Olaf.

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
In this article, we’ll look at how to deploy ProxySQL.
The viewer will learn how to dynamically set the form action using jQuery.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

579 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