[Found_Count] & MySQL

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

[Found_Count] & MySQL

Marc Lucke
If I use Found_Count on a record set I've limited using MySQL's LIMIT
command, naturally I'll get the count of how many records are being
returned by MySQL - i.e. LIMIT or less.  So how can I have my cake and
eat it too?  Do I need 2 SQL statements - one to count how many records
matched and one to return the limited set?  I want to use LIMIT for
performance reasons but I still want to report to the user how many
records matched.

Like this:

* SELECT * from this_table WHERE words='bah bah black sheep' LIMIT 10,20
* Found_Count would return 10
* SELECT COUNT(*) as Found_Count from this_table WHERE words='bah bah
black sheep'
* [Field: 'Found_Count'] would return the correct result

What's the best way to handle this?

--
Marc

------------------------------
If you missed the Summit, you can still learn from the best at a Lasso Master Class near you!
http://www.omnipilot.com/id.lasso?tid=91

------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: [Found_Count] & MySQL

Michael Coninx
Marc Lucke wrote:

> If I use Found_Count on a record set I've limited using MySQL's LIMIT
> command, naturally I'll get the count of how many records are being
> returned by MySQL - i.e. LIMIT or less.  So how can I have my cake and
> eat it too?  Do I need 2 SQL statements - one to count how many
> records matched and one to return the limited set?  I want to use
> LIMIT for performance reasons but I still want to report to the user
> how many records matched.
>
> Like this:
>
> * SELECT * from this_table WHERE words='bah bah black sheep' LIMIT 10,20
> * Found_Count would return 10
> * SELECT COUNT(*) as Found_Count from this_table WHERE words='bah bah
> black sheep'
> * [Field: 'Found_Count'] would return the correct result
>
> What's the best way to handle this?
>
Add "SQL_CALC_FOUND_ROWS" to your query.
Perform the query stated below, the field named "found_rows" will give
you the number you are looking for

* SELECT SQL_CALC_FOUND_ROWS * FROM this_table WHERE words='bah bah
black sheep' LIMIT 10,20
* SELECT FOUND_ROWS() as found_rows

------------------------------
If you missed the Summit, you can still learn from the best at a Lasso Master Class near you!
http://www.omnipilot.com/id.lasso?tid=91

------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage