Custom Select Queries With PHP?

[ShoeLace1291]

I have a five star rating system and I want to be able to pull the highest rated items with one query.  My table has columns called "ratePoints" and "rateTimes".  Every time a user makes a rating, the "rateTimes" column of the specific row is updated by 1 integer.  The "ratePoints" column is then updated by whatever the user rates the item.  So if an item is rated 100 times and has a total of 500 rating points, then the item has a 5 star rating(ratePoints / rateTimes).  Is there a way to incorporate a function into a mysql selection query that only selects results that return true for a function like this:

 

if(($ratePoints / $rateTimes) > 2.5) return TRUE;

[nobodyk]

it's best if you do the math in php. Only do math in mysql if you don't expect your table to grow much.

 

You can try doing a select and then storing the rows. After you store the rows you can begging sorting the information with a while or for loop.

 

mysql_query("Select FROM mytable Where sum(ratePoints / rateTimes) > 2.5 ORDER BY DESC");

 

Alternatively, you can try adding a new column called Overall (or w/e).

 

 

[fenway]

No reason you can't do this math in mysql -- but don't necessarily store it -- and you'll need to use the result of aggregate functions in a HAVING.

[ShoeLace1291]

It actually seems like it might make more sense to do this with ORDER.  will it work if i do select * from table ORDER BY (ratePoints/rateTimes)

[kickstart]

Hi

 

You could use order by

 

SELECT item, (ratePoints/rateTimes) AS rating
FROM itemTable]
ORDER BY rating DESC

 

Using having

 

SELECT item, (ratePoints/rateTimes) AS rating
FROM itemTable]
ORDER BY rating DESC
HAVING rating > 2.5

 

However I would expect such a system to have a separate table listing the items and each rating (so you can control who has voted).

 

All the best

 

Keith

[ShoeLace1291]

hmm very true, keith.  you guys have been a big help.

[ShoeLace1291]

Since using SELECT item will only return that item from the table, how would I use that while selecting all items such as SELECT *

[kickstart]

Hi

 

No aggregate functions so you should just be able to add the field names to the SELECT.

 

You could use SELECT item.*, (ratePoints/rateTimes) AS rating, but I would generally recommend against using SELECT *.

 

All the best

 

Keith