How to get mysql_num_rows with PDO?

Question

How can I easily get the number of rows returned with "SELECT *" SQL-query?

function valid_credentials($db,$name,$pw) {
    try {

    $sth = $db->prepare("SELECT * FROM ib_members WHERE name=:val AND pw=:val2");
    $sth->bindValue(":val",$name);
    $sth->bindValue(":val2",$pw);   
    $sth->execute();


    $numrows = $sth->fetchColumn();

    return $numrows;

    } catch (PDOException $e) {

        return $e->getMessage();

    }
}

That returns 14, which is definately not the number of rows returned, but it's the ID that the first row has.

$sth->rowCount(); [http://php.net/manual/en/pdostatement.rowcount.php](http://php.net/manual/en/pdostatement.rowcount.php) just check the example, this question can be found by googling in couple of seconds, not sure why bother to ask! — Hawili, Aug 28 '12 at 19:21
possible duplicate of [Count number of rows in SELECT query with PDO](http://stackoverflow.com/questions/6041886/count-number-of-rows-in-select-query-with-pdo) — Josh, Aug 28 '12 at 19:22
Hmm. It turns out that `mysql_num_rows()` only worked for SELECT statements because internally `mysql_query()` buffered all of the rows returned in order to derive this information. See [this answer](http://stackoverflow.com/a/770045/862594) for more information. — nickb, Aug 28 '12 at 19:31

quietmint · Answer 1 · 2012-08-28T19:37:46.633

1

For DELETE, INSERT, or UPDATE statements, use PDOStatement::rowCount().

For SELECT statements, count the rows manually in your while( $sth->fetch() ) loop (you can break out of the loop if the count exceeds a certain threshold, for instance), or execute a separate query to have the database return the row count (for example, SELECT COUNT(*) FROM table WHERE column = ?).

edited Aug 28 '12 at 19:37

answered Aug 28 '12 at 19:27

quietmint

13,885
6
48
73

How to get mysql_num_rows with PDO?

1 Answers1