CODEWITHSUNDEEP
phpmysqldatabasepdo
Wayne Allen
Wayne Allen

Reputation: 49

pdo list top 5 using avg?

Ok once again I am struggling making my movie rating system :(

My table structor is 

      ID | UserID | Rating | TMDB |  TYPE
      -----------------------------------
       1      34        6     432      2
      -----------------------------------
       2      34        9     432      3
      -----------------------------------
       3      44        9     468      2

Thanks to user Barmar I was able to work out the average using

$sql = "SELECT AVG(rating) AS avg_rating FROM `tbl_rating` WHERE `tmdb`= :tmdb AND `type`= :type ";
$stmt = $conn->prepare($sql);
$stmt->bindParam(':tmdb', $tmdb);
$stmt->bindParam(':type', $type);
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_ASSOC);
$avg_rating = $row['avg_rating'];

Now I want to make a top 5 list of gorey horrors (type = 2). I have absolutely no clue how to work this out :( I know i can limit the results by a simple LIMIT command at the end and I think ORDER by AVG(rating) is need? and I need to drop the WHERE tmdb. I have no idea how to achive this could anyone help please!

Upvotes: 2

Views: 618

Answers (1)

Asaph
Asaph

Reputation: 162831

You can modify your query to group by tmdb (instead of filter by), order the results by avg_rating (descending) and limit the rows returned to 5. Something like this:

SELECT `tmdb`, AVG(rating) AS avg_rating
FROM `tbl_rating`
WHERE `type`= :type
GROUP BY `tmdb`
ORDER BY avg_rating DESC
LIMIT 5

Proof of concept:

You can run the following SQL commands in the MySQL console to validate the query returns sensible results on your sample data.

create table tbl_rating (
ID int unsigned primary key auto_increment,
UserID int unsigned,
Rating smallint unsigned,
tmdb int unsigned,
type int unsigned) engine=innodb;

insert into tbl_rating
(UserId, Rating, tmdb, `type`) values
(34, 6, 432, 2),
(34, 9, 432, 3),
(44, 9, 468, 2);

SELECT `tmdb`, AVG(rating) AS avg_rating
FROM `tbl_rating`
WHERE `type`= 2
GROUP BY `tmdb`
ORDER BY avg_rating DESC
LIMIT 5;

+------+------------+
| tmdb | avg_rating |
+------+------------+
|  468 |     9.0000 |
|  432 |     6.0000 |
+------+------------+
2 rows in set (0.01 sec)

Upvotes: 2

Related Questions