----- Original Message -----
From: Jerry Preeper <preeper@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?
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?