weighted random select?

[TEENFRONT]

Hey all,

 

Just looking for some quick help, iv googled around but nothings really coming up that i can appl into a query.

 

I have an advert banner system and want to select a random banner id but have some more likely to display than others.

 

mysql table has the columns (aswell as others), tag_id and tag_weight  ---  an example of the records would be

 

id weight

1 0.25

2 0.25

3 0.50

4 0.40

 

So i want tag_id 1 to show up 25% of the time or 25% more likely to show up in a rand() query.

 

my query so far, without the weighted addition is

 

$result = mysql_query("SELECT * FROM tags WHERE ad_size='$ad_size' AND active='1'  ORDER BY rand() LIMIT 1");

 

My heads spinning when i think about how to work it out lol. How would i add in a weighted rand function to my query?

 

Also would the weights all have to add up to 100?

 

If i served 100,000 impressions and if i had  tag 1 set to 0.50 tag 2 set to 0.40 and tag 3 set to 0.30 .. which tags would show up how many times? If i set the weights added up to 100, im guessing that would each display like a percentage, so if tag 1 was weighted at 0.25 it would show roughly 25,000 impressions from the 100,000 correct? But what would happen if the weights dont add up to 100?

 

im getting myself lost.

 

 

[jskywalker]

* pick a random number between 0 and 100

* if its below 25 than pick id=1

* if its between 25 and 50 pick id=2

* if its between 50 and 100 pick id=3

* etc...

 

if the total of your 'weights' is above 100 than some ads wont show

if total of 'weights' is below 100 than sometimes no add will show...

 

[teamatomic]

Why stop at 100? One point per percent, random pick from the total points.

 

 

HTH

Teamatomic

[TEENFRONT]

I found this on my travels, but how can i test it works how i want it to? like how can i test what it will select each time and check it is actuall choosing ads like 25% of the time etc. This is what my query now looks like, it returns banners, but i dont know if its weighting them or not?

 

$result = mysql_query("SELECT * FROM tags WHERE ad_size='$ad_size' AND status='1'  ORDER BY Rand()*(1/weight) LIMIT 1");

 

the additon being

 

ORDER BY Rand()*(1/weight) LIMIT 1

 

[jskywalker]

count them when they are selected.

and review the count after a lot of hits

(enough hits to check the wieghting)