MySQL: limiting the results of your query and show the total number of results

In an SQL query you can easily limit the results that are returned by using 'LIMIT start, records' at the end of the query.

$query = "SELECT id, name, city
FROM tbl_names
WHERE city="AMSTERDAM"
LIMIT 0,10";

$result = mysql_query($query);

Which will return the first ten names (LIMIT first=0, number of names=10).

In this case I am interested in the LIMIT option to reduce the results that I show but I also want to use the result of the query a little bit later on in the page to show the total number of results. In the above code I do not know the total number of results I would have gotten if I had not included the LIMIT statement.


To get the following result with one SQL query you can use the code below.
name 1
name 2
....
name 9
name 10

total number of names in Amsterdam: 34


The trick is to set the LIMIT in the $result = mysql_query($query); statement and not in the query itself.

$query = "SELECT id, name, city
FROM tbl_names
WHERE city="AMSTERDAM";

$result = mysql_query($query, " LIMIT 0,10");
$totalresult = mysql_query($query);
$totalnumberofnames= mysql_num_rows($totalresult);

$result is then used to show the 10 names and $totalnumberofnames is used to show '34' at the end. The query runs twice against your database but you only have to write the query once in your PHP.

Just make sure you do not forget the space between the " and LIMIT!