Originally Posted by ClubCosmic
For my own curiosity will this work?
$query = 'SELECT count(*), ID,name FROM users';
Not quite -- you can't mix aggregate functions with non-aggregate functions without having a GROUP BY clause. So you need something like
count(*) as mycount
This comes with another gotcha, though. The above will give you an error in any SQL except
MySQL, because we're selecting two non-aggregate fields (ID and name), but only grouping on one. But just because you're not getting an error in MySQL doesn't mean it's a good idea. For this query, there shouldn't be any problems, since ID'll be a unique value -- there will only be one name per ID, and the grouping is only a formality (this also means, of course, that your COUNT() value will always be 1
). If your fields aren't unique, though, you can get some pretty skewed results. So the standard-friendly query is
COUNT(*) AS mycount
As for efficiency and COUNT(), for something like this, COUNT(1) is best, since you don't have to pull anything out of the db itself.