Lets just put it at its simplest, a table with two fields: 'item_id' & 'times_seen'.
| item_id | times_seen |
----------+-------------
| 1001 | 48 |
| 1002 | 25 |
| 1003 | 1 |
| 1004 | 12 |
| 1005 | 96 |
| 1006 | 35 |
I'm trying to find a way to randomly select a row, but give preference to items that haven't been selected much before.
(obviously, a second query would be sent to increment the 'times-seen' field after it has been selected)
Although my current "project" is a php/mysql one, I'd like language agnostic solutions if possible. I'd much rather have a math based solution that could be adapted elsewhere. I'm not opposed to a php solution though. I'd just like to be able to understand how the code works rather than just copy and paste it.
How about a SQL solution:
select * from item order by times_seen + Rand()*100 limit 1;
How much you multiply random with (Its a value between 0 and 1) depends on how much randomness you want..
Edit: http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_rand
times_seen
max - times_seen
Step 4 is the tricky part, but you could do it all like this:
$max = 1;
$rows = array();
$result = mysql_query("SELECT * FROM table");
while ($row = mysql_fetch_array($result)){
$max = max($max, $row['times_seen']);
$rows[] = $row;
}
$pick_list = array();
foreach ($rows as $row){
$count = $max - $row['times_seen'];
for ($i=0; $i<$count; $i++) $pick_list[] = $row['item_id'];
}
shuffle($pick_list);
$item_id = array_pop($item_id);
To do it all in SQL:
SELECT *
FROM table
ORDER BY RAND( ) * ( MAX( times_seen ) - times_seen ) DESC
LIMIT 1
This selects a single row with weightings inversely proportional to the times_seen