Re: Counting results for pagination + limit?
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]
Re: Counting results for pagination + limit?
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
Counting results for pagination + limit?
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!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
