How would you write this?

[FiRe]

Say I have a table with:

ID ¦ Points
1 ¦ 5
2 ¦ 20
3 ¦ 100
4 ¦ 2
5 ¦ 37
6 ¦ 90
7 ¦ 1
8 ¦ 23
9 ¦ 80
10 ¦ 15

What I need to do is figure out either a query or an algorithm that displays 3 random id's but should be weighted in favour of how many points they have.

So 3 random id's are returned, but they would more likely to be if they have a larger number of points, e.g. id's 3,6,9 will be seen more often than 7,1,4

Does this make sense, and does anyone have any ideas? Take into the fact that there will be an unlimited amount of id's. Here is the beginning of the query:

SELECT * FROM table ORDER BY RAND() LIMIT 3

Obviously this wont show id's 3,6,9 any more often than the other ids. Maybe you need to put all of the results into an array and then do some math?

[darrenG]

My first thought is to generate a random threshold, which is not exclusively predisposed to being high, but is more likely to be high. Then select random records from the table which are greater than your threshold.

The threshold could be something like ((2 ^ rand) < max(score)) - max(score). Im not mathematician, so what I mean by that is, rand must be between 0 and no greater than the highest power of 2 that is less than max(score). Because powers increase exponentially it is more likely to be a low number because the gap between each power of two is greater each step, so we reverse the number by substracting from max(score), which will give us a greater chance of hitting a high threshold because there will be more of them.

for example:

max(score) = 1000

Powers of 2 which are less than max(score):

1, 2, 4, 8, 16, 32, 64, 128, 256, 512

At the moment they are more biased to low numbers ie 9/10 are less than max(score) / 2. so we reverse them:

1000 - 1 = 999
1000 - 2 = 998
1000 - 4 = 996
1000 - 8 = 992
1000 - 16 = 984
1000 - 32 = 968
1000 - 64 = 936
1000 - 128 = 872
1000 - 256 = 746
1000 - 512 = 488

(apologies if my maths is out lol).

Now we have those values, we can construct our SQL:

select * from table where score > 999 or score > 984 or score > 488 order by rand() limit 3

Not perfect, but its a start...

[darrenG]

Just thought, that won't work because MySQL will check each row against each condition in the WHERE clause, rather than checking for matches against each condition before moving onto the next condition.

[FiRe]

It doesn't have to be all in one query though?
I am not math wizz myself but here is just some experimenting with what I read above:

$max_score = 100;
$rand = rand(0, 64);
$threshold = ((2 ^ $rand) < $max_score) - $max_score;
echo $threshold;

this will always give a $threshold of -99

[PHP_Chimp]

Although I think that this is a slow way to do what you want this is about as random as I can think of.

$limit = 3; // number of results you want returned.
$max = 100; // assumption that your Points are a %
$min = 0; // assumption that you are not going into -Points.
$numbers = array(); // the array of random numbers
for ($i=0;$i<$limit;$i++) {
$rand = mt_rand(1,4);
switch ($rand) {
case 1:
$rand_min = $max/4; // top 75%
break;
case 2:
$rand_min = $max/3; // top 66%
break;
case 3:
$rand_min = $max/2; // top 50%
break;
case 4:
$rand_min = $min;
break;
}
$numbers[] = mt_rand($rand_min, $max);
}
foreach ($numbers as $number) {
$q = "SELECT ID FROM table WHERE ($number - Points)>=0 ORDER BY Points ASC LIMIT 1;"; // should give you the closest match to the $number.
$res = mysql_query($q);
// do what you want with each number.
}

So this should give you -
25% chance of getting a number that is the whole range of Points.
75% chance of getting a result within the top 75%.
50% chance of top 66%.
25% chance of top 50%.

Seeing as I have assumed that you know what the min and max points are you may need to change that part to get the min and max points from the table.

[darrenG]

That is because you have taken what I said literally, because I wrote it badly. But note I was writing english, not code!

Threshold = any power of 2, which is less than max(score)

Is what I meant. See the example for clarification.

What you have written, is TRUE - 100, which, as true is 1, will be -99...

Although you might want to do what phpchimp has provided for you.

[FiRe]

Hi Chimp,

$max = 100; // assumption that your Points are a %

The points are just going to be numbers that get incremented, how would this work?

[PHP_Chimp]

The only thing that you would need to know is the maximum number of points from that table. So you could get that by something like -

$q = 'SELECT Points FROM table ORDER BY Points ASC Limit 1;';
$result = mysql_query($q);

$max is only there so that the rand function has some limits, otherwise the actual upper limit for mt_rand is a very large number ;)

<edit>

$numbers[] = mt_rand($rand_min, $max);

This is the part that actually generates your random Points. So the $max just needs to be the largest number of Points in the table. The $rand_min is the minimum number generated by the random switch statement.
You could of course do away with the switch statement, as the final SELECT statement in my code will get you the closest match to the random number generated. However assuming that the max for mt_rand may well be 2147483647 you would stand a very high chance of always getting the top scorer.
If your points dont have a limit then you may want to look at the mt_getrandmax [uk.php.net] function to see what the upper limit for mt_rand is, then cap your score at that amount, so that you dont get into a situation where you cant return your top scorers as there score is higher than can be produced by mt_rand.

[edited by: PHP_Chimp at 10:36 pm (utc) on Feb. 18, 2008]