problem getting num_rows with PDO class in php

Question

I have just changed my database connection. I am not used to the PDO class or OOP yet. Anyway, I connect to the db like this:

        $dsn = "mysql:host=" . DB_HOST . ";dbname=" . DB_NAME;
        try
        {
            $this->db = new PDO($dsn, DB_USER, DB_PASS);
        }
        catch ( Exception $e )
        {
            die ( $e->getMessage() );
        }

I am trying to get number of rows from this query:

    $ip = $this->ip(); 
    $sql = "SELECT `id` FROM `login_failed`
            WHERE `ip` = :ip AND `time` BETWEEN NOW( ) - INTERVAL 120 MINUTE AND NOW( )
            LIMIT 3";
    try 
    {
        $stmt = $this->db->prepare($sql);
        $stmt->bindParam(':ip', $ip, PDO::PARAM_STR);
        $result = $stmt->execute(); // $result = true
        $n = $stmt->num_rows ; // n = NULL?
        $stmt->closeCursor(); 
    }       
    catch (Exception $e)
    {
        die ($e->getMessage() ); 
    }

In phpmyadmin I get a result so my query is correct, but $n is NULL for some reason.. How do I get number of rows with PDO

Answer 1

$stmt is of type PDOStatement. That class has no num_rows property.

You might be looking for rowCount instead, but the documentation for that states:

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 long and the short if it is that, if you want to actually SELECT all that data, you can reliably determine how many rows were returned by iterating over the result set (or just call fetchAll and count the items in the array). If you don't need the data but just a number, use SELECT COUNT instead.

So, to count the rows without changing the query:

$result = $stmt->execute();
$rows = $stmt->fetchAll(); // assuming $result == true
$n = count($rows);