when executing $stmt->num_rows return 0

Question

I am new to mysqli. I am connecting to a mysql db, and fetching some data.

$user_id = "CCD_00005";

    /* create a prepared statement */
    if ($stmt = mysqli_prepare($GLOBALS['conn'], "SELECT user_name FROM user_master WHERE user_id=?")) {

        /* bind parameters for markers */
        mysqli_stmt_bind_param($stmt, "s", $user_id);

        /* execute query */
        mysqli_stmt_execute($stmt);

        /* bind result variables */
        mysqli_stmt_bind_result($stmt, $user_name);

        /* fetch value */
        mysqli_stmt_fetch($stmt);

        echo("Total Results:".$stmt->num_rows."<br />");

        printf("%s is having name %s\n", $user_id, $user_name);

        /* close statement */
        mysqli_stmt_close($stmt);
 }

I am getting the result Total Results:0 CCD_00005 is having name sdafasdf

Why am I not getting the row count as 1.

Please help.

You might need `$stmt->store_result()` first before you can check the number of rows. — Tolios, Sep 16 '17 at 12:03
From the manual on `mysqli_stmt::$num_rows`: "*Returns the number of rows in the result set. The use of `mysqli_stmt_num_rows()` depends on whether or not you used `mysqli_stmt_store_result()` to buffer the entire result set in the statement handle. If you use `mysqli_stmt_store_result()`, `mysqli_stmt_num_rows()` may be called immediately.*" — Qirel, Sep 16 '17 at 12:06
N.B.: The duplicate used to close the question with, was found under the **"Related"** area over to the right of the screen. — Funk Forty Niner, Sep 16 '17 at 12:09

score 1 · Accepted Answer · answered Sep 16 '17 at 12:17

change your execution order as below:

$user_id = "CCD_00005";

    /* create a prepared statement */
    if ($stmt = mysqli_prepare($GLOBALS['conn'], "SELECT user_name FROM user_master WHERE user_id=?")) {

        /* bind parameters for markers */
        mysqli_stmt_bind_param($stmt, "s", $user_id);

        /* execute query */
        mysqli_stmt_execute($stmt);

        mysqli_stmt_store_result($stmt);        
        echo("Total Results:".mysqli_stmt_num_rows($stmt)."<br />");

        /* bind result variables */
        mysqli_stmt_bind_result($stmt, $user_name);

        /* fetch value */
        mysqli_stmt_fetch($stmt);


        printf("%s is having name %s\n", $user_id, $user_name);

        /* close statement */
        mysqli_stmt_close($stmt);

when executing $stmt->num_rows return 0

1 Answers1