Page 1 of 2

SELECT random row from table with condition

Posted: Thu Apr 15, 2004 6:02 am
by JayBird
I have a table in a MySQL database that has reference to lots of images.

These image are weighted "HI", "MED", "LOW".

Images that are rated "HI" are more likely to be randomly picked than images rate "LOW".

Now, i have produced the PHP code to decide wether to chose a "HI", "MED", "LOW" image, i am just having a problem with the query.

Say the PHP code decided to chose a "HI" image, and there are 5 images rated "HI" in the database, how do i know randomly chose one of the 5 images with a query?

Thanks

Mark

Posted: Thu Apr 15, 2004 6:22 am
by JayBird
This seems to do the trick

Code: Select all

SELECT *
FROM images
WHERE rating = 'HI' 
ORDER BY rand()
LIMIT 1
Seems the best way to me!?

Mark

Posted: Thu Apr 15, 2004 6:24 am
by JayBird
...and this is my function

Code: Select all

function getRandomImage() {
	// Array of priorities
	// 1/2 chance of selecting an image rated as HI
	// 1/3 chance of selecting an image rated as LOW
	// 1/6 chance of selecting an image rated as HI
	$priority=array( 
	    "HI", 
	    "HI", 
	    "HI", 
	    "MED", 
	    "MED", 
	    "LO" 
	); 

	// Generate a random number with the maximum number
	// being the total number of elements in the priority array
	$pri=rand(0,sizeof($priority)-1); 

	// Create the query
	$sql = "SELECT *
			FROM ocda_gd_imgs
			WHERE rating = '".$priority[$pri]."'
			ORDER BY rand() 
			LIMIT 1";

// Execute the query and return what we need

}
Mark

Posted: Thu Apr 15, 2004 6:28 am
by magicrobotmonkey
Ahh - good solution to the problem we talked about yesterday!

Posted: Thu Apr 15, 2004 9:34 am
by JayBird
Okay, come across a little problem.

Sometime, in the DB, there may not be any images rated as LOW for example.

So, if the PHP chooses LOW, the query will not return anything cos there are no images with this rating.

How can i get round that?

Thanks

Mark

Posted: Thu Apr 15, 2004 9:40 am
by magicrobotmonkey
ahh a pitfall! you could error check and if it returns none, bump it up - that is, if there's no low, return a med

or..

you could build your array from the database

SELECT rating
FROM ocda_gd_imgs

I would suggest the second

Posted: Thu Apr 15, 2004 9:46 am
by JayBird
Yeah, because if it was low, and i bumped it up one, that would mean there was an equla chance of it being MED or HI (i think).

How would i go about populating the Array?

problem is.

If there were only images rated HI and MED, the array would need to be like this

Code: Select all

$priority=array(
       "HI",  
       "HI", 
       "HI", 
       "HI", 
       "MED", 
       "MED",  
   );
Depending on what image rating had been set, the array would need to be different to keep the chance of a HI being picked as 1/2 chance, MED image 1/3 chance and LOW image 1/6 chance

Mark

Posted: Thu Apr 15, 2004 10:00 am
by magicrobotmonkey

Code: Select all

<?php
//run this query
  $sql = "SELECT rating
         FROM ocda_gd_imgs"; 

//get dbase stuff here...

$priority = array()
while($row = mysql_fetch_assoc($result)){
    $priority[]=$row['rating'];
}


?>

Posted: Thu Apr 15, 2004 10:05 am
by JayBird
...but think about it.

If i had 20 images in my DB and 19 of them were rated HI and 1 rated MED, there would be 19/20 chance of choosing a HI image and only a 1/20 chance of choosing a MED image.

I need the chances to stay the same as in the function i showed above.

Mark

Posted: Thu Apr 15, 2004 10:08 am
by magicrobotmonkey
Oh right i see what you mean - good point. So you always want the same chances that a certain level will be picked but you can't gurantee that there will be at least one of each level. It seems like the answer is right on the tip of my tongue...

let me think about it while i go on with this mindless accounts management work...

Posted: Thu Apr 15, 2004 10:12 am
by magicrobotmonkey
Well I guess the question then has to come down to what you want to happen when there's none of a level? Pick 50/50 between the other two? I guess it doesn't really matter...

Posted: Thu Apr 15, 2004 10:14 am
by JayBird
yeah, thats what i would like, but to be honest i don't think it is possible to keep the chance the same.

Take this for example, in the DB, there are only images rated HI and LOW.

The chances of a LOW image being chosen should be 1/6. This means i need an array that looks like

Code: Select all

$priority=array( 
       "HI",  
       "HI", 
       "HI", 
       "HI", 
       "HI", 
       "LOW",  
   );
Even though there is a 1/6 chance that LOW will be chosen, the is now a 5/6 chance of a HI image being chosen, which is a higher chance than 1/2 (3/6)!?!?

Mark

Posted: Thu Apr 15, 2004 10:16 am
by JayBird
Suppose an non-satisfactory solution would be to make sure there is always at least one of each rating, but i don't want to do that really cos it mean this will have beaten me, and i hate being beaten :)

Mark

Posted: Thu Apr 15, 2004 10:18 am
by magicrobotmonkey
What you need here is a mathmatician! I think you have to set up your ratios like 3:2:1, then stick with that ratio if you miss one like 3:1, 2:1, or 3:2 . but i don't know if that's mathematically right or not. I have no idea how to make a nice clean little code to do that anyways without involving a bunch of different arrays.

Posted: Thu Apr 15, 2004 10:50 am
by Draco_03
mhh just to point that it s impossible to keep your ration when there is no image of one type (med hi or low).. it s not possible..

i ll say something that s prolly not brilliant ..but here it is anyways
why not keep randomizing. ? if you have no MED and you get a med in from your array

Code: Select all

array {
'HI'
'HI'
'HI'
'MED'
'MED'
'LOW'
}
well why not just keep randomizing until you get ewhat you have.. if you have no MED you can t posibli have one.. but if YOU HAD ONE the chance of seeing it would still be 1/3
dunno if you follow me..
nyways :)