SELECT random row from table with condition
Moderator: General Moderators
SELECT random row from table with condition
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
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
This seems to do the trick
Seems the best way to me!?
Mark
Code: Select all
SELECT *
FROM images
WHERE rating = 'HI'
ORDER BY rand()
LIMIT 1Mark
...and this is my function
Mark
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
}
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
-
magicrobotmonkey
- Forum Regular
- Posts: 888
- Joined: Sun Mar 21, 2004 1:09 pm
- Location: Cambridge, MA
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
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
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",
);Mark
-
magicrobotmonkey
- Forum Regular
- Posts: 888
- Joined: Sun Mar 21, 2004 1:09 pm
- Location: Cambridge, MA
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'];
}
?>-
magicrobotmonkey
- Forum Regular
- Posts: 888
- Joined: Sun Mar 21, 2004 1:09 pm
- Location: Cambridge, MA
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...
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
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
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
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",
);Mark
-
magicrobotmonkey
- Forum Regular
- Posts: 888
- Joined: Sun Mar 21, 2004 1:09 pm
- Location: Cambridge, MA
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.
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
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
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'
}dunno if you follow me..
nyways