Solved

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
3
404 Views
Last Modified: 2013-12-13
Hello.

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
<?php
// [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();
		array_map
			(
			function($o_Field)
				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];
				},
			$o_MetaData->fetch_fields()
			);

		// 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.
		$o_Stmt->execute();
		
		// Store the results as we want to know if we have any rows to process.
		$o_Stmt->store_result();

		// 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.
			while(!is_null($o_Stmt->fetch()))
				{
				// 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));
				}
			}

		$o_Stmt->close();

		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`(?)',
			'i',
			array
				(
				&$i_BadgeID
				)
			);

		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.

Regards,

Richard.
0
Comment
Question by:RQuadling
  • 2
3 Comments
 
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'))
		{
			$stmt->execute();
			$stmt->store_result();
			if ($stmt->num_rows>0)
			{
				$stmt->bind_result($user['id'], $user['name'], $user['mailadress']);
				while ($stmt->fetch())
				{
					$users[]=unserialize(serialize($user));
				}
			}
			$stmt->free_result();
			$stmt->close();
		}
		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.
0
 
LVL 40

Author Comment

by:RQuadling
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.
0
 
LVL 29

Accepted Solution

by:
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.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The viewer will learn how to count occurrences of each item in an array.
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…

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now