Random affiliates in a mysql database with a table field for their importance.
how would i go about doing something random, and giving priority to more importants affiliates?


Already know how to properly generate a random number

PHP Code:

rand()&7; 


But yeay the part after that is a bit fuzzy
Problem is that i don't know enough about mysql and php in order to judge how to do this effectivly.
There's not really any way to store this in a cache and since it will be loaded loads of times sort of need to do this effective

You need some way of defining a numeric range for each affiliate, say give the first one 1 to 10, the next one gets 11 to 20, then the third one gets 21 to 40 cos he paid more. Then you can find the biggest number out of all the ranges, and generate a random number up to that value. Then you can grab the MySQL record that got picked.

So you'd need two columns, one called say 'lower' and the other called 'upper'. When you add an affiliate, you give them a certain number range by adding on to the last one. The top value of all the ranges can be found with MySQL too:

PHP Code:


//To add an affiliate

function addAffiliate($rangewidth,$name,$othervariables) {
  $result = mysql_query("SELECT upper FROM affiliates ORDER BY upper DESC LIMIT 1");
  $current_max = mysql_result($result,0,'upper');
  $newlower = $current_max + 1;
  $newupper = $current_max + $rangewidth;
  mysql_query("INSERT INTO affiliates VALUES ( '$name' , $newlower, $newupper, $othervariables)");
} 


Then to pick a random affiliate, you generate your random number. What you have above will generate one in the range 0 to 7 since you are using bitwise AND. I would write rand(1,n) to get a number from 1 to n:

PHP Code:

//To pick an affiliate

function pickAffiliate() {
  $result = mysql_query("SELECT upper FROM affiliates ORDER BY upper DESC LIMIT 1");
  $current_max = mysql_result($result,0,'upper');
  $random = rand(1,$current_max);
  $result = mysql_query("SELECT * FROM affiliates WHERE lower < $random ORDER BY lower DESC LIMIT 1");
  $affiliate_array = mysql_fetch_array($result);
  return $affiliate_array;
} 


You then need to watch out if you change the range for one affiliate (like they upgrade their rating or something), you need to make their upper value higher, but make all the values above that bigger too so they don't overlap:

PHP Code:


function setRange($affiliateId,$newrange) {
  $result = mysql_query("SELECT * FROM affiliates WHERE id = $affiliateId");
  $row = mysql_fetch_array($result);
  $difference = $newrange-($row['upper']-$row['lower']);
  mysql_query("UPDATE affiliates SET upper = upper + $difference, lower = lower + difference WHERE upper > ".$row['upper']);
  mysql_query("UPDATE affiliates SET upper = upper + $difference WHERE id = $affiliateId");
} 


That ought to do it - note this code has not been tested and may need some adjustment. Post back if you have trouble.

that's some really good help.
In fact, this is probably the most thorough help i ever got.
Thanks!

the pickanaffiliate requires 2 queries.
Anybody any ideas on how to do it in one?

for getting 5 random affiliates what would be the best way?


Is there a more effective way for doing that instead of using a range?

advice please