[thelist] mysql count(*)

Warden, Matt mwarden at odyssey-design.com
Mon Dec 11 19:51:50 CST 2000


> I'm using mysql count(*) to return the number of rows found that meet a
> criteria and then using the same criteria to display the items found sorta
> like this:
>
>  $dbh=$db->prepare("SELECT COUNT(*) FROM tblDockets WHERE docAccountEnd >= "
> . int($current_time) . " AND   docCat = '" . sqlEncode($form{'CATEGORY'}) .
> "' AND   docState = '" . sqlEncode($form{'STATE'}) . "' AND docSuburb  = '"
> . sqlEncode($form{'SUBURB'}) . "' AND docClosed <= 2 AND docPending=0");

Rudy will probably chime in on this, but I've always used "SELECT COUNT(1)
FROM ...". You don't really need to return all (or any) columns to count the
number of rows returned. Note that "SELECT 1 FROM ..." will return 1 for each
and every row from the selected table that satisfies the where clause if one
is present.

> To get the numbers of rows (for paging)
> then this :
>
> $db_query = "SELECT
> docId,docCat,docBusName,docStreet,docBusPhone,docDesc1,docState,docRegion,do
> cSuburb,docHits FROM tblDockets WHERE docAccountEnd >=  " .
> int($current_time) . " AND   docCat = '" . sqlEncode($form{'CATEGORY'}) . "'
> AND   docState = '" . sqlEncode($form{'STATE'}) . "'  ";
> $db_query .= " AND docSuburb  = '" . sqlEncode($form{'SUBURB'}) . "'";
> $db_query .= "AND docClosed <= 2 AND docPending=0 ORDER BY docSuburb
> $startrow, $limit";
>
> to display the item.  I think there is an element of redundancy in this?
> Can I include the count function some how into the second select so I only
> access the db the one time?

Um... yes and no. Here's the skinny. The database returns a matrix-like object
(remember matrices? -- as in, not the movie ;-). If you'll recall, a matrix
looks a little like:

{    4,    5,    6    }
{    5,    4,    6    }
{    5,    5,    4    }

This is a 3x3 matrix. You can have any ZxY matrix (for all positive integers Z
and Y), but they have to be defined by the demensions Z and Y. So, you're
returning a list of fields for every row, ten to be exact. Let's say you have
50 rows that satisfy the where clause. (I always forget whether rows or
columns go first...) So, you have a 10x50 matrix that is being returned to you
by the database. You get ONE value returned by Count(*). It's called an
aggregate function. Where are you going to put it? Are you going to create a
new column and have that same value repeated 50 times (once for every row
returned)? Nope, you have to return two sets, AFAIK.

> Ive had it set up so that if they do input somethng in sbuburb a search is
> done with that added to to the CAT and STATE.  Then if they use CITY I do a
> seperate search using that on the end of the CAT and STATE. All in all I
> have three seperate searchs written. One for suburb, one for city and then
> one to cover the rest.  I was trying to get it to degrade gracefully.  If
> all fields were filled in and nothing was found in Suburb then it would fall
> back to searching on Cat, State and City.  If nothing was found in city then
> it searched on Cat and State.  If nothing found in either of those then
> display "nothing found".
...
> I think its long and convoluted.  Can any gurus out there shed some light on
> how to do it smarter?

You can do this with a single select statement and a dynamically-created SQL
statement. I'm pretty confident that someone else will offer a better solution
than I could, so I'll save myself the time of figuring it all out. However, if
no one helps you soon, feel free to email me offlist and I'll delve in.


--
mattwarden
mattwarden.com





More information about the thelist mailing list