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'];
    }

Answer 1

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.

Answer 2

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';