Ranking average values from a MySQL table in PHP (Dreamweaver code)

[_AJD_TUS_]

Hi all,

I have a table with various entries in it, including a whole load of columns in which people rate certain criteria, on a 1-10 basis.

As a brief example, I might have a portion of the table that looks like this:

Establishment	Setting_Rating	Product_Quality	Customer_Service_Rating	Speed_Rating
Diner 1	5	8	9	7
Joes Cafe	8	5	6	8
Moes Tavern	3	6	7	5
Big Ben's	3	5	4	8
Mamas House	9	8	10	3
Fast Foodies	7	4	2	9

What I want to do is put some analysis on my website. The three main functions I want to present are:

'The area with the highest average rating is "Customer_Service_Rating"'

'The Highest rated establishments (based on average ratings) are:

"1: Mamas house"'

"2: Big Bens"

"3: Diner 1"

'The average rating for Fast Foodies is "5"'

...etc, etc, etc.

Taking these examples as my starting point - how in the world do I achieve this?!

[bregent]

I would consider changing your main table structure to something like:

RATINGS:

ratings_id

establishment

criteria_id

rating

and have another table

CRITERIA:

criteria_id

description

This will make it much easier to do the type of analysis you are after, and make it easy to add more criteria.

[_AJD_TUS_]

Ah... what about if the idea of analysis was quite a new one to me, and that the table in question already had quite a lot of data that I'd be reluctant to move about too much? Is there any way of creating any number of secondary tables to perform the analysis on the original table?

If so, what syntax would be used to find things like "Top 3", "Average of...", etc, etc, etc...

[bregent · Accepted solution]

It's difficult to do statistical analysis across multiple columns. You'd have to achieve this using a combination of SQL and procedure programming.

But, you could try creating a VIEW which would consist of a UNION that would convert your columns into rows. Something like:

SELECT Establishment, Setting_Rating, "Setting Rating" from MyTable

UNION

SELECT Establishment, Product_Quality, "Product Quality" from MyTable

UNION

SELECT Establishment, Customer_Service_Rating, "Customer Service Rating" from MyTable

UNION

SELECT Establishment, Speed_Rating, "Speed Rating" from MyTable.

Then you can do your analysis on the VIEW using standard SQL aggregate operators (MAX, AVG, etc) and the Group By clause.

>and that the table in question already had quite a lot of data

It would be a simple matter to move the data from its current format into the format I suggested. However, this would require reworking the front end so that will take a bit of time for you. Try creating the VIEW I suggested.

[_AJD_TUS_]

bregant, once again you supply a perfect solution. Many thanks indeed.

[bregent]

You're welcome. Glad it worked for you.