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;
How do I count unique rows in php pdo?
You can use distinct in mysql to select only unique fields in your table.
$query = "SELECT distinct user_id FROM users";
$stmt = $db->prepare($query);
$stmt->execute();
$count = $stmt->rowCount();
PDO::rowCount VS COUNT(*)
1st question:
Using count COUNT()
, internally the server(MySQL) will process the request differently.
When doing COUNT()
, the server(MySQL) will only allocate memory to store the result of the count.
When using $row=$SQL->rowCount();
the server (Apache/PHP) will process the entire result set, allocate memory for all those results, and put the server in fetching mode, which involves a lot of different details, such as locking.
Take note that PDOStatement::rowCount()
returns the number of rows affected by the last statement, not the number of rows returned. 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.
On my analysis, if you use COUNT()
, the process would be divided to both MySQL and PHP while if you use $row=$SQL->rowCount();
, the processing would be more for PHP.
Therefore COUNT()
in MySQL is faster.
2nd question:
COUNT(*)
is better than COUNT(id)
.
Explanation:
The count(*)
function in mysql is optimized to find the count of values. Using wildcard means it does not fetch every row. It only find the count. So use count(*)
wherever possible.
Sources:
- PDOStatement::rowCount
- MySQL COUNT(*)
get number of rows with pdo
PDO::FETCH_NUM: returns an array indexed by column number as returned in your result set, starting at column 0
You aren't fetching the row-count at all.
SELECT COUNT(*) FROM coursescompleted where person=:p
This query would return total rows in $rows[0];
EDIT:
Please see @ray's answer. using count(id)
is better than count(*)
for InnoDB.
You could get row-count in the following manner, from your earlier query.
$row_count = $result->rowCount();
But be warned:
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.
Documentation
how to get the count of row values in php pdo
In Mysql $stmt->rowCount(); doesnt work. Try this
$nRows = $pdo->query('select count(*) from yourTable')->fetchColumn();
echo 'Number of rows is = '. $nRows;
Here is an excerpt from a comment and answer regarding the same. Check it out its very resourceful.
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. Refer to this anwere
So in PDO, your options are:
- Use MySQL's FOUND_ROWS() function.
- Use PDO's fetch_all() function to fetch all the rows into an array,
then use count() on it. - Do an extra query to SELECT COUNT(*),
https://stackoverflow.com/a/883523/2536812
PDO rowCount only returning one
PDOStatement::rowCount() returns the number of rows affected by the last DELETE, INSERT, or UPDATE statement executed by the corresponding PDOStatement object.
rowCount() is not for a SELECT
query, use a separate COUNT
query for that or fetch all rows in an array and count
its size
get row count in pdo class - after fetching data
<?php
class Database
{
protected $dbh;
protected $query;
public $rows;
protected $count;
public function __construct($host,$user,$pass,$dbname)
{
// Set DSN
$dsn = 'mysql:host=' . $host . ';dbname=' . $dbname;
// Set options
$options = array(
PDO::ATTR_PERSISTENT => true,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
);
// Create a new PDO instanace
try
{
$this->dbh = new PDO($dsn, $user, $pass, $options);
}
catch(PDOException $e)
{
echo $this->error = $e->getMessage();
die();
}
}
public function query($query)
{
$this->query = $this->dbh->query($query);
}
public function fetchAllQuery()
{
$result = $this->query->fetchAll(PDO::FETCH_ASSOC);
$this->count = count($result);
return $result;
}
public function rowCountQuery()//--------------------------------(1)
{
return $this->count;
}
}
$database = new Database('localhost','root','','speed');
$area='aa';
$price=200;
$query = $database->query("SELECT * FROM tbl_deliverygrid");
$result = $database->fetchAllQuery();///-------------------------------(2)
echo '<pre>',print_r($result),'</pre><br>';
echo 'row count query [['.count($result).']]<br>';
echo '['.print_r($database->rowCountQuery()).']';
echo '<br>';
Related Topics
Simplest PHP Example For Retrieving User_Timeline With Twitter API Version 1.1
Using .Htaccess to Make All .Html Pages to Run as .PHP Files
Laravel 5 - Remove Public from Url
Replace Keys in an Array Based on Another Lookup/Mapping Array
How to Create Custom Helper Functions in Laravel
Continue Processing PHP After Sending Http Response
How to Let PHP to Create Subdomain Automatically For Each User
Difference Between Bindparam and Bindvalue
Can't Use Method Return Value in Write Context
PHP MySQLi_Connect: Authentication Method Unknown to the Client [Caching_Sha2_Password]
How to Implement a Good Profanity Filter
Getting the Screen Resolution Using PHP
What's Better At Freeing Memory With PHP: Unset() or $Var = Null