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
410 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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
Creating and Managing Databases with phpMyAdmin in cPanel.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to dynamically set the form action using jQuery.

861 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

23 Experts available now in Live!

Get 1:1 Help Now