mysqli_stmt::get_result

    mysqli_stmt_get_result

    (PHP 5 >= 5.3.0, PHP 7, PHP 8)

    mysqli_stmt::get_result -- mysqli_stmt_get_result — プリペアドステートメントから結果を mysqli_result オブジェクトとして取得する

    説明

    オブジェクト指向型

    public mysqli_stmt::get_result(): mysqli_result|false

    手続き型

    mysqli_stmt_get_result(mysqli_stmt $statement): mysqli_result|false

    プリペアドステートメントから結果を mysqli_result オブジェクトとして取得します。 データは MySQLサーバー からPHPへ転送されます。 このメソッドは結果セットを生成するクエリの場合にだけコールされるべきです。

    注意:

    mysqlnd でのみ使用可能です。

    注意:

    この関数は、 mysqli_stmt_store_result() と一緒に使うことはできません。 これらの関数は両方、MySQLサーバー から完全な結果セットを取得します。

    パラメータ

    stmt

    手続き型のみ: mysqli_stmt_init() が返す mysqli_stmt オブジェクト。

    戻り値

    失敗した場合に false を返します。 結果セットを生成するクエリ、 つまり SELECT, SHOW, DESCRIBE あるいは EXPLAIN が成功した場合は、mysqli_stmt_get_result() は mysqli_result オブジェクトを返します。それ以外のクエリが成功した場合は、 mysqli_stmt_get_result() は false を返します。 PHP 7.4.13 より前のバージョンでは、 その他の DML クエリを実行したのかクエリの実行に失敗したのかを区別するのに、 mysqli_errno() 関数を使わなければなりませんでした。

    エラー / 例外

    mysqli のエラー報告 (MYSQLI_REPORT_ERROR) が有効になっており、かつ要求された操作が失敗した場合は、警告が発生します。さらに、エラー報告のモードが MYSQLI_REPORT_STRICT に設定されていた場合は、mysqli_sql_exception が代わりにスローされます。

    例

    例1 オブジェクト指向型

    <?php

    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";
    }
    }

    例2 手続き型

    <?php

    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";
    }
    }

    上の例の出力は、 たとえば以下のようになります。

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

    参考

    Improve This Page

    Learn How To Improve This Page • Submit a Pull Request • Report a Bug
    ＋add a note

    User Contributed Notes 6 notes

    up
    47
    Anonymous
    11 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
    22
    Anon
    6 years 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
    6
    jeffspicer at gmail dot com
    6 years 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
    24
    jari dot wiklund at gmail dot com
    13 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
    1
    Haravikk
    8 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().
    up
    1
    HeadlessDev
    5 years ago
    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
    <?php
    //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;
    }
    ?>
    ＋add a note
    To Top