SELECT random row from table with condition

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

SELECT random row from table with condition

Post 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
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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
Last edited by JayBird on Thu Apr 15, 2004 6:43 am, edited 1 time in total.
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

Post by magicrobotmonkey »

Ahh - good solution to the problem we talked about yesterday!
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

Post 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
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

Post 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'];
}


?>
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

Post 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...
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

Post 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...
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

Post 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.
Draco_03
Forum Regular
Posts: 577
Joined: Fri Aug 15, 2003 12:25 pm
Location: Montreal, Canada

Post 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 :)
Post Reply