Ok, so I have a list of values, and I want it so the user can select any 7 different options, and it'll randomly select x number of values from the table with any of those options.
The stucture is like
ImgID (unique key) | SiteID | Other tables
Try 1: The first method I tried was read the database for any selected values, and count the amount for each selected option. The amount of occurrences were stored in one variable (just call it num), and the total amount was calculated too. It then chose a random number between 1 and the max amount, ran through the num variable with the individual amounts, incrementing it until it went past the value, revealing which option value the random value landed on. This could randomly choose the option correctly, but it'd still require another random selection from the database, which leads into the next try.
Try 2: The random value from the database seemed reasonable, but I thought why not just do it all at once and skip a lot of queries. I can't seem to get it working properly though, I was using this site - http://akinas.com/pages/en/blog/mysql_random_row/ and chose the bottom mehod to use. There's quite a few error messages from this - 'unable to save result set', 'execution was interrupted', 'lost connection to mysql server' and 'not a valid result resource'
The code is
for($i=0;$i<count($sites)-1;$i++){
$siteinfo = explode("@",$sites[$i]);
$siteid = $siteinfo[0];
$sitegroup = $siteinfo[1];
if($i!=0){
$sqlextra .= " OR ";
}
$sqlextra .= "SiteID='".$siteid."'";
}
$sql="SELECT * FROM ImageList WHERE ImgID >= (SELECT FLOOR( MAX(ImgID) * RAND()) FROM
ImageList) AND Valid='1' AND (".$sqlextra.") ORDER BY ImgID LIMIT 1";
if(mysql_query($sql, $mysql_connect)){}
else{echo mysql_error();}
And the sql output with a few of the option selected is
SELECT * FROM ImageList WHERE ImgID >= (SELECT FLOOR( MAX(ImgID) * RAND()) FROM ImageList) AND Valid='1' AND (SiteID='6' OR SiteID='7') ORDER BY ImgID LIMIT 1
I'm not very good at mysql so not sure if I've gone wrong somewhere, but if anyone has a better way of doing this or just knows why mine isn't working, it'd be appreciated, cheers
