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

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.
LVL 40
Richard QuadlingSenior Software DeveloperAsked:
Who is Participating?
 
Olaf DoschkeConnect With a Mentor Software DeveloperCommented:
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
 
Olaf DoschkeSoftware DeveloperCommented:
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
 
Richard QuadlingSenior Software DeveloperAuthor Commented:
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
All Courses

From novice to tech pro — start learning today.