OK guys this was a real pain in the ass, but here is the solution (works pretty smoothly, I tested it ;)) The way I use it is a more complicated, I adjusted the code for posting to this thread.
First, I added two INT columns to the table -
from_no and
to_no
Code:
mysql_query("ALTER TABLE table_name ADD COLUMN (from_no INT, to_no INT)") or die(mysql_error());
Then, I created a file that I will set up a cronjob for like every 20 minutes. This file will fill the "from_no" and "to_no" columns with number ranges. The first range starts with 0, no range overlaps, no gaps between the ranges. The larger the range, the better chance for the row to be selected.
Code:
$from_no = '0';
$result = mysql_query("SELECT id,probability_index FROM table_name") or die(mysql_error());
while ($row = mysql_fetch_array($result)) {
mysql_query("UPDATE table_name SET from_no='$from_no', to_no='" . ($from_no + $row[probability_index]) . "' WHERE id=$row[id]") or die(mysql_error());
$from_no += $row[probability_index] + 1;
}
And finally, this code will generate random number within the range of 0 and the largest number in the to_no column and selects the right row
Code:
$row = mysql_fetch_array(mysql_query("SELECT MAX(to_no) * RAND() AS rnd FROM table_name"));
$rnd = $row[rnd];
$row = mysql_fetch_array(mysql_query("SELECT id FROM table_name WHERE from_no<=$rnd AND to_no>=$rnd LIMIT 1")) or die(mysql_error());
Looks pretty simple now, but I worked 2 hours on it. Hopefully don't I find out there's a function for it in MySQL ;)