Row count with PDO

$sql = "SELECT count(*) FROM `table` WHERE foo = ?"; 
$result = $con->prepare($sql); 
$result->execute([$bar]); 
$number_of_rows = $result->fetchColumn(); 

Not the most elegant way to do it, plus it involves an extra query.

PDO has PDOStatement::rowCount(), which apparently does not work in MySql. What a pain.

From the PDO Doc:

For most databases, PDOStatement::rowCount() does not return the number of rows affected by a SELECT statement. Instead, use PDO::query() to issue a SELECT COUNT(*) statement with the same predicates as your intended SELECT statement, then use PDOStatement::fetchColumn() to retrieve the number of rows that will be returned. Your application can then perform the correct action.

EDIT: The above code example uses a prepared statement, which is in many cases is probably unnecessary for the purpose of counting rows, so:

$nRows = $pdo->query('select count(*) from blah')->fetchColumn(); 
echo $nRows;

As I wrote previously in an answer to a similar question, the only reason mysql_num_rows() worked is because it was internally fetching all the rows to give you that information, even if it didn't seem like it to you.

So in PDO, your options are:

1. Use PDO's fetchAll() function to fetch all the rows into an array, then use count() on it.
2. Do an extra query to SELECT COUNT(*), as karim79 suggested.
3. Use MySQL's FOUND_ROWS() function UNLESS the query had SQL_CALC_FOUND_ROWS or a LIMIT clause (in which case the number of rows that were returned by the query and the number returned by FOUND_ROWS() may differ). However, this function is deprecated and will be removed in the future.

To use variables within a query you have to use bindValue() or bindParam(). And do not concatenate the variables with " . $variable . "

$statement = "SELECT count(account_id) FROM account
                  WHERE email = ? AND is_email_confirmed;";
$preparedStatement = $this->postgreSqlHandler->prepare($statement);
$preparedStatement->bindValue(1, $account->getEmail());
$preparedStatement->execute();
$numberRows= $preparedStatement->fetchColumn();

GL

The simplest way, it is only 2 lines,

$sql = $db->query("SELECT COUNT(*) FROM tablename WHERE statement='condition'");
echo $sql->fetchColumn();

So, the other answers have established that rowCount() shouldn't be used to count the rows of a SELECT statement. The documentation even says, that :

PDOStatement::rowCount() returns the number of rows affected by the last DELETE, INSERT, or UPDATE statement executed by the corresponding PDOStatement object.

• https://web.archive.org/web/20220409162106/https://www.php.net/manual/en/pdostatement.rowcount.php

So it's okay for other queries, but not great for SELECT. Most answers suggest that you should make two queries, one to count rows, and one to get the subset of records you need. However, you could query the row count and your subset of the data in one request. This is a bit of an exercise in code golf, but could actually prove more efficient than two requests if the request time is a bit costly and these requests are made frequently.

If you're in PostgreSQL you can provide clean JSON output, like so:

WITH mytable as (VALUES(1,2,3),(4,5,6),(7,8,9),(10,11,12))
SELECT
    jsonb_build_object(
        'rowcount', (SELECT count(1) FROM mytable)
        ,'data', (
            SELECT jsonb_agg(data.*)
            FROM (
                SELECT *
                FROM mytable
                WHERE column1 > 1 -- pagination offset
                ORDER BY column1
                LIMIT 2 -- page size
            ) as data
        )
    ) jsondata

Output:

{"data": [
    {
      "column1": 4,
      "column2": 5,
      "column3": 6
    },
    {
      "column1": 7,
      "column2": 8,
      "column3": 9
    }
  ],
"rowcount": 4
}

If you're not in postgres, those functions won't be available, but you could do this:

WITH mytable as (VALUES(1,2,3),(4,5,6),(7,8,9),(10,11,12))
SELECT
    (SELECT count(1) FROM mytable) as rowcount
    ,data.*
FROM (
    SELECT *
    FROM mytable as mytable(column1, column2, column3)
    WHERE column1 > 1 -- pagination offset
    ORDER BY column1
    LIMIT 2 -- page size
) as data

but it will return the rowcount on every row, which might be a bit wasteful:

There is a simple solution. If you use PDO connect to your DB like this:

try {

    $handler = new PDO('mysql:host=localhost;dbname=name_of_your_db', 'your_login', 'your_password'); 
    $handler -> setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

} catch (PDOException $e) { 

    echo $e->getMessage();
}

Now, if you want to know how many rows are existing in your table and you have for example column 'id' as the primary key, the query to DB will be:

$query = $handler->query("SELECT id FROM your_table_name");

And finally, to get the amount of the rows matching your query, write like this:

$amountOfRows = $query->rowCount();

Or you can write:

$query = $handler ->query("SELECT COUNT(id) FROM your_table_name");

$amountOfRows = $query->rowCount();

Or, if you want to know how many products there are in the table 'products' have the price between 10 and 20, write this query:

$query = $handler ->query("SELECT id FROM products WHERE price BETWEEN 10 AND 
20");

$amountOfRows = $query->rowCount();

fetchColumn()

used if want to get count of record [effisien]

$sql   = "SELECT COUNT(*) FROM fruit WHERE calories > 100";
$res   = $conn->query($sql);
$count = $res->fetchColumn(); // ex = 2

query()

used if want to retrieve data and count of record [options]

$sql = "SELECT * FROM fruit WHERE calories > 100";
$res = $conn->query($sql);

if ( $res->rowCount() > 0) {

    foreach ( $res as $row ) {
        print "Name: {$row['NAME']} <br />";
    }

}
else {
    print "No rows matched the query.";
}

PDOStatement::rowCount