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;
PHP PDO - Num Rows
According to the manual, there is a PDOStatement->rowCount
method ; but it shouldn't be used (quoting) :
For most databases,
PDOStatement::rowCount()
does not
return the number of rows affected by
aSELECT
statement.
Instead, use
PDO::query()
to issue aSELECT COUNT(*)
statement with the same
predicates as your intendedSELECT
statement, then use
PDOStatement::fetchColumn()
to
retrieve the number of rows that will
be returned.
Your application can then
perform the correct action.
If you already have a recordset, and want to know how many lines are in it, you'll have to fetch the data, using one of the fetch*
methods ; and use count -- like you suggested.
Alternative for mysql_num_rows using PDO
$res = $DB->query('SELECT COUNT(*) FROM table');
$num_rows = $res->fetchColumn();
or
$res = $DB->prepare('SELECT COUNT(*) FROM table');
$res->execute();
$num_rows = $res->fetchColumn();
You can use this to ask if data exists or is selected, too:
$res = $DB->query('SELECT COUNT(*) FROM table');
$data_exists = ($res->fetchColumn() > 0) ? true : false;
Or with your variables:
$res = $DB->query('SELECT COUNT(*) FROM table');
$message = ($res->fetchColumn() > 0) ? array('status' => 'ok') : array('status' => 'error');
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();
How to count number of rows in MySQL table (PHP PDO)
you are overriding the $stmt after execute using fetchAll()
so instead try this
$stmt->execute();
$totalrows = $stmt->rowCount();
echo $totalrows;
// Pick up the result as an array
$result = $stmt->fetchAll();
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 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(*)
How to count the number of rows in MySQL using PHP PDO
If you have a PDO Object with a PDO connection to your database -
(Let´s call it for example $con)
You can prepare a statement like
$stmt = $con->prepare('SELECT movie_name FROM movie_info LIMIT 3 OFFSET 0');
// Then fire it up
$stmt->execute();
// Pick up the result as an array
$result = $stmt->fetchAll();
// Now you run through this array in many ways, for example
for($x=0, $n=count($result); $x < $n; $x++){
echo $result[$x]['movie_name'];
}
The count($result) keeps your number of result
Related Topics
Best Way to Determine If a Url Is an Image in PHP
How to Use PHP to Check If a Directory Is Empty
Adding Attributes to Customer Entity
Include Constant in String Without Concatenating
Chaining Static Methods in PHP
Why Are Functions and Methods in PHP Case-Insensitive
How to Install PHP-Curl in Ubuntu 16.04
Fatal Error: Class 'Ziparchive' Not Found In
Php: How to Call Function of a Child Class from Parent Class
Dependency Hell - How Does One Pass Dependencies to Deeply Nested Objects
How to Read Large Worksheets from Large Excel Files (27Mb+) with PHPexcel
Any Decent PHP Parser Written in PHP
How to Write a Stored Procedure Using PHPmyadmin and How to Use It Through PHP