[thelist] mysql count(*)

rudy r937 at interlog.com
Mon Dec 11 20:33:31 CST 2000


> Rudy will probably chime in on this, but I've always
> used "SELECT COUNT(1) FROM ...".

thanks matt   ;o)

you could also use the more traditional SELECT COUNT(*) which just counts
rows (and is free to use any available indexes on the table, not that
SELECT COUNT(1) might not, it might, but then again it might actually
generate a result set, and with so many different databases out there, who
really knows unless you test it...)

> Nope, you have to return two sets, AFAIK.

actually, the number of rows returned should be available to the scripting
language, and you should *not* have to do two queries (yuck)

in cold fusion you can use the query variable RecordCount, which gives you
the total number of records returned by the query, and i'm pretty sure
there's a similar thingie in ASP, so on that basis, i did a quick search at
www.php.net and i think pg_numrows might do what you want...

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






More information about the thelist mailing list