MYSQL Multi table select, average, group by, and order by

[KingOfHeart]

I need some help with this.
I'm using three different mysql tables.

1st - Accounts. This holds the username needed for this script
2nd - Downloads. Contains the filename, userid, download count, etc
3rd - Rating. Contains all the ratings for every file. Each Rating needs to be grouped by and to average the data together.

Then I need to be able to order all this data (user choice).
Any ideas on how to do this?
This is about my closest idea on how it might be done, but oviously no.

$od - Contains the string to order by. Example: $od = "filename"
$dir2 - Contains "DESC" or is left blank.
$filetype = 0 - 5. 0 will list all the files.

$sql = "SELECT * FROM Accounts, Rating, Downloads WHERE Accounts.id = Downloads.userid, SELECT Rating.fileid, AVG(Rating.rating) GROUP BY Rating.fileid";
if($filetype == 0)
$sql .= "ORDER BY $od $dir2";
else
$sql .= "AND Downloads.type = '$filetype' ORDER BY $od $dir2";
$result=mysql_query($sql);


So anyone got any clues?

[wige]

Since you don't state what you are actually trying to get out of the database, I am going to guess that you are trying to get the average rating for every file that the user has uploaded. To start with, lets try a much simpler query and make sure that works - getting the average ratings of all the files. (Just in case you have a massive database of files, we will add a limitation so only 100 will be returned.)

So, the specific information you need from the database is the name of each file, and the average rating of that file. Additional fields can be added to the query later. This query should work:

SELECT d.filename, AVG(r.rating) rating FROM Ratings r LEFT JOIN Downloads d ON d.fileid = r.fileid GROUP BY r.fileid ORDER BY rating DESC LIMIT 100;

You should get the 100 highest rated file names and the associated ratings, in descending order. If it does work, post back and let me know, and the next step will be limiting by user ID. If not, let me know what happens. And if I completely misunderstood what you are trying to do, clarify for me and I will try to come up with a solution.

[KingOfHeart]

I'll try your script shortly.
I'm trying to get the output from all three of those tables.
I need the output of the userids, the average rating for that file, and the username for who made the file.

Imagine a page where you see a list of files.

Filename, Username, Rating, Downloads, Last Updated.

The Filename, userid(not username) downloads, and last updated can all be retrieved with the Download MYSQL table.
The userid has to search the MYSQL table "Accounts" to get the proper username.
The Rating has to search the MYSQL table "Rating" to average out all the ratings for that one file.

Now when it's listing all this data you can click on the text "Filename","Username","Rating","Downloads","La st Updated" to choose the ordering. So to do this it needs to search through three mysql tables at once.

Sometimes forums have it set up like this when searching through the "Member list"

[wige]

Basically, the results set needs to come from the filename table then, because you want one response for each item in that table. Still, the most complex thing you need to get is the Rating of each file, but the core query must be done on the file table to allow the usernames to be retrieved more efficiently. Our starting point still needs to be ensuring that the query returns accurate ratings for each item. After you try the above query, try this one:

SELECT d.filename, AVG(r.rating) rating, d.fileid FROM Downloads d LEFT JOIN Ratings r ON d.fileid = r.fileid ORDER BY rating DESC LIMIT 100;

See if you get the same results. You can test the accuracy of this query by spot checking a few of the results with the following query:
SELECT AVG(rating) FROM Ratings WHERE fileid = whatever;
replacing whatever with the fileid from the previous query. This is something you would do manually, to make sure the original query is correct. The result of this query should match the rating in the rating column for the specified file from the previous query.

Once we are sure this query returns accurate data, we can easily incorporate retrieval of the user names.

[KingOfHeart]

Just getting errors.
Some more info for the actual mysql tables.

Table - Accounts
id
username
password
privilagee
mail
avatar
signature
rank
date
flags
lastlogin

Table - Downloads
id
name
userid
description
link
date
type
downloads
info
lastupdate

Table - Rating
id
rating
memidfileid

[KingOfHeart]

Hmm, don't see an edit button.

$sql = "SELECT Accounts.username, Accounts.id, Downloads.name, Downloads.userid, Downloads.lastupdate, Rating.fileid, Rating.rating FROM Accounts, Downloads, Rating WHERE Accounts.id = Downloads.userid";

This script gets the data from all three tables but can't group by or average out the ratings.
I had another thought.
Would it be good to just stick in a rating field in the Downloads, and updated this info when averaging the data.
So it would go

1. Average all the ratings by reading the Rating table.
2. Use that info and Update the Rating field in the Downloads table.
3. Now just read the Downloads table info for easy ORDER BY results.
Good idea or not?

[datetopia]

Sticking an additinal stats field or creating a table for this would be recommended for system speed.

You should really consider creating special tables for these stats (i.e. ratings) and have the stats updated for a user when another user votes. Developing like this would solve future server load issues.

Realtime calculations are ok if you have hundreds or a couple of thousands of users but this could kill the server when your sites becomes crowded with tens of thousands of users and hundreds of ratings or other data for each one.

[wige]

What is the error message you get when you run the queries?

[KingOfHeart]

Not worried about that script anymore. Now I'm working on a script to average all the ratings and then update that info in the Downloads table.

function GetRating() 
{
    $ratings = mysql_query("SELECT fileid, AVG(rating) FROM Rating GROUP BY fileid");
    $downloads = mysql_query("SELECT * FROM Downloads");
    $n = 1;
    while($row = mysql_fetch_array($downloads)) {
        $raterow = mysql_fetch_array($ratings);
        $rate = ($raterow['AVG(rating)'] == false ? -1 : $raterow['AVG(rating)']);
        echo "row " . $rate; 
        
        if($rate != -1)
        {
            mysql_query("UPDATE Downloads SET rating='{$rate}' WHERE id='{$raterow['fileid']}'");
        }
        else
        {
            mysql_query("UPDATE Downloads SET rating='0' WHERE id='$n'");
        }
        $n++;
    }
}

I got it to update the ratings (when that else is removed). However if nobody added any ratings for that file or there's no ratings at all it will not update the field in the Downloads table. I assuming I need an else, but how do I reset the info for the other fields that have no ratings. The $n method is not working. If I remove the "WHERE" part in the else it sets everything to 0.
So I hope this script is a lot easier to help me with.

[KingOfHeart]

Well I got that script above working right by resetting all the ratings in the Download table to 0, and then updating certain tables.