CODEWITHSUNDEEP
phpmysql
iosfreak
iosfreak

Reputation: 5238

Get top 5 most popular items in mysql database?

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

Answers (5)

Rajesh Kannan
Rajesh Kannan

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

mpdaugherty
mpdaugherty

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

ataylor
ataylor

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

Marc B
Marc B

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(" &amp; ", $results);

Upvotes: 17

mpen
mpen

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

Related Questions