PHP MySQLI Result num_rows returns null

Question

Why would num_rows ever return null? It should always return a number representing the exact number of rows in the result set.

The following code returns null for num_rows:

<?php
include_once 'includes/psl-config.php';   // host, user, password, database
$db = new PDO('mysql:host='.HOST.';dbname='.DATABASE.';charset=utf8', USER, PASSWORD,
                array(PDO::ATTR_EMULATE_PREPARES => false, 
                      PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));

if ($res = $db->query("select 'Hello world!'")){
    if (is_null($res)){
        echo "Result is null<br>";
    }
    if (is_null($res->num_rows)){
        echo "Num rows is null<br>";
    } else {
        echo "Rows returned: ".$res->num_rows."<br>";
    }
    while ($row = $res->fetch_row){
        echo "Text returned = $row[0]<br>";
    }
    echo "Done";

} else {
    echo "SQL error";
}

The resulting page displays "Num_rows is null".

This is so simple, I must be missing something!

The manual says num_rows always returns an int.
I use mysqli query, which stores the results by default. I don't need to use store_result.
Result is not null.

for PDO i beleive counting rows i going with $res->rowCount(); — Aleksandar Vasić, Nov 07 '14 at 08:21
please refer to this page: http://stackoverflow.com/questions/2700621/php-pdo-num-rows use $foundRows = $pdo->query("SELECT FOUND_ROWS()")->fetchColumn(); is best alternative options than using $res->rowCount(); IMHO. — Somy A, Nov 07 '14 at 08:24
You should not use `$res->rowCount()` on select statements. It is unreliable...I learned this literally yesterday. — Rasclatt, Nov 07 '14 at 08:24
"SELECT 'Hello World!'" is valid SQL - no table needed, simply some text to return. — haryadoon, Nov 08 '14 at 04:55

score 1 · Answer 1 · answered Nov 07 '14 at 08:26

1

You are using PDO. So it should be

$res->rowCount()

instead of

$res->num_rows

answered Nov 07 '14 at 08:26

Sougata Bose

31,517
8
49
87

It is not reliable for select statements. You need to do a `COUNT(*)` statement/query. – Rasclatt Nov 07 '14 at 08:27
Dont know might be.. but if it is not reliable then `count()` will do the trick.. – Sougata Bose Nov 07 '14 at 08:29

Rasclatt · Accepted Answer · 2016-06-29T13:49:32.630

Since you are using PDO and not MySQLi, num_rows is not applicable. That being said, in general, I have found rowCount() is reliable when it comes to MySQL, however, as it states in the manual you should not rely on the rowCount() regarding SELECT statements:

If the last SQL statement executed by the associated PDOStatement was a SELECT statement, some databases may return the number of rows returned by that statement. However, this behaviour is not guaranteed for all databases and should not be relied on for portable applications.

The recommendation is to use a COUNT() sql statement instead for greatest reliability across database platforms:

$db->query("SELECT COUNT(*) FROM table_name");

PHP MySQLI Result num_rows returns null

2 Answers2