Count without using rowCount from PDO

Question

I’m new to PHP PDO, I’m using MVC and I couldn’t do the line count on database using $obj->rowCount();

Just follow my code

php model.:

public function read($table, $fields, $where = null, $fetchMode = PDO::FETCH_ASSOC) {

    $where = isset($where) ? "WHERE {$where}" : "";

    $sql = $this->db->query("SELECT {$fields} FROM {$table} {$where} ");

    return $sql->fetchAll($fetchMode);

}

login_model.php

public function login() {
    $id = 1;
    $sql = $this->read('users', 'email', "id={$id}");
    print_r($sql);
    if (count($sql) >= 1) {
        echo 'é maior';
    } else {
        echo 'é menor';
    }
}

as you saw I’m wearing count() to make the count because I could not do using rowCount(); for lack of logic :/

Answer 1

In your method returns only $sql and not the results (fetchAll().

public function read($table, $fields, $where = null, $fetchMode = PDO::FETCH_ASSOC) {
    $where = isset($where) ? "WHERE {$where}" : "";
    $sql = $this->db->query("SELECT {$fields} FROM {$table} {$where} ");
    return $sql;
}

In the chamda faça:

$sql = $this->read('users', 'email', "id={$id}");
echo $sql->rowCount();

Answer 2

Pdostatement::rowCount() Returns the number of Rows affected by the last DELETE, INSERT, or UPDATE statement executed by the corresponding Pdostatement Object.

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."

Source: http://php.net/manual/en/pdostatement.rowcount.php

EDIT ():

If the last SQL query executed through Pdostatement was a query of type select some databases can return the number of selected Rows, however, other databases may not return and therefore the behavior is not guaranteed for all databases and therefore should not be invoked in applications portaile.

It may not be perfect, but I find myself at work right now and this is the best I can do to help you.

Answer 3

If you are using Mysql you can work around the situation using sql_calc_found_rows and found_rows().

Here is a link to an article that makes use of these resources.

http://www.marcosborges.com/blog/2010/08/13/select-sql_calc_found_rows-no-mysql/

The most interesting thing is that it returns the total number of lines found even using the LIMIT and OFFSET clasps. I use this feature a lot to make pagination in the systems I do.