Page 1 of 1

Getting a row count where a limit and a join is being used!

Posted: Wed Aug 03, 2005 5:12 am
by icarpenter
Hi does anyone know of a way that I can return the overall row count of query where a limit is being implemented..

ie:- Say I have a query with 100 rows of info...

Code: Select all

SELECT * FROM table1 LEFT JOIN table2
ON id=id
LIMIT 0,10
In the above query the I get 10 rows returned using limit and in PHP I can use this value by calling...

Code: Select all

mysql_num_rows($result);
And the the value is 10...I would like to get the value 100! I have tried count(*) to no avail...Is there anything else I can try, other than to run the query a second time without the limit?

Any help would be very much appreciated.

Rgds Ian.

Posted: Wed Aug 03, 2005 6:15 am
by dreamline
Maybe not the best solution but i use 2 queries for those.. First a count(*) without a limit and then for printing the info with the limit value...

But i must say i haven't taken the trouble to see if there is a way to use just one query for that purpose..

Posted: Wed Aug 03, 2005 6:21 am
by Skittlewidth
Hi does anyone know of a way that I can return the overall row count of query where a limit is being implemented..

ie:- Say I have a query with 100 rows of info...

PHP:
SELECT * FROM table1 LEFT JOIN table2
ON id=id
LIMIT 0,10

In the above query the I get 10 rows returned using limit and in PHP I can use this value by calling...

PHP:
mysql_num_rows($result);


And the the value is 10...I would like to get the value 100! I have tried count(*) to no avail...Is there anything else I can try, other than to run the query a second time without the limit?
Correct me if I'm wrong but 10 is the overall count for this query. The limit clause will only return the 10 rows specified, it doesn't return all 100 and then just display 10. So it will require a second query without Limit.....I think :?

Posted: Wed Aug 03, 2005 4:58 pm
by feyd
correct.. lose the limit for a count() aggregator query, then use the full selection with limit to get the current result set.

Posted: Thu Aug 04, 2005 7:16 am
by icarpenter
Oh well, was worth a try....

Thanks!!!