Count total Average rating with PHP

Question

How would I go about running a PDO query and then processing the results with PHP to output an Average rating for a specific blank based on user inputted reviews?

Each review is weighted on 2 criterias, Service and Price. Both of this are 0-5 therefore the end average is weighted out of 10. Per review bases I currently use the following code to calculate the Average Rating for the specific review.

$rating_total = $row['review_service_rating'] + $row['review_price_rating'];

$average_rating = ($rating_total / 100) * 100;

So for each review there are 2 sql rows for review_service_rating and review_price_rating both of this are out of 5.

However I'm not exactly sure how to Count all of the ratings and then determine base grade for the specific page out of 10.

So let's say we have 3 reviews. This reviews are 7/10, 8/10 and 10/10. Logically we would add up all of the numbers and then add up all of /10 giving us. 25/30 which translates to 83.33 or in my case 8/10. This are the 2 steps I'm uncertain how to do.

1 - Get all ratings from SQL and count them

2 - Determine Average /10 based on all ratings

Current PDO code to pull data from DB as well as Count of how many Reviews each particular user has.

$q = $db->prepare("SELECT * FROM reviews WHERE assigned_id = :review_id ORDER BY review_posted DESC");
         $q->execute(array(':review_id' => $_GET['page_id']));

         $r = $q->fetchAll(PDO::FETCH_ASSOC);

         echo '<br><div class="well">Average Rating</div><br>';

         foreach($r as $row) {

         $q = $db->prepare("SELECT * FROM users WHERE user_id = :review_user_id");
         $q->execute(array(':review_user_id' => $row['user_id']));
         $r = $q->fetch(PDO::FETCH_ASSOC);

         $q = $db->prepare("SELECT COUNT(*) FROM reviews WHERE user_id = :review_user_id");
         $q->execute(array(':review_user_id' => $row['user_id']));
         $user_reviews = $q->fetchColumn();

Isn't `$average_rating = ($rating_total / 100) * 100` just `$rating_total`? — ctwheels, Jun 29 '17 at 16:04
I keep seeing PDO in the tags and question, but I don't see anything database related. Databases can calculate the average for you in most cases — apokryfos, Jun 29 '17 at 16:06
@apokryfos added my current SQL and added some images to explain what's going on — , Jun 29 '17 at 16:12
Can you explain the structure of your review table. It's very unclear — apokryfos, Jun 29 '17 at 16:20
@apokryfos hows is it unclear? 1st SQL gets all data from DB for the Review Page, then we run foreach loop meaning it's going to pull data per Review bases (same concept as Posts on a forum, no?) in the foreach loop we pull User data and link it to the reviewers ID found in Review table. And the last line in the foreach counts rows in Reviews against User ID to determine how many Reviews user X has in total. Again giving it a read it's pretty straight forward tbh — , Jun 29 '17 at 16:29
Isn't `SELECT ( AVG(review_service_rating) + AVG(review_price_rating) ) AS average_rating, COUNT(review_service_rating) AS num_service_rating, COUNT(review_price_rating) AS num_price_rating FROM reviews WHERE assigned_id = :review_id ORDER BY review_posted DESC` useful to you? As averages vary from 0 to 5, just adding them will give you the sum from 0 to 10. — Alejandro Iván, Jun 29 '17 at 16:29
@AlejandroIván interesting. However I still need the service and price values for the Progress bar, because the progress bar is from 0 to 100%, so either way I still would need to call both of this and then turn 8/10 into 80% for the Bootstrap Progress bar to properly visualize data. — , Jun 29 '17 at 16:31
@0111010001110000 you have it. As you're adding averages, the result is below 10. So if you get `7.1`, just append the string `/10`. If you need the percentage, `( 7.1 / 10 ) * 100` (= 71%) will give it to you. — Alejandro Iván, Jun 29 '17 at 16:33
@0111010001110000 I suggest you take a step back and rethink your approach. It looks to me that you made a simple join into a loop and a lot of queries. The words you use "weighted" and "average" are not actually what you're dealing with here. You're just adding up 2 numbers. — apokryfos, Jun 29 '17 at 16:38
@apokryfos pretty much. As I said I'm just a hobbyist, I do this for fun, thus my code is bit like that. — , Jun 29 '17 at 16:39

Huy Trịnh · Answer 1 · 2017-06-29T16:20:17.503

1

Assume each user give x point (limit by 10), you must sum them up (x) then divide it by rating_times, you get average rating.

For example

 User 1 rate 7
 User 2 rate 6 
 User 1 rate 5

 => (7+6+5)/3 = 6

So you just add one more field when user rating called point. Then use query SUM to SUM point up, divided with query COUNT then done. forget the (x**/10**).

Finally, use 2 average rating (Price and service), sum of them, divided by 2 and you got overall rating.

edited Jun 29 '17 at 16:20

answered Jun 29 '17 at 16:13

Huy Trịnh

733
3
11

So from my understanding I would have to add criteria #1 (service) and criteria #2 (price) this will give me a value out of 10 combined points (each criteria is weighted out of 5). After that I would have PHP count total reviews for let's say Jake Motorcycle Repair shop. SQL would return 3 reviews. After which I would just run the math. – Jun 29 '17 at 16:17
Something like this? `function sum($a, $b) { return $a + $b; } $average_rating = explode('.', (string)(array_reduce($ratings, 'sum') / sizeof($ratings)))[0] . '%';` – Jun 29 '17 at 16:19

apokryfos · Answer 2 · 2017-06-29T16:45:05.063

0

Here's what you probably meant to do:

$q = $db->prepare("
      SELECT * 
      FROM review r 
          JOIN users u ON r.user_id=u.id  
          JOIN (SELECT user_id, COUNT(1) as count FROM review GROUP BY user_id) uc ON uc.user_id = u.id 
      WHERE r.assigned_id=:review_id 
      ORDER BY r.review_posted DESC");
$q->execute(array(':review_id' => $_GET['page_id']));
$r = $q->fetchAll(PDO::FETCH_ASSOC);

foreach ($r as $row) {
     // $row should have all fields included in $tables review and users as well as $row[count] would contain the total number of reviews a user made
}

This will include the user's details alongside the rating details for each review with that assigned_id

Edit: Added sum of user reviews as well.

edited Jun 29 '17 at 16:45

answered Jun 29 '17 at 16:32

apokryfos

38,771
9
70
114

Tried to add your code, broke the page haha. Anyways here is the full full code for the Review page. https://pastebin.com/raw/0m8b7Ycq – Jun 29 '17 at 16:42
@0111010001110000 might be worth doing a `print_r($r); die()` to see how the result of this query looks like (assuming it works, I haven't tested it) and see if it's something you can work with. – apokryfos Jun 29 '17 at 16:45
Changed the values to proper values and it works. Bit of a stupid question but what would I now use to display x Reviews for each user? – Jun 29 '17 at 16:55

score 0 · Answer 3 · answered Sep 02 '21 at 18:09

0

You can use this snippet. This helped me https://gist.github.com/tiagofrancafernandes/1c5752ba2ebeb477975c6c016a37fc5c

answered Sep 02 '21 at 18:09

DEV Tiago França

1,271
9
9

Count total Average rating with PHP

3 Answers3