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
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
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
  • 2
LVL 29

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 29

Accepted Solution

Olaf Doschke earned 500 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
php error 27 55
MySQL Finding Duplicates in a Normalized Database 6 28
mysql qry 1 22
&& operators not performing required logic 8 13
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

756 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