[thelist] mysql count(*)

[Adrian Fischer]

I need to know the total number that meet the select criteria first for
paging and then I select again to display (using $rowstart and $limit).  I
dont think I can get away with doing it all together.

I am also using this :$varReturnResult = $dbh->execute();  which will tell
me how many rows are returned but that is in the second select which has the
$rowstart and $limit stuck in there.$limit is 10 so $varReturnResult = 10.
And thats no good to me.  Therefore I think Im stuck with what Ive got.

As for the search thing...My first two criteria (CATEGORY and STATE) are
givens and are searched first.  The other to two criteria may or may not be
selected.  Thats not the issue.  If they are selected and nothing is found
in SUBURB then I need it to then drop back and search on CITY and if that
returns nothing then search on just CATEGORY and STATE alone.  If Im using
AND in my statements that implies that there must be a match for the query
to return true.  This is what Im trying to do:

"if SUBURB then search...if nothing found then search on CITY (if selected)
if nothing found on that either then search on CAT and STATE (which they are
told must be selected before we even start) if nothing found then return
"not found" "

So it always drops back to CAT and CITY as a last resort but I want them to
be able to put in all fields and then have the system still come up with a
result if either SUB or CITY does not return true.  Its not an AND type of
thing, and OR is not what I need either.

Not sure Im making myself clear here?

Adrian Fischer

> as far as conditionalizing your query, adrian, i gave a tip earlier today,
> it was for cold fusion, but i'm sure you can adapt it...
>
> >when generating exclusive WHERE conditions, avoid needless if/else
testing
> >by starting the WHERE clause with something guaranteed to be true --
> >
> >     select foo from yourTable
> >        where 1=1
> >
> >if no additional conditions are needed, i.e. selected by the user
(imagine
> >this query being dynamically tailored by user choices on a search form),
> >then the above query returns the entire table
> >
> >but when additional exclusive conditions are selected, you don't have to
> >bother testing each one (to see if it's the first one which would've
> >otherwise had to start with the WHERE keyword)
> >
> >just generate each one with an AND keyword, tacking it on to the query so
> >far --
> >
> >     select foo from yourTable
> >        where 1=1
> >   <cfif userField1Checked>
> >           and dbColumn1 = '<cfoutput>#userField1Value#</cfoutput>'
> >    </cfif>
> >   <cfif userField2Checked>
> >           and dbColumn2 = '<cfoutput>#userField2Value#</cfoutput>'
> >    </cfif>
>
>
> hope that helps some...
>
>
> rudy
>
>
>
> ---------------------------------------
> For unsubscribe and other options, including
> the Tip Harvester and archive of TheList go to:
> http://lists.evolt.org Workers of the Web, evolt !
>