PHP/MySQL join statement

Question

I am trying create an average rating for a store listing using MySQL's `AVG` and then displaying the average next to each store on a directory list page. Every time someone leaves a review for a store, they give a rating of 1-5, which is inserted into `TABLE reviews` in column `rating`. The store data is coming from `TABLE reviews` and the table relationship is coming from a column in both tables named `store_id`. I trying to use a join statement to get the average to display next to each store. This is the current code in the loop for the rating with join statement: ``` "; echo "Store IDRating "; $result1 = mysql_query("SELECT s.store_id AVG(r.rating) AS avg_rating FROM stores as s JOIN reviews as r ON s.store_id = r.store_name GROUP BY s.store_id"); while ($row1 = mysql_fetch_assoc($result1)) { echo "$row1[store_id]$row1[avg_rating] "; } echo " ";?> echo " ";?> ``` This is the SQL error I…

Accepted Answer

Your SQL query is invalid. You need to either omit `s.store_name` from the query or include it in the GROUP BY

```
SELECT 
    s.store_id, 
    s.store_name, 
    AVG(r.rating) AS     
FROM stores as s
    JOIN reviews as r ON s.store_id = r.store_id
GROUP BY s.store_id, s.store_name
```

PHP/MySQL join statement

Solution

Problem

Related problems