Reputation: 5238
I have a database that looks something like this:
user_id photo_id
1 1
1 2
1 3
1 4
2 5
2 6
I want to get a list of the most popular users from it. Like this:
Popular Users: 1 (4) & 2 (2)
How would I go about doing that in mysql in PHP?
Thanks, Coulton
PS: I do know much about mysql commands so you don't have to dumb it down. Thanks!
Upvotes: 3
Views: 17790
Reputation: 1
SELECT COUNT(a.category_id) as cnt,b.category,b.image FROM bookings as a
INNER JOIN service_category as b ON a.category_id=b.category_id
GROUP BY a.category_id ORDER BY cnt DESC LIMIT 6
Upvotes: 0
Reputation: 1170
This can be accomplished using only SQL commands. Here's what I'd do:
SELECT user_id, count(user_id) uid_count
FROM <<table>>
GROUP BY user_id
ORDER BY uid_count DESC
LIMIT 5;
GROUP BY collects rows that all have the same user_id, and ORDER BY ... DESC sorts the results in descending order, so the first rows represent the most popular users. LIMIT gives you the top 5 results.
Upvotes: 2
Reputation: 66109
The database query will look something like this:
select user_id, count(photo_id) as c
from table group by user_id
order by c desc limit 5;
In PHP, it would look something like this:
$sql = 'select user_id, count(photo_id) as c from table group by user_id order by c desc limit 5';
$result = mysql_query($sql, $link);
if (!$result) {
echo "DB Error, could not query the database\n";
echo 'MySQL Error: ' . mysql_error();
exit;
}
while ($row = mysql_fetch_assoc($result)) {
echo $row['user_id'];
echo $row['c'];
}
Upvotes: 1
Reputation: 360912
The basic query would be:
select user_id, count(user_id) as cnt
from yourtable
group by user_id
order by cnt desc
To display the results, something like:
$results = array()
while($row = mysql_fetch_assoc($query_result)) {
$results[] = "{$row['user_id']} ({$row['cnt']})"
// repeat for however many results you want
}
echo "Popular user: ", implode(" & ", $results);
Upvotes: 17
Reputation: 283355
select user_id, count(user_id) as count from table order by count desc group by user_id
something like that anyway...
Upvotes: 5