Get function result php

Содержание

PHP get_result() and mysqli_stmt_get_result()
PHP get_result()
PHP get_result() Syntax
PHP mysqli_stmt_get_result()
PHP mysqli_stmt_get_result() Syntax
mysqli_stmt_get_result
Parameters
Return Values
Errors/Exceptions
Examples
See Also
User Contributed Notes 6 notes

PHP get_result() and mysqli_stmt_get_result()

This article is created to cover the two functions of PHP, namely:

Both functions are used to get a result set from a prepared statement as a mysqli_result object. The only difference is that get_result() uses PHP mysqli object-oriented script, whereas mysqli_stmt_get_result() uses PHP mysqli procedural script.

PHP get_result()

The PHP get_result() function returns a result set from a prepared statement as a mysqli_result object in object-oriented style. For example:

connect_errno) < echo "Database connection failed!"; echo "Reason: ", $conn->connect_error; > else < $sql = "SELECT name, age FROM customer"; $stmt = $conn->prepare($sql); if($stmt->execute() == true) < $result = $stmt->get_result(); while($row = $result->fetch_array()) < echo "Name: ", $row[0]; echo ""; echo "Age: ", $row[1]; echo ""; > > > $conn->close(); ?>

The output of the above PHP example using the get_result() function is shown in the snapshot given below:

Note: The mysqli() function is used to open a connection to the MySQL database server in object-oriented style.

Note: The new keyword is used to create a new object.

Note: The connect_errno is used to get or return the error code (if any) from the last connect call in object-oriented style.

Note: The connect_error is used to get the error description (if any) from the last connection in object-oriented style.

Note: The prepare() function is used to prepare an SQL statement before its execution on the MySQL database in object-oriented style, to avoid SQL injection.

Note: The execute() function is used to execute a prepared statement on the MySQL database in object-oriented style.

Note: The fetch_array() function is used when we need to fetch and get the result as an enumerated array, an associative array, or both in object-oriented style.

Note: The close() function is used to close an opened connection in object-oriented style.

The above example can also be written as:

connect_errno) < $stmt = $conn->prepare("SELECT name, age FROM customer"); $stmt->execute(); $result = $stmt->get_result(); while($row = $result->fetch_array()) < echo "Name: ", $row[0]; echo ""; echo "Age: ", $row[1]; echo ""; > > $conn->close(); ?>

PHP get_result() Syntax

The syntax of the get_result() function in PHP is:

PHP mysqli_stmt_get_result()

The PHP mysqli_stmt_get_result() function returns a result set from a prepared statement as a mysqli_result object in procedural style. For example:

"; echo "Age: ", $row[1]; echo ""; > > mysqli_close($conn); ?>

Note: The mysqli_connect() function is used to open a connection to the MySQL database server in procedural style.

Note: The mysqli_connect_errno() function is used to get or return the error code (if any) from the last connect call in procedural style.

Note: The mysqli_prepare() function is used to prepare an SQL statement before its execution on the MySQL database in procedural style, to avoid SQL injection.

Note: The mysqli_stmt_execute() function is used to execute a prepared statement on the MySQL database in procedural style.

Note: The mysqli_fetch_array() function is used when we need to fetch and get the result as an enumerated array, an associative array, or both in procedural style.

Note: The mysqli_close() function is used to close an opened connection to the MySQL database in procedural style.

PHP mysqli_stmt_get_result() Syntax

The syntax of the mysqli_stmt_get_result() function in PHP is:

mysqli_stmt_get_result($mysqli_stmt)

Источник

mysqli_stmt_get_result

Retrieves a result set from a prepared statement as a mysqli_result object. The data will be fetched from the MySQL server to PHP. This method should be called only for queries which produce a result set.

Note:

This function cannot be used together with mysqli_stmt_store_result() . Both of these functions retrieve the full result set from the MySQL server.

Parameters

Procedural style only: A mysqli_stmt object returned by mysqli_stmt_init() .

Return Values

Returns false on failure. For successful queries which produce a result set, such as SELECT, SHOW, DESCRIBE or EXPLAIN , mysqli_stmt_get_result() will return a mysqli_result object. For other successful queries, mysqli_stmt_get_result() will return false . The mysqli_stmt_errno() function can be used to distinguish between the two reasons for false ; due to a bug, prior to PHP 7.4.13, mysqli_errno() had to be used for this purpose.

Errors/Exceptions

