How to count all records but only retrieve (LIMIT) a specific number for display?

Question

I want to echo only the first 10 rows, but I need to count the total number of rows affected by the query.

I was doing a LIMIT 10 and then counting with the obvious problem I kept getting 10 as the count.

What would be a proper way to do it?

$data = mysql_query("SELECT * FROM Badges WHERE UID = '$user' ORDER by Date DESC");
$count = mysql_num_rows($data);

while($row = mysql_fetch_array( $data )) 
    { 
    echo $row['Site'];
    }

possible duplicate of [How to count all rows when using SELECT with LIMIT in MySQL query?](http://stackoverflow.com/questions/2439829/how-to-count-all-rows-when-using-select-with-limit-in-mysql-query) — , Nov 09 '11 at 04:13

mu is too short · Accepted Answer · 2014-01-09T05:39:24.270

10

MySQL has some special support for this sort of thing. First, include SQL_CALC_FOUND_ROWS in your SELECT:

SELECT SQL_CALC_FOUND_ROWS *
FROM Badges
WHERE UID = '$user'
ORDER by Date DESC
LIMIT 10 -- Or whatever

Then pull out your rows and then immediately look at FOUND_ROWS() like this:

SELECT FOUND_ROWS()

to get the number of rows that matched your original query without considering the LIMIT clause.

This is MySQL-specific but it should be a little faster than doing two queries.

edited Jan 09 '14 at 05:39

answered Nov 09 '11 at 04:15

mu is too short

426,620
70
833
800

1

Seems you also need to disable `mysql.trace_mode` in config if it defaults to "On" in your version of PHP. Also, it's still two queries :) – Phil Nov 09 '11 at 04:19
Great! [Here it is...](http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows) – Shomz Nov 09 '11 at 04:21
@Phil: So `mysql.trace_mode` sometimes causes queries behind your back that would mess up the `SELECT FOUND_ROWS()`? I tend to be more of a Ruby/PostgreSQL guy than a PHP/MySQL guy. – mu is too short Nov 09 '11 at 05:02
@muistooshort Something like that, I've never used it. More of an ANSI SQL guy myself :) See http://stackoverflow.com/questions/674061/sql-calc-found-rows-found-rows-does-not-work-in-php/869957#869957 – Phil Nov 09 '11 at 05:03
Thanks, this is how the code looked in the end in case someone finds it a useful reference: `$data = mysql_query("SELECT SQL_CALC_FOUND_ROWS * FROM Badges LIMIT 10"); $count = mysql_fetch_row(mysql_query("SELECT FOUND_ROWS()")); echo $count[0] ` – lisovaccaro Nov 09 '11 at 18:12

score 3 · Answer 2 · answered Nov 09 '11 at 04:12

It's pretty standard to issue two queries, one selecting the desired columns with the limit clause and another selecting only a count with no limit.

For example

$countQuery = 'SELECT COUNT(1) FROM Badges WHERE UID = ?';
$limitQuery = 'SELECT * FROM Badges WHERE UID = ? ORDER BY `Date` DESC LIMIT 0, 10';

score 0 · Answer 3 · answered Nov 09 '11 at 04:17

You have to make 2 queries: the first one will count all rows, the second one will return 10 rows:

$count = 0;
$query = "SELECT count(*) as count FROM Badges WHERE UID = '$user'";
$rs = mysql_query($query);   

if (mysql_errno() == 0)
{
    $r = mysql_fetch_object($rs);
    $count = $r->count;
}

if ($count > 0)
{
    $query = "SELECT * FROM Badges WHERE UID = '$user' ORDER by Date DESC LIMIT 10";
    $rs = mysql_query($query);      

    if (mysql_errno() == 0)
    {            
        while ($r = mysql_fetch_array($rs))       
        {       
            echo $r['Site']; 
        }     
    }
}

score -1 · Answer 4 · answered Nov 09 '11 at 04:16

-1

count all records

$data = mysql_query("SELECT * FROM Badges WHERE UID = '$user' ORDER by Date DESC");
$count = mysql_num_rows($data);
echo "No of Records is :" . $count;

print 10 records...

 $data = mysql_query("SELECT * FROM Badges WHERE UID = '$user' ORDER by Date DESC LIMIT 0, 10");
    while($row = mysql_fetch_array( $data )) 
        { 
        echo $row['Site'];
        }

answered Nov 09 '11 at 04:16

SB24

531
6
10

1

I'm guessing you got a -1 for proposing a `SELECT *` to perform a count operation (with an `ORDER BY` clause, no less!) when `SELECT COUNT(*)` is MUCH faster (and the "correct" way to do it). – Nov 09 '11 at 14:06

How to count all records but only retrieve (LIMIT) a specific number for display?

4 Answers4

Linked