Getting Results: mysql_num_rows() and LIMIT

Anon

When you use LIMIT in your sql statement, mysql_num_rows returns the number of rows returned after the limit statement in your sql.

So, if your sql statement was something like:
SELECT * FROM ... LIMIT 0,10 - mysql_num_rows() would return 10. (assuming there were at least 10 results) even thought there could be many more than 10 results from the select.

So how do you get the total number of results returned overall, as if your sql statement omitted the LIMIT part?

That way you could limit the number of results displayed, but you would also know the total number of rows returned by your SELECT. Do you really have to run the sql statement twice?

Many thanks,
matt.

Anon

Yep, I'm afraid so, you have to do two queries, thou to speed things up you shouldn't do the full query each time...

First to get the total number of items from your query you'd use a count(*) statement instead of using the mysql_num_rows, this is was faster as it doesn't require all that nasty data transfer stuff and is handled internaly by MySQL.

SELECT COUNT(*) FROM ...

Then you would retrieve this value from the array like so

$row = mysql_fetch_array($result);
$count = $row["COUNT(*)"];

Next you would do your second query as normal, like so...

SELECT * FROM ... LIMIT 0,10

This seems to work quite fast and is almost unnoticable as far as my bench tests go.

Anon

Hello,

What is going to be the difference between using:

mysql_fetch_array (as you used)

and:

mysql_fetch_row

Later.
JbA

Anon

mysql_fetch_array returns a nice little array that can then be accessed really easy like so:

$row = mysql_fetch_array($result);
$item1 = $row["gaw"];
$item5 = $row["flubber"];

instead of the nasty and easy to break mysql_fetch_row which returns the values ub ab array that is access by the number in which it is retrieved.

$row = mysql_fetch_row($result);
$item1 = $row[1];
$item5 = $row[5];

Now say you wanted to suddenly take an item out of the results to save on query execution time, then the mysql_fetch_row row numbers would change and each item request would need to be modified, but the mysql_fetch_array items would still maintain their names and would still work without modification. It's just much easier to read and to maintain.