If mysqli error reporting is enabled ( MYSQLI_REPORT_ERROR ) and the requested operation fails, a warning is generated. If, in addition, the mode is set to MYSQLI_REPORT_STRICT , a mysqli_sql_exception is thrown instead.

Examples

Example #1 Object-oriented style

mysqli_report ( MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT );
$mysqli = new mysqli ( «localhost» , «my_user» , «my_password» , «world» );

$query = «SELECT Name, Population, Continent FROM Country WHERE Continent=? ORDER BY Name LIMIT 1» ;

$stmt = $mysqli -> prepare ( $query );
$stmt -> bind_param ( «s» , $continent );

$continentList = array( ‘Europe’ , ‘Africa’ , ‘Asia’ , ‘North America’ );

foreach ( $continentList as $continent ) $stmt -> execute ();
$result = $stmt -> get_result ();
while ( $row = $result -> fetch_array ( MYSQLI_NUM )) foreach ( $row as $r ) print » $r » ;
>
print «\n» ;
>
>

Example #2 Procedural style

mysqli_report ( MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT );
$link = mysqli_connect ( «localhost» , «my_user» , «my_password» , «world» );

$query = «SELECT Name, Population, Continent FROM Country WHERE Continent=? ORDER BY Name LIMIT 1» ;

$stmt = mysqli_prepare ( $link , $query );
mysqli_stmt_bind_param ( $stmt , «s» , $continent );

$continentList = array( ‘Europe’ , ‘Africa’ , ‘Asia’ , ‘North America’ );

foreach ( $continentList as $continent ) mysqli_stmt_execute ( $stmt );
$result = mysqli_stmt_get_result ( $stmt );
while ( $row = mysqli_fetch_array ( $result , MYSQLI_NUM )) foreach ( $row as $r ) print » $r » ;
>
print «\n» ;
>
>

The above examples will output something similar to:

Albania 3401200 Europe Algeria 31471000 Africa Afghanistan 22720000 Asia Anguilla 8000 North America

User Contributed Notes 6 notes

I went through a lot of trouble on a server where mysqlnd wasn’t available, and had a lot of headaches.

If you don’t have mysqlnd installed/loaded whatever, you will get an undefined reference when trying to call «mysqli_stmt_get_result()».

I wrote my own mysqli_stmt_get_result() and a mysqli_result_fetch_array() to go with it.

class iimysqli_result
public $stmt , $nCols ;
>

function iimysqli_stmt_get_result ( $stmt )
/** EXPLANATION:
* We are creating a fake «result» structure to enable us to have
* source-level equivalent syntax to a query executed via
* mysqli_query().
*
* $stmt = mysqli_prepare($conn, «»);
* mysqli_bind_param($stmt, «types», . );
*
* $param1 = 0;
* $param2 = ‘foo’;
* $param3 = ‘bar’;
* mysqli_execute($stmt);
* $result _mysqli_stmt_get_result($stmt);
* [ $arr = _mysqli_result_fetch_array($result);
* || $assoc = _mysqli_result_fetch_assoc($result); ]
* mysqli_stmt_close($stmt);
* mysqli_close($conn);
*
* At the source level, there is no difference between this and mysqlnd.
**/
$metadata = mysqli_stmt_result_metadata ( $stmt );
$ret = new iimysqli_result ;
if (! $ret ) return NULL ;

$ret -> nCols = mysqli_num_fields ( $metadata );
$ret -> stmt = $stmt ;

mysqli_free_result ( $metadata );
return $ret ;
>

