How to retrieve the total number of rows when using LIMIT X, XXX

[ichsie]

With php+mysql, $results -> num_rows returns the number of rows of the current query, however with LIMIT X, XXX in that query the $results -> num_rows only contains the LIMITED number of rows rather than the total amount of rows meeting the WHERE clause without LIMIT.

e.g. With LIMIT 0, 100 in my query $results -> num_rows returns 100 rather than 2000 which is the total number of rows had I not limited the returning results for paging.

Some search site has the capability of paging big amount of results while displaying also the total amount. Does it require 2 or more queries to do this? Is it possible to do it with just 1 query?

So what's the most efficient way to achieve this:

Searching results 1 - 100, 2,653 total
...
...

 

[Dave]

Yes it does require two queries for pagination. The first query is to get the total number of rows (using count) and the second is to limit by an offset and maximum value. Lets say you wanted to display 20 results per page and you wanted to display page number 3. This is how you would do it in procedural style.

<?php

// NOTE: This is just a quick basic example and as such you will need to
// improve the security of this yourself. If you are using PHP 5 I highly
// recommend the mysqli extension.

// How many results to show per page?
$rows_per_page = 20;

// Fetch the page number. In the page of the query string this would be 3.
$current_page = isset($_GET['page']) ? (int) $_GET['page'] : 1;

// Count the maximum number of rows from the table. Make sure you add a 
// where clause yourself so you only get rows you need.
$result = mysql_query("SELECT COUNT(id) AS num_rows FROM your_table");
$row = mysql_fetch_array($result, MYSQL_ASSOC);
$num_rows = $row['num_rows'];

// Now we work out the total number of pages
$total_pages = ceil($num_rows / $rows_per_page);

// The offset is the row we start searching from in our database
$offset = ($current_page - 1) * $rows_per_page;

// This is where you get all rows for the current page now
$result = mysql_query("SELECT * FROM your_table LIMIT " . $rows_per_page . " OFFSET " . $offset);

// and then do whatever else you need here
?>

Displaying 1 - 20 of 567 is simple. In your template, or html code you replace each of the following as so.

<?php

// In the above example the first item is the 1 and the last item is the 20
$first_item = $offset + 1;
$last_item = ($first_item + $rows_per_page) - 1;

// The 567 is of course just the $num_rows
?>

Thanks and I hope this has helped you out.

:tu:

 

[ichsie]

Thanks a lot, it helps. =)

 

[Peter]

1 way to lower overhead for this would be to actually do a subquery. You could have a subquery as 1 of the rows you are selecting.

This will not lower the overhead for the database engine but will lower the overhead for PHP as it will only make 1 call to the DB. This option may not be open to you however, it depends on your mySQL version.