I have a question that someone here may or may not be able to answer (I
think perhaps MySQL is incapable of a solution). I have a program which
uses a MySQL database to help create a playlist for a ShoutCast stream.
There is a web site associated with the web-radio. Users of the web site
can rate songs which are contained in the database. The rating system
works such that users can rate songs from +3 to -2. Now, what I would
like to accomplish is to create a query that allows me to randomly
select a song from the database to add to the queue while taking into
account the ratings. For example:
There are 2400 songs listed
One song has been rated three times as follows:
I would like that song to have a 3/2400 chance of being selected for the
Secondly, I need the database to store who voted for waht so that users
cannot continually vote +3 over and over for their favorite song.
I know how I can accomplish this in Postgres using VIEWs, but I have
not yet been able to find a good solution in MySQL. Can anyone recommend
a viable option? My current database structure is irrelevant, as I will
change the structure if I can find a good solution.
Thanks In Advance,