Have you ever been annoyed about the input and output binding that is part of Prepared Statements? Prepared Statements have their room in PHP and MySQL[i] for some good reasons. Though, I recall that I didn’t like the output binding when I tried them for the first time. I wanted the good old mysqli_fetch_assoc() to be available. Last year in November someone, I think it was Lukas (but don’t blame me, if I’m wrong), suggested to implement mysqli_stmt_get_results() with mysqlnd.

By help of the new function, you can create a mysqli_result object from a statement that returns data (SELECT and other – version dependent!). Then you can use the mysqli_result object to process the returned data: fetch results, access meta data – all you can also do using a mysqli_result object returned by mysqli_query().

It’s prepared statements: native types, if possible

But unlike with mysqli_query(), a result set returned by mysqli_stmt_get_result() will not convert all data into strings. Just like with mysqli_stmt_bind_result() you will be able to fetch data in native form. That is, for example, INT columns will not be converted into string but returned as int.

$res = mysqli_query($link, 'SELECT 1, "a", 1e06, 1.1');
var_dump(mysqli_fetch_assoc($res));

array(4) {
  [1]=>
  string(1) "1"
  ["a"]=>
  string(1) "a"
  ["1e06"]=>
  string(7) "1000000"
  ["1.1"]=>
  string(3) "1.1"
}

$stmt->prepare('SELECT 1, "a", 1e06, 1.1');
$stmt->execute();
$res = $stmt->get_result();
var_dump(mysqli_fetch_assoc($res));

array(4) {
  [1]=>
  int(1)
  ["a"]=>
  string(1) "a"
  ["1e06"]=>
  float(1000000)
  ["1.1"]=>
  string(3) "1.1"
}

Did you note that 1.1 got returned as a string? This is not a bug. The server recognized 1.1 as a DECIMAL column. Start a MySQL prompt with the command line option --column-type-info and you will see some column type metadata that the server sends to the client.

nixnutz@linux-eu6p:~/php6_mysqlnd> /usr/local/mysql/bin/mysql -uroot -proot --column-type-info phptest
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 76
Server version: 5.1.15-beta MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT 1.1;
Field   1:  `1.1`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       NEWDECIMAL
Collation:  binary (63)
Length:     4
Max_length: 3
Decimals:   1
Flags:      NOT_NULL BINARY


+-----+
| 1.1 |
+-----+
| 1.1 |
+-----+
1 row in set (0.00 sec)

As you can see above, the data is send as DECIMAL. The DECIMAL data range cannot be covered in all cases by any native PHP data type. Therefore it needs to be converted to string and returned using this “generic representation”. If you want to learn more about these data type conversions, check out the phpt test mysqli_stmt_get_result_types.phpt (SVN only) or mysqli_stmt_bind_result.phpt (php.net HEAD CVS).

Syntax

mysqli_stmt_get_result, stmt->get_result() — 
  creates a mysqli_result object from a prepared statement

Procedural style:
  mixed mysqli_stmt_get_result ( mysqli_stmt $stmt)

Object oriented style (method):
  class mysqli_stmt {
    mixed get_result ()
  }

stmt
    Procedural style only: A mysqli prepared statement object

Returns mysqli_result object on success or FALSE on failure.

Please note that it depends on the MySQL Server which SQL statements can be prepared.

Performance of mysqli_stmt_get_result() is roughly on pair with mysqli_stmt_bind_result(). However, I have not done any proper testing. On the first look, using the example script below, mysqli_stmt_get_result() seemed to be a bit slower.

Example

<?php
error_reporting(E_ALL);

if (!$link = mysqli_connect('localhost', 'root', 'root', 'phptest'))
	die(sprintf("Cannot connect, [%d] %s\n", mysqli_connect_errno(), mysqli_connect_error()));

if (!mysqli_query($link, 'DROP TABLE IF EXISTS test') ||
	!mysqli_query($link, 'CREATE TABLE test(id INT AUTO_INCREMENT PRIMARY KEY, label VARCHAR(127))'))
	die(sprintf("[%d] %s\n", mysqli_errno($link), mysqli_error($link)));

for ($num_rows = 10; $num_rows < 1010; $num_rows += 100) {
	$sql = sprintf('INSERT INTO test(label) VALUES ("%s")', str_repeat('a', 127));
	for ($i = 0; $i < $num_rows; $i++) {
		if (!mysqli_query($link, $sql))
			die(sprintf("[%d] %s\n", mysqli_errno($link), mysqli_error($link)));
	}

	/* Do one "pre-fetch" to fill the buffers */
	$res = mysqli_query($link, 'SELECT id, label FROM test ORDER BY id');
	$tmp = mysqli_fetch_all($res);
	mysqli_free_result($res);

	usleep(200);
	$stmt = mysqli_prepare($link, 'SELECT id, label FROM test ORDER BY id');
	mysqli_stmt_execute($stmt);
	$start = microtime();
	mysqli_stmt_bind_result($stmt, $id, $label);
	$rows = array();
	while (mysqli_stmt_fetch($stmt))
		$rows[$id] = $label;
	mysqli_stmt_close($stmt);
	$time_bind_result = microtime() - $start;

	usleep(200);
	$stmt = mysqli_prepare($link, 'SELECT id, label FROM test ORDER BY id');
	mysqli_stmt_execute($stmt);
	$start = microtime();
	$res = mysqli_stmt_get_result($stmt);
	$rows = array();
	while ($row = mysqli_fetch_assoc($res))
		$rows[$row['id']] = $row['label'];
	mysqli_free_result($res);
	mysqli_stmt_close($stmt);
	$time_get_result = microtime() - $start;

	printf("%05d rows %03d%% - mysqli_stmt_get_result() was %s\n",
		$num_rows,
		(100 / $time_bind_result) * $time_get_result,
		(($time_bind_result < $time_get_result) ? 'slower' : 'faster')
	);
}

mysqli_close($link);
?>