help sorting in total order DESC

[EchoFool]

Hey guys, i have a while loop calculating the total number of users with types of weapons in my game.. how ever i could not add an ORDER BY or at least i don't think i could... but i would like the most popular used weapon to be at the top and the worst at the bottom, in other words DESC based on how many users have it.....though i do not believe mysql can do this ORDER BY, so how can it be done via php?

 

This is my script:

<?php
$Select = mysql_query("SELECT WeaponID,Name FROM weapons ORDER BY WeaponID DESC")
	Or die(mysql_error());
While($row = mysql_fetch_assoc($Select)){
	$WeaponID = $row['WeaponID'];
	$WeaponName = $row['Name'];
$Total = 0;
$Count = mysql_query("SELECT WeaponID FROM userweapons WHERE WeaponID='$WeaponID'")
	Or die(mysql_error());
While($row2 = mysql_fetch_assoc($Count)){
$Total = $Total + 1;
}	
Echo $WeaponName.'['.$WeaponID.'] - '.$Total.' users';
Echo '<br><br>';
}
}
?>

 

Hope you can point me in the right direction

Thank you!

[Goldeneye]

Do you get an error when you use ORDER BY?

[EchoFool]

No but to ORDER BY the "total number of users that own each weapon" ...

 

would not be possible via mysql ORDER BY based on the above script, if it is possible where do i put the ORDER BY cos I cannot see where it fits logically?

[.josh]

Think what you're looking for is using a GROUP BY with your ORDER BY

[EchoFool]

Think what you're looking for is using a GROUP BY with your ORDER BY

 

I will try it and see what happens.

 

Edit:

I'm afraid that didn't work as you can see here:

wep46[46] - 0 users

wep45[45] - 22 users

wep44[44] - 1 users

 

As you can from above, it should be in order of:

Wep 45

Wep 44

Wep 46

 

Based on how many people own each weapon.

[.josh]

can you post the query you tried

[.josh]

Well anyways I think this is what you're looking for...

 

SELECT WeaponID, Name FROM weapons GROUP BY WeaponID ORDER BY WeaponID DESC

 

[EchoFool]

Well heres all 3 of my attempts with different approaches of which all 3 failed to echo in the correct order:

 

This was attempt 1:

<?php
$Select = mysql_query("SELECT WeaponID,Name FROM weapons ORDER BY WeaponID DESC")
	Or die(mysql_error());
While($row = mysql_fetch_assoc($Select)){
	$WeaponID = $row['WeaponID'];
	$WeaponName = $row['Name'];
$Total = 0;
$Count = mysql_query("SELECT WeaponID FROM userweapons WHERE WeaponID='$WeaponID'")
	Or die(mysql_error());
While($row2 = mysql_fetch_assoc($Count)){
$Total = $Total + 1;
}	
Echo $WeaponName.'['.$WeaponID.'] - '.$Total.' users';
Echo '<br><br>';
}
}
?>

 

 

This was attempt 2 which you suggested:

 

<?php
$Select = mysql_query("SELECT WeaponID,Name FROM weapons GROUP BY WeaponID ORDER BY WeaponID DESC")
	Or die(mysql_error());
While($row = mysql_fetch_assoc($Select)){
	$WeaponID = $row['WeaponID'];
	$WeaponName = $row['Name'];
$Total = 0;
$Count = mysql_query("SELECT WeaponID FROM userweapons WHERE WeaponID='$WeaponID'")
	Or die(mysql_error());
While($row2 = mysql_fetch_assoc($Count)){
$Total = $Total + 1;
}	
Echo $WeaponName.'['.$WeaponID.'] - '.$Total.' users';
Echo '<br><br>';
}
}
?>

 

This was attempt 3:

 

<?php
$Select = mysql_query("SELECT WeaponID,Name FROM weapons GROUP BY WeaponID ORDER BY WeaponID DESC")
	Or die(mysql_error());
While($row = mysql_fetch_assoc($Select)){
	$WeaponID = $row['WeaponID'];
	$WeaponName = $row['Name'];
$Total = 0;
$Count = mysql_query("SELECT COUNT(WeaponID) AS WeaponCount FROM userweapons WHERE WeaponID='$WeaponID' ORDER BY WeaponCount")
	Or die(mysql_error());
While($row2 = mysql_fetch_assoc($Count)){	
Echo $WeaponName.'['.$WeaponID.'] - '.$row2['WeaponCount'].' users';
Echo '<br><br>';
	}
}
?>

 

[.josh]

okay wait wait wait, you said you want to list out how many of each type of weapon is used by players, in descending order? Shouldn't you be doing your group by on your userweapons table?

[Barand]

try

SELECT w.WeaponID, w.Name, COUNT(*) as usercount
FROM weapons w
INNER JOIN userweapons u USING (weaponID)
GROUP BY w.WeaponID
ORDER BY usercount DESC