PHP 7.0.33 Released

mysqli_stmt::get_result

mysqli_stmt_get_result

(PHP 5 >= 5.3.0, PHP 7)

mysqli_stmt::get_result -- mysqli_stmt_get_resultプリペアード・ステートメントから結果セットを取得

説明

オブジェクト指向型

mysqli_result mysqli_stmt::get_result ( void )

手続き型

mysqli_result mysqli_stmt_get_result ( mysqli_stmt $stmt )

プリペアード・ステートメントから結果セットを返すための呼び出し。

パラメータ

stmt

手続き型のみ: mysqli_stmt_init() が返すステートメント ID。

返り値

SELECT クエリが成功した場合はその結果セットを返します。 その他の DML クエリを実行した場合や失敗した場合は FALSE を返します。 その他の DML クエリを実行したのかクエリの実行に失敗したのかを区別するには、 mysqli_errno() 関数を使います。

MySQL ネイティブドライバ限定

mysqlnd でのみ使用可能です。

例1 オブジェクト指向型

<?php 

$mysqli 
= new mysqli("127.0.0.1""user""password""world"); 

if(
$mysqli->connect_error)
{
    die(
"$mysqli->connect_errno$mysqli->connect_error");
}

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

$stmt $mysqli->stmt_init();
if(!
$stmt->prepare($query))
{
    print 
"Failed to prepare statement\n";
}
else
{
    
$stmt->bind_param("s"$continent);

    
$continent_array = array('Europe','Africa','Asia','North America');

    foreach(
$continent_array as $continent)
    {
        
$stmt->execute();
        
$result $stmt->get_result();
        while (
$row $result->fetch_array(MYSQLI_NUM))
        {
            foreach (
$row as $r)
            {
                print 
"$r ";
            }
            print 
"\n";
        }
    }
}

$stmt->close();
$mysqli->close();
?>

例2 手続き型

<?php 

$link 
mysqli_connect("127.0.0.1""user""password""world"); 

if (!
$link)
{
    
$error mysqli_connect_error();
    
$errno mysqli_connect_errno();
    print 
"$errno$error\n";
    exit();
}

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

$stmt mysqli_stmt_init($link);
if(!
mysqli_stmt_prepare($stmt$query))
{
    print 
"Failed to prepare statement\n";
}
else
{
    
mysqli_stmt_bind_param($stmt"s"$continent);

    
$continent_array = array('Europe','Africa','Asia','North America');

    foreach(
$continent_array as $continent)
    {
        
mysqli_stmt_execute($stmt);
        
$result mysqli_stmt_get_result($stmt);
        while (
$row mysqli_fetch_array($resultMYSQLI_NUM))
        {
            foreach (
$row as $r)
            {
                print 
"$r ";
            }
            print 
"\n";
        }
    }
}
mysqli_stmt_close($stmt);
mysqli_close($link);
?>

上の例の出力は以下となります。

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

参考

add a note add a note

User Contributed Notes 6 notes

up
37
Anonymous
5 years ago
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.

<?php
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 ."']";
    };

   
$code .= ");";
    if (!eval(
$code)) { return NULL; };

   
// 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.
up
26
jari dot wiklund at gmail dot com
7 years ago
Please note that this method requires the mysqlnd driver. Othervise you will get this error: Call to undefined method mysqli_stmt::get_result()
up
2
Anon
5 months ago
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:

mysqli_stmt_execute($stmt);
mysqli_stmt_store_result($stmt);
$num_rows = mysqli_stmt_num_rows($stmt);

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!
up
1
barkeldiho at gmail dot com
2 months ago
The description of get_result() indicates that it will return a resultset on success of a SELECT statement or false (boolean) in case of any other query.

This is not true. In fact, there are cases in which get_result() will return true (boolean) e.g. when a DELETE command has been successfully send to the database. I can reproduce this case with the following code:

DELETE command send from garbage collector in a custom sessionhandler:

<?php
public function _gc($life) {
  
$sessionlife = strtotime("-".SESSION_TIMEOUT."minutes");
  
$dummy = "DELETE FROM sessions WHERE lastupdated< ?";
  
$valueArray = array();
  
$valueArray[] = $sessionlife;
  
$result = $this->DB->prepQuery($dummy,$valueArray);
   return
$result;
}
?>

Part of the prepQuery function to send prepared statements to the database:

<?php
$sqlquery
->execute();
$result = $sqlquery->get_result();
$sqlqueryError = $sqlquery->errno;
$sqlqueryAffRows = $sqlquery->affected_rows;
$sqlquery->close();

//Catch non-SELECT statements, false cases
if($result === false) {
   if(
$sqlqueryError == false) {
      if(
$sqlqueryAffRows == 0) {
         return
false;
      }
      return
true;
   }
   else {
      return
false;
   }
}
return
$this->makeArrayResult($result);
?>

that should have catched all the false (boolean) cases, and the makeArrayResult function, that only works with objects:

<?php
private function makeArrayResult($resultobj) {
   if(
$resultobj->num_rows == 0) {
      return array();
   }
   else {
     
$array = array();
      while(
$line = $resultobj->fetch_array(MYSQLI_ASSOC)) {
        
array_push($array,$line);
      }
      return
$array;
   }
}
?>

but which was giving me the following Error Notice:

PHP Notice -- Trying to get property of non-object

Hence the case true (boolean) needs to be considered and the description should get updated.
up
0
jeffspicer at gmail dot com
5 months ago
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.
up
0
Haravikk
2 years ago
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().
To Top