On Tuesday 30 August 2005 02:30 pm, [EMAIL PROTECTED] wrote:
> 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
Thank you, works great!!!!
-c
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