function iimysqli_result_fetch_array (& $result )
$ret = array();
$code = «return mysqli_stmt_bind_result(\$result->stmt » ;

for ( $i = 0 ; $i < $result ->nCols ; $i ++)
$ret [ $i ] = NULL ;
$code .= «, \$ret[‘» . $i . «‘]» ;
>;

// This should advance the «$stmt» cursor.
if (! mysqli_stmt_fetch ( $result -> stmt )) < return NULL ; >;

// Return the array we built.
return $ret ;
>
?>

Hope this helps someone.

Please OH PLEASE.
I have been trying to get a result set from this function, and I had 0 luck completely, for nearly 3 hours!

If you ARE using mysqli_stmt_get_results() to get a result set, in conjuction with mysqli_stmt_store_results in order to retrieve the number of rows returned, you are going to have some major trouble!

PHP Documentation states that to retrieve the number of rows returned by a prepared select sql statement, one should call the following statements respectively:

THIS IS A MAJOR DEATH TRAP, IF YOU ARE USING mysqli_stmt_get_result() in conjunction. Results of doing so vary depending which statements you call first, but in the end, you will NOT get the desired result.

In conclusion, please, PLEASE, NEVER use mysqli_stmt_store_result(), then mysqli_ AND mysqli_stmt_get_result() at the the same time. This is a MAJOR death trap.

SOLUTION:
If you are trying to get a result set, and you need the number of rows returned at the same time, use the following statements respectively instead:

$result_set = mysqli_stmt_get_results($stmt);
$num_rows = mysqli_num_rows($result_set);

Reflecting on my actions, this solution may seem fairly obvious. However, to someone new using PHP (like me) or someone who is not fully comfortable with prepared statements, it’s very easy to get lost by using Google and learn on your own.

Summary:
NEVER use mysqli_stmt_store_result($stmt) & mysqli_stmt_num_rows($stmt) in conjunction with mysqli_stmt_get_result($stmt). You will regret it!! I have been stuck on this for hours, and Google offered me no answer!

To prevent others from hours of screaming trying to figure out why this does not work when you have the native driver enabled. It is because you need to make sure you enable both native drives mysqlnd and nd_mysqli on your server if you wish to use this functionality.

Otherwise just enabling the mysqlnd native driver does include the mysqli_stmt_get_result function as this apparently resides in the nd_mysqli native driver. Unfortunately, most documentation only ever talks about the mysqlnd native driver.

Once you enable both native drivers this function works as documented.

Please note that this method requires the mysqlnd driver. Othervise you will get this error: Call to undefined method mysqli_stmt::get_result()

For those interested, this function seems to always produce a stored result, making it equivalent to mysqli::store_result(), rather than fetching on demand as in the case of mysqli::use_result().

This is important as it means that you cannot control the nature of the result as you would normally by calling mysqli_stmt::store_result() prior to fetching, as I had personally expected.

So, if you want to emulate the behaviour of mysqli::use_result() you will still need to use mysqli_stmt::bind_param() and mysqli_stmt::fetch().

Thanks to everyone that leaves notes, without them I would not be able to do what I do. I wrote this function with help from notes on this page and others.

I did not have mysqlnd available to me, but wanted to be able to get_result() of a query as an object.

PLEASE NOTE: I am not an expert at PHP

Heres an example of using my get_result() to login users
//Sanitize input before using this function
function login ( $email , $password ) require ‘connect_db.php’ ;
$query = $sql -> stmt_init ();
if( $query -> prepare ( «SELECT CustomerId, Password, Admin FROM users WHERE Email = ?» )) $query -> bind_param ( «s» , $email );
$result = get_result ( $query ); //USING FUNCTION HERE
if( $result != NULL ) $user = $result [ 0 ];
if( password_verify ( $password , $user -> Password )) $_SESSION [ ‘user_id’ ] = $user -> CustomerId ;
if( $user -> Admin == 1 ) $_SESSION [ ‘admin’ ] = true ;
>
$sql -> close ();
return true ;
>
>
>
$sql -> close ();
//If we get here they are not logged in
return false ;
>

//Returns an array with each row as an object
function get_result ( $stmt ) $stmt -> execute (); //Execute query
$stmt -> store_result (); //Store the results
$num_rows = $stmt -> num_rows ; //Get the number of results
$results = NULL ;
if( $num_rows > 0 ) //Get metadata about the results
$meta = $stmt -> result_metadata ();
//Here we get all the column/field names and create the binding code
$bind_code = «return mysqli_stmt_bind_result(\$stmt, » ;
while( $_field = $meta -> fetch_field ()) $bind_code .= «\$row[\»» . $_field -> name . «\»], » ;
>
//Replace trailing «, » with «);»
$bind_code = substr_replace ( $bind_code , «);» , — 2 );
//Run the code, if it doesn’t work return NULL
if(!eval( $bind_code )) <
$stmt -> close ();
return NULL ;
>
//This is where we create the object and add it to our final result array
for( $i = 0 ; $i < $num_rows ; $i ++)//Gets the row by index
$stmt -> data_seek ( $i );
//Update bound variables used in $bind_code with new row values
$stmt -> fetch ();
foreach( $row as $key => $value ) //Create array using the column/field name as the index
$_result [ $key ] = $value ;
>
//Cast $_result to object and append to our final results
$results [ $i ] = (object) $_result ;
>
>
$stmt -> close ();
return $results ;
>
?>