CODEWITHSUNDEEP
phpmysql
4world2c
4world2c

Reputation: 31

Pagination - Always returns ALL the records in the database

(a) I have Pagination code BEFORE I ask my query:

$query = "SELECT COUNT(*) as num FROM $tableName";
$total_pages = mysql_fetch_array(mysql_query($query));
$total_pages = $total_pages[num];

The $total_pages is counting ALL the records in the database ALL THE TIME, because the code listed above is counting ALL the pages BEFORE I make the query - I want the records counted AFTER the results of the query, and get the proper number of pages out of those results.

After a series of code lines for my SELECT statement, , my query ends like this ( which is working fine - except for Pagination problems outlined above).

$query = "SELECT * FROM $tableName $qryWhere LIMIT $start, $limit";
$result = mysql_query($query);

Following the above, the rest of the Pagination code follows, as well as query results. They all work. It's those 3 first lines counting ALL the database pages BEFORE my query that is the problem.

Thank you in advance for any assistance you may be able to offer me !

Upvotes: 0

Views: 326

Answers (2)

Marc B
Marc B

Reputation: 360632

FOUND_ROWS() only works if you've done a select query with the SQL_CALC_FOUND_ROWS modifier. Otherwise it just returns 0:

$sql = "SELECT SQL_CALC_FOUND_ROWS * FROM $tableName $qryWhere LIMIT $start, $limit;";
$result = mysql_query($sql) or die(mysql_error());
$found = mysql_query("SELECT FOUND_ROWS();") or die(mysql_error());

$result would contain only the rows specified in your limit clause, while $found will contains how many rows WOULD have been found without the limit clause at all.

Upvotes: 0

BenOfTheNorth
BenOfTheNorth

Reputation: 2872

If you want to get the total number of results (not just the amount your displaying at any one time), you can run this query AFTER you have run your 'SELECT * FROM $tableName $qryWhere LIMIT $start, $limit' query:

SELECT FOUND_ROWS();

That will tell you the number of matching rows, regardless of the limits you applied to the intial query.

If you want to find out how many rows you have pulled out in the last query instead, try modifying it to:

SELECT *, COUNT(id) as total FROM $tableName $qryWhere LIMIT $start, $limit

This effectively creates a new column in your results with the total results your pulling out with the limits applied. (I'm assuming you have a column called 'id', if not, use a different column name for the count.

Upvotes: 1

Related Questions