I'm working on a script that allows sorting based off a form, where people can choose to orderby 4 different values in the database:

visits
rating
name
date

the one I'm trying to do is the rating, yet the rating is made up of 2 fields in the database, rate, and rates

Rate being the total accumilation of all the values when someone rates it, and Rates being the total number of ratings...for example:

Rate: 40
Rates: 10
The history log would be something like this (the rating is out of 5)

Rate 1: 4
Rate 2: 5
Rate 3: 3
Rate 4: 5
Rate 5: 2
Rate 6: 5
Rate 7: 5
Rate 8: 2
Rate 9: 4
Rate 10: 4

so you can see...it to get a rating out of 5...I just divide rate by rates.

Now for the problem: How do I order it in a MySQL query (with PHP) so the value it's ordering by is the result of rate/rates?

    You can very simply just put in your expressions in your query.
    for example:

    $query = "SELECT (rate/rates) as score, etc.... from table order by score asc limit 30";

      SELECT SUM(rating)/COUNT(id) AS rated FROM tbl GROUP BY name ORDER BY name;

      Then refer to the field rated

      COUNT is the number of rows per name
      SUM is the total of the rating field per name

        Write a Reply...