Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to get the total found rows without considering the limit in PDO?

Tags:

php

mysql

pdo

I found it very unprofessional to make a secondary query to check for more results in order to display a load more button.

I'm using PDO, is there any way to get the total found rows without the limit filter but still filtering the results?

Current Code:

// Show Results

$start = 0

$r=$db->prepare("SELECT * FROM locations WHERE area=:area LIMIT $start,10");
$r->execute($fields);

// See if there is more results

$r=$db->prepare("SELECT * FROM locations WHERE area=:area");
$r->execute($fields);

$offset=$start+10;
if($r->rowCount() > $offset){
echo "<div class='load'>Load More</div>";
}
like image 497
CodeOverload Avatar asked Jan 04 '11 16:01

CodeOverload


People also ask

How to count number of rows in PDO?

PDOStatement::rowCount() returns the number of rows affected by a DELETE, INSERT, or UPDATE statement. print("Return number of rows that were deleted:\n"); $count = $del->rowCount();

How to row count in PHP?

We can get the total number of rows in a table by using the MySQL mysqli_num_rows() function. Syntax: mysqli_num_rows( result ); The result is to specify the result set identifier returned by mysqli_query() function.


1 Answers

MySQL only AFAIK:

$r=$db->prepare("SELECT SQL_CALC_FOUND_ROWS * FROM locations WHERE area=:area LIMIT $start,10");
$r->execute($fields);

var_dump($r->fetchAll());

var_dump($db->query('SELECT FOUND_ROWS();')->fetch(PDO::FETCH_COLUMN));

About as heavy for the database server as querying a single time for all the records of course. For non-MySQL use, this query is of course better then getting the rowcount of all the records:

$r=$db->prepare("SELECT COUNT(*) FROM locations WHERE area=:area");
$r->execute($fields);
$count = $r->fetch(PDO::FETCH_COLUMN);
echo $count;
like image 64
Wrikken Avatar answered Sep 21 '22 16:09

Wrikken