So my site’s main page display’s applications for a PMP. Users can rate these apps with ‘Like’, ‘Dislike’, or ‘Neutral/None’ (every app is rated Neutral/None by default.)
On the main page I wanted to list the games by the highest difference (So it would take the total Like’s and Dislike’s then add them together.) I’m having difficulty making an SQL statement to do this correctly.
My tables structures are like this:
appinfo: id; name; genre; ...
appinfo basically holds all the metadata for the app. Nothing about the rating.
Next, I have the
apprating: username; app_id; rating;
This is where all the ratings are stored. Ratings are stored as a -1 (Dislike), 0 (Neutral/None), and 1 (Like). I was hoping with this structure it would be easier to setup the whole statement. Can anyone help me out with writing one? I ended up writing a ridiculously long one that doesn’t sort properly or display the rating number.
SELECT appinfo.title, appinfo.description, appinfo.username, appinfo.genre FROM appinfo LEFT JOIN appratings ON appinfo.id=appratings.app_id GROUP BY appinfo.id ORDER BY SUM(appratings.rating) DESC
Thanks SO MUCH for reading this through, just an SQL statement and correction on my evil SQL ways would be nice!
So before there was a rating system, I would have my SELECT statement, then in PHP I’d
mysql_fetch_array, then loop through and echo their details, effectively letting me do things such as:
... <p class="description"><?php echo $gameinfo'description'; ?></p> ...
Which would grab the description of the game it was on and echo it. Now, description still works, but since I now have an odd system that involves using SUM with my Like’s/Dislike’s and a JOIN statement for appinfo.id and apprating.app_id, I’m now experiencing issues with
- Echoing the ID of the current game, (echoing
$gameinfo'id'doesn’t echo anything.)
- How can I get the SUM of the Like’s/Dislike’s and echo that?
The SQL statement allowed me to write
echo $gameinfo'totalscore' to get the Like/Dislike differences and I just had to add
appinfo.id to the SELECT to be able to echo the ID.
Your GROUP BY clause needs to include all of the columns from the SELECT list that aren’t being aggregated in some manner. You may happen to know that the description, title, and other details won’t change for a particular app, but the database engine doesn’t, so it needs
to understand whether each selected column is fixed (as part of the GROUP BY) or aggregated (SUM, COUNT, MIN, MAX, etc.)
SELECT appinfo.title, appinfo.description, appinfo.username, appinfo.genre, SUM(appratings.rating) AS totalscore FROM appinfo LEFT JOIN appratings ON appinfo.id=appratings.app_id GROUP BY appinfo.title, appinfo.description, appinfo.username, appinfo.genre ORDER BY totalscore DESC ;
See if you get the right results by changing the columns in the GROUP BY clause.
SELECT appinfo.title, appinfo.description, appinfo.username, appinfo.genre FROM appinfo LEFT JOIN appratings ON appinfo.id=appratings.app_id GROUP BY appinfo.title, appinfo.description, appinfo.username, appinfo.genre ORDER BY SUM(appratings.rating) DESC