Count select....

NZ_Kiwis

I have this code

$result2 = mysql_query ("SELECT COUNT(*) From news group by user asc limit 10");
while($row2 = mysql_fetch_array($result2)) {

$un = $row2['user'];
echo " ..";
echo $un;

Now it counts okay but I can not get the value of "user".. how can i do this??

dougal85

$result2 = mysql_query ("SELECT COUNT(*) as usercount From news group by user asc limit 10");
while($row2 = mysql_fetch_array($result2)) {

$un = $row2['usercount'];
echo " ..";
echo $un;

If i'm undertanding correctly and you want the count value, thats how you do it there. You need to give the value returned by COUNT an alias like above.

NZ_Kiwis

No quite, the count maybe 12 but what is the value of the field, ie
I have heaps of rows with username as "kiwis" for me.

I want a code which will count everything and list the top ten users.. may be me but it might be someone else...

it could be

dougal85 - 15
kiwis - 12
third - 5

So i need the count plus the username...

dougal85

$result2 = mysql_query ("SELECT usernamefield, COUNT(*) as xcount From news group by user asc limit 10");
while($row2 = mysql_fetch_array($result2)) {

$un = $row3['usernamefield'] . " - " . $row2['xcount'] . "<br/>";
echo " ..";
echo $un;

NZ_Kiwis

That is not quite what i'm after, it lists the names and count fine :-)

However it does not list the 10 ten in order by count?? how do i do this?

cgraz

NZ_Kiwis wrote:
That is not quite what i'm after, it lists the names and count fine :-)

However it does not list the 10 ten in ORDER BY count?? how do i do this?

Have you tried looking at the MySQL Manual? I added emphasis to your quote in my post - you're about 90% there...

NZ_Kiwis

$result2 = mysql_query ("SELECT userid, COUNT(*) as xcount From news group by user order by xcount desc limit 5");
while($row2 = mysql_fetch_array($result2)) {

still no luck, am i doing something wrong still??

cgraz

Are you getting an error? Incorrect results?

NZ_Kiwis

incorrect results

carlnz - 498
carlnz - 323
kiwis - 201
- 201
kiwis - 97

cgraz

so you're getting results for your user's more than once?

try GROUP BY userid

If that doesn't resolve it, could you post a CREATE TABLE statement and maybe even a few INSERT statements?