Chris <[EMAIL PROTECTED]> wrote on 08/30/2005 01:51:34 PM:
> Greetings,
>
> We have a few queries that we use against our product database. We pull
these
> results, and only display 50 rows per page via our web interface (then
use
> "next and back page" links). An example query is:
>
> select distributer.short_desc,
> distributer.sku,
> distributer.avail_code,
> distributer.msrp,
> distributer.dealer,
> IF( ISNULL(distributer_classmap.description),
> distributer.brand_code,
> distributer_classmap.description
> ),
> distributer.msrp - distributer.dealer as profit,
> distributer.family from distributer
> Left Join distributer_classmap on
> distributer_classmap.code = distributer.brand_code
> where avail_code = ('AA' or 'A' or 'B')
> and
> (distributer.brand_code = ? or distributer_classmap.description = ?)
> LIMIT ?,50
>
> What I need to do is be able to count the *total* number of results
> this query
> generates, so I can build the offset numbers for the proper number of
"next
> page" links.
>
> Since I am using a limit clause, if i were to count in my app how many
rows
> there are I would get 50 as that is what the limit is set for.
>
> The only other way I know of to get the total results is to use COUNT. I
> really am not that great with SQL, so I don't know how I would apply a
count
> statement to the above SQL.
>
> I would like to keep the counting of total results, along with the
LIMIT'ed
> result statement into 1 query if possible.
>
> Any hints would be greatly appreciated.
>
> I am using MySQL 4.1.13a
>
> Thanks!
>
You want to check out the FOUND_ROWS() function:
http://dev.mysql.com/doc/mysql/en/information-functions.html
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
