Popularity order php sql

PHP
1

Hi there!

I have a sql table with the colums below:

location_id, user_id, location_city, rating, comment

I’d like to achieve something like this. I’d like to echo the restaurant actual position in descending order. What is the sql/php logic behind this?

colibri-flottant-solaire-ailes-de-plume-et-colibr
colibri-flottant-solaire-ailes-de-plume-et-colibr306×123 9.7 KB

2

Take a look at SQL functions AVG() and COUNT(). With these two functions you can figure out the rating average across all reviews and then the sum of the reviews. To use these functions, you can group by the location_id (assuming the location_id is the id to the restaurant).

Once you have these two pieces of data, you can order them in descending order on both of the columns to yield the restaurant with the highest rating with the most reviews.

For instance something like this…

SELECT location_id, AVG(rating) as rating_avg, COUNT(location_id) as review_count 
FROM restaurant_reviews 
GROUP BY location_id 
ORDER BY rating_avg desc, review_count desc;

Here you will see that this list will order the highest rated with the most reviews first. Now of course you can adjust this based on your own criteria, but you see how we can use AVG and COUNT to get the values. The first record in this result set would then be #1, the second would be #2 etc. :slight_smile:

3 Likes
3

(Because, in this system, a restaurant with 1 five…dot… thingy… rating, outranks a restaurant with 1015 reviews but a 4.99 rating.)

4

Thanks for the reply, it helps tho but thats not what I really want. It lists the restaurants in order by popularity, yes (I am also aware the problem m_hutley pointed out). But lets say I’m on the restaurant detail page: restaurant.php?id=52

I’d like to determine the actual rank of the restaurant like on the example (from tripadvisor) I uploaded above. Eg: 4# of 327 restaurants in New York

5

In Martyr’s example, he showed you how to derive the full set of restaurants. Whether you do the next step at the SQL end or the PHP end is up to you, but the principle remains the same…
The number of returned rows is the total number of restaurants.
The position of the specific restaurant’s row is its ranking.

1 Like
6

@m_hutley Is correct. He is picking up what I am putting down. :wink:

7

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.