[thelist] MySQL Total number of results with LIMIT set

[Paul Waring]

On 5/26/05, Simon Perry <thelist at si-designs.co.uk> wrote:
> In short I would like to be able to display that there are, say, '374'
> matches split over '13' pages but I just can't seem to get the number of
> matches.

There are two ways in which you can do this:

1. Get the whole result set (SELECT * FROM table) and use PHP to
extract the relevant results and the count() function or
mysql_num_rows() to find the total number of rows.
2. Get a count of the rows in one query:

$result = mysql_fetch_array(mysql_query(SELECT COUNT(*) AS row_count
FROM table));
$total_row_count = $result['row_count'];

and then do your LIMIT query as normal - this way you should hopefully
not get the entire result set returned to PHP (although MySQL will
still have to select all the rows in order to count them).

There may be a built-in MySQL function to count the number of rows in
a given table other than using COUNT() but I don't know of one.

Hope this helps.

Paul

-- 
Rogue Tory
http://www.roguetory.org.uk