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
> > way of getting a total count of matching items in the database but
> > 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
> > 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.