Join tables Count and sort by rating?

join count math

[AndieR]

Hello to all,

I'm not really a newbie but I've never came accross such a concept and I am a little lost I have to say.

I have two tables:

Schools
- id
- name
- address1
- phone
- active

Ratings
- id
- idschool
- rating
- user

And I need to display all of the schools ordering them by their rating average?

I know this will involve join etc but each time I take a step I can't seem to model the whole process in my mind, I would greatly appreciate any help! :)

Thanks
Andie

[syber]

This should give you what you need:

SELECT name, AVG(rating) AS avg_rating 
 FROM Schools JOIN Ratings 
 ON Schools.id = Ratings.idschool 
GROUP BY name 
ORDER BY avg_rating 

[AndieR]

Thanks! That's great :)

This is exactly what I was looking for... just another question though, right now if one of the schools has no reviews the query doesn't select/display it...

Is there any way to make it so that schools that have no reviews will still show up as if they had a zero rating score?

Thanks again!
Andie

[AndieR]

Hmmm a LEFT JOIN did the trick :) Thanks a million for helping me out!

-Andie

[AndieR]

Hmm I have a little problem though... with th following code, I can't seem to retrieve the id for each one of the listed schools...? How should I go about it?

$query="SELECT name, idcategory, address1, address2, city, state, zip, phone, maps, AVG(rating) AS avg_rating FROM schools LEFT JOIN reviews ON schools.id = reviews.idschool WHERE schools.idcategory = '$category' GROUP BY schools.id ORDER BY avg_rating DESC";

$result=mysql_query($query) or die ("Query failed");

$row = mysql_fetch_array($result);

$idschool = $row["id"];

Thanks :)
Andie

[LifeinAsia]

Add schools.id to the SELECT part?

[AndieR]

Thanks,

I tried it and when I call my variable pulled from the results returned by the query it is empty...

I tried both $idschool = $row["schools.id"]; and $idschool = $row["id"]; with the later what I get is the reviews id...

Any ideas?

Thanks
Andie

[LifeinAsia]

Try it as an alias:
SELECT schools.id AS SchoolsID

[AndieR]

I eventually solved this with the following:

$query="SELECT name, idcategory, address1, address2, city, state, zip, phone, maps, AVG(rating) AS avg_rating, schools.id AS sch FROM schools LEFT JOIN reviews ON schools.id = reviews.idschool WHERE schools.idcategory = '$category' GROUP BY sch ORDER BY zip";

$result=mysql_query($query) or die ("Query failed");

$row = mysql_fetch_array($result);

$idschool = $row["sch"];

Honestly I am at a loss why using schools.id didn't work and am curious to know if anyone could cast a light there. But I am glad to have this solved anyway.

Thanks again,
Andie

[LifeinAsia]

ID is a reserved word in some DBs. And since you have fields called "ID" in both tables, it created ambiguity. That's why I suggested aliasing schools.id, which you used. :)

I normally append "ID" to the table name as a field name when I use ID fields (e.g., "SchoolsID" and "RatingsID" or "Schools_ID" and "Ratings_ID" in your case).

[AndieR]

Yes, I really see your point and will apply that concept to my new databases. I also think it will be easier to handle through the code for me!

Thanks again!
Andie