[thelist] mysql count(*)

Adrian Fischer adrian at logo-logic.com
Mon Dec 11 19:01:55 CST 2000


Hi Gang,

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");


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?

I am also having some trouble coming to grips with developing an efficient
search for my table.  There are effectively 4 search criteria.  CATEGORY,
STATE, CITY and SUBURB.  The CATEGORY and STATE are mandetory but the other
two arent.  I want the search to be smart enough to know that they have or
have not put anything in either the last 2 selections. That is easy enough
with staright
 "if ($form{'category'} ne ""){ search the db using that input}same for
suburb.
The tough part is searchng the db.

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".

The if statements look like this:
if ($form{'SUBURB'} ne "")
  {
&suburb;
 }
if (($form{'REGION'} ne "") && ($varReturnResult <= 0))
  {
&region
  }
if  ($varReturnResult <= 0)
  {
&therest;
  }
if  ($varReturnResult <= 0)
 {
print "<tr><td colspan=4 align=center><b><font color=red>We didn\'t find
anything to match your search! </font></b></td></tr>";
 }

The SELECT statement at the top of the page is the guts of the searching
thing.  Just rewrite it , remove SUBURB  and replace it with CITY and youve
got the second search and the final one would be withouth the suburb or
city.

I think its long and convoluted.  Can any gurus out there shed some light on
how to do it smarter?

Was that to long winded?

Thanks

Adrian Fischer






More information about the thelist mailing list