Count loop

[Mutley]

I'm wanting to count how many "1" there are in a mysql table under a column called "xyz" then loop it to display the top 5 users (user_id) who has the mosts "1"s

 

My database table is like this:

user_id | xyz

1        |

1        | 1

2        | 1

2        | 1

 

So then on my page it shows:

 

User:  |  XYZs:

2      | 2

1      |  1

 

I hope that makes sense, I don't know how to loop a SELECT COUNT in this way.

[monk.e.boy]

select count(id)

from table

where xyz = 1

group by user_id

 

I think the above will do it. The group by is what you want. Also google 'mysql having'

 

monk.e.boy

[Mutley]

So something like this:

 

$sql = "SELECT COUNT(*) FROM table WHERE xyz = '1' GROUP BY user_id";

$result = mysql_query($sql);

echo $result;

 

Except how do I make it loop (and work, lol)?

[Mutley]

???

[hitman6003]

$query = "SELECT userid, SUM(xyz) AS hits FROM table_name GROUP BY userid ORDER BY hits DESC";
$result = mysql_query($query) or die(mysql_error());

echo '
  <table>
    <tr>
      <td>Userid</td>
      <td>Count</td>
    </tr>';

while ($row = mysql_fetch_assoc($result)) {
  echo '
    <tr>
      <td>' . $row['userid'] . '</td>
      <td>' . $row['hits'] . '</td>
    </tr>';
}

[Mutley]

Where do I state only if:

xyz = '1'

 

...though?

[hitman6003]

if there is no value for xyz, it won't be added to the sum...conversely, if it has a value more than 1, that value will be added to the sum.

 

To answer your question...the same place you always would....after the FROM statement, before the GROUP BY statement.

[Mutley]

$query = "SELECT userid, SUM(xyz) AS hits FROM table_name WHERE xyz = '1' GROUP BY userid ORDER BY hits DESC";

 

Doesn't appear to work, just says "0".

[monk.e.boy]

Yeah well, SQL is tricky.

 

monk.e.boy

[Mutley]

Still unable to get this to work.