dieselmachine
-
Posts
2 -
Joined
-
Last visited
Never
Posts posted by dieselmachine
-
-
I am doing some volunteer work on a site that uses a lot of mysql databases, and while looking through the files for code to optimize, i found a script which does this:
(for background, there is a table called users full of user accounts, and each one has a rank. there are 8 different ranks, and the purpose of this script is to tally how many users are of each rank and display the info).
i'm still kind of new to php/mysql, so if my syntax is a bit off, forgive me. It's not the syntax I really care about, just the question of runtime.
SELECT count(*) as total1 FROM users WHERE rank='1'
//send query, get row
$level1users = row['total1'];
SELECT count(*) as total2 FROM users WHERE rank='2'
//send query, get row
$level2users = row['total2'];
... and so on, up to 8. My question is, could I save time by running a single iteration through the table and incrementing 8 different variables based on the value of 'rank'? It seems to me this current script is going through the entire table 8 different times, which (in my mind) makes me think it could be optimized.
I'm thinking:
SELECT rank FROM users;
//send query
while{
array[$row['rank']]++;
}
then at the end, print the 8 variables in the array. Would this be more efficient?
Thanks in advance for any guidance.
question about mysql count(*)
in PHP Coding Help
Posted
You want to use [a href=\"http://dev.mysql.com/doc/refman/4.1/en/group-by-functions-and-modifiers.html\" target=\"_blank\"]GROUP BY[/a].
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] rank, [color=blue]COUNT[/color](*) [color=green]AS[/color] total [color=green]FROM[/color] [color=orange]users[/color] GROUP BY rank [!--sql2--][/div][!--sql3--]
[/quote]
You are totally awesome, thanks a ton dude!