List:General Discussion« Previous MessageNext Message »
From:Gerald Clark Date:October 14 1999 1:00pm
Subject:Re: using limit and also getting total count
View as plain text  
Jay J wrote:
> 
> ----- Original Message -----
> From: Jerry Preeper <preeper@stripped>
> To: <mysql@stripped>
> Sent: Wednesday, October 13, 1999 4:10 PM
> Subject: using limit and also getting total count
> 
> > I have the following sql query I am using and was wondering if there is
> any
> > way of getting a total count of matching items in the database but
> limiting
> > the results to 3 or whatever number of items.
> >
> > I have shortened the query somewhat for irrelevant stuff:
> > $sql_query = "select id,date,headline,type,story from jpnews ";
> > $sql_query .= "where type=1 and status=1 "
> > $sql_query .= "order by id desc limit 3";
> > $sql_result = $dbh->Query($sql_query);
> > $howmany = $sql_result->numrows;
> >
> > Naturally, $howmany just gives me 3 on a query I tested that returns 6
> > without the limit.  I want the total count so I can display a more tab at
> > the end of everything with the number of additional matching records and a
> > link to another program that runs a different query when retrieving those
> > items.  I also want to keep the limit since the stories are all in the
> > database as text blobs and there is no sense returning a lot of data that
> > I'm just not going to use in this query.
> >
> > Do I need to do two separate queries (one for the count plus the one
> above)
> > or is there some efficient way to get this into the same query?
> >
> > Thanks
> > Jerry
> 
> No, you're forced to run a seperate count. (I'd love to be wrong!)
> 
> On this topic though, I was wondering ... since this query is SORTed with a
> WHERE clause, isn't MySQL forced to examine every record matching the WHERE,
> _then_ sort (at least once)? So doesn't it know the total count anyway? If
> so, why not return it on queries where this is the case?
> 
> Just wondering,
> 
> -Jay J

Or ask for 4, display 3, and if you got 4, run the next query.
Thread
using limit and also getting total countJerry Preeper13 Oct
  • Re: using limit and also getting total countJay J14 Oct
    • Re: using limit and also getting total countBob Kline14 Oct
      • Re: using limit and also getting total countJay J14 Oct
        • Re: using limit and also getting total countBob Kline14 Oct
          • Re: using limit and also getting total countJerry Preeper14 Oct
          • Re: using limit and also getting total countRyan Dickinson18 Oct
        • Re: using limit and also getting total countMichael Widenius14 Oct
      • Re: using limit and also getting total count [cacheing query answers]Jay J16 Oct
    • Re: using limit and also getting total countMichael Widenius14 Oct
  • Re: using limit and also getting total countGerald Clark15 Oct