Using num_rows with a MySQLi prepared statement

While refactoring some code that I had written about six years ago, I was puzzled by the MySQLi num_rows property constantly returning 0, even though I knew there were matching records in the database. It turns out that the problem was caused by using a prepared statement, but not storing the result before attempting to access num_rows. I hadn’t bothered to store the result because all I wanted to know was if there were any matching records. This is the code I was using:

$stmt = $db->stmt_init();
$sql = 'SELECT username FROM users WHERE username = ?';
$stmt->prepare($sql);
$stmt->bind_param('s', $_POST['username']);
$stmt->execute();
$numrows = $stmt->num_rows;

The code worked without error, so it wasn’t immediately obvious why $numrows was always 0. So, I checked the PHP documentation for mysqli_stmt::num_rows. The description is rather ambiguous in that it refers only to the need to store the result when using the procedural style, but the object-oriented example makes it clear that you need to call the store_result() method before accessing the num_rows property. When I changed my code like this, I got the expected result:

$stmt = $db->stmt_init();
$sql = 'SELECT username FROM users WHERE username = ?';
$stmt->prepare($sql);
$stmt->bind_param('s', $_POST['username']);
$stmt->execute();
// store result of prepared statement
$stmt->store_result();
$numrows = $stmt->num_rows;

At first, it seemed counterintuitive to store a result that I was going to throw away, but that’s how num_rows works with a MySQLi prepared statement. Thinking about it a bit more, it makes sense because if you use the MySQLi query() method instead of a prepared statement, you store the result in a MySQLi_Result object and get the number of rows from the result like this:

$sql = 'SELECT user_id, name, username FROM users';
$result = $db->query($sql);
$numrows = $result->num_rows;

This entry was posted in MySQL, PHP. Bookmark the permalink.

9 Responses to Using num_rows with a MySQLi prepared statement

  1. Ray Hendriksma says:

    So I’m doing the exact same thing, and it’s NOT working. I still get a zero. So obviously something is different!

    function get_tabs_sub($tabID) {
    global $db;
    $query = “SELECT *
    FROM tabs_sub
    WHERE tabID = $tabID
    ORDER BY subName”;
    $tab_subs = $db->query($query);
    return $tab_subs;
    }
    ….
    $tab_subs_count = $tab_subs->num_rows;
    ….

    If I do a foreach loop, I can ‘count’ the rows but that just isn’t the right way to do it!

    Any ideas?

  2. David Powers says:

    Sorry, I don’t know what would be causing the problem. You’re returning a MySQLi_Result object from your function, so it should work. I’ve had a look at the PHP bugbase, but don’t see anything relevant there either. Very strange.

  3. Suzanne Hahn says:

    I’m having the same problem as Ray (getting 0 for num_rows). Could it be related to this change in PHP 5.3, noted in the first comment (update at bottom of comment) on this page of the PHP manual: http://www.php.net/manual/en/mysqli-stmt.bind-param.php? The commenter doesn’t state what specific problem is caused by not passing values by reference, but in my case the values are not bound and num_rows returns 0.

    (Note: I haven’t tried the commenter’s solution as I’m trying to follow the code in PHP Solutions. I did try passing by reference when binding parameters and then when binding the result, but received a warning that call-time pass-by reference had been deprecated.)

  4. Ken says:

    Fatal error: Call to a member function query() on a non-object in C:\wamp\www\phpsols\mysql\mysqli_10.php on line 9

    line 9 $result = $conn->query($sql) or die(mysqli_error());

    Where is the code to create the phsols database

    Ken

  5. David Powers says:

    The solution to both issues is on the book’s corrections and updates page.

  6. Ken says:

    I am looking for the script to create the database phpsols or the Data dictionary on what is internal to the tables and structures.

  7. David Powers says:

    There is no script. The instructions for creating the database structure are in Chapter 10.

  8. London Map says:

    For simple queries I tend to use $db->query($r), the difference is that variables in the query should be escaped e.g. $q=”SELECT * FROM users WHERE user_id=”$user_id”

  9. Chris says:

    Seriously, the official php documention is the biggest bullshit ever ! the official “num_rows”page does not really explain how to get num_rows. 100 of lines of example code, but not aa single line that display shows how to use it! a doc made by nerds.