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:

Post by JayBird »

okay, this is what i have finally come up with

Don't hesistate to suggest any improvements

Code: Select all

/**
 * Display a random image from the portfolio
 * Images can be weighted so certain image have more chance of being displayed
 * WON'T show same image twice in a row
**/
function getRandomImage() {

	// Create query to get all distinct weightings
	$sql = "SELECT DISTINCT(weighting)
			FROM ocda_images_gd";

	// Execute the query
	$result = mysql_query($sql) or die(mysql_error());

	// Store weightings in $weightings array
	while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
		$weightings[] = $line['weighting'];
	}

	// 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" 
	);

	// Choose random weighting until a weighting that exists is found
	while (!in_array($priority[$pri], $weightings)) {

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

	}

	// Execute the query
	$result = mysql_query($sql) or die(mysql_error());

	// Create the query
	$sql = "SELECT *
			FROM ocda_images_gd
			WHERE weighting = '".$priority[$pri]."'
			AND id != '".$_SESSION['last_image_gd']."'
			ORDER BY rand() 
			LIMIT 1";

	// Execute the query
	$result = mysql_query($sql) or die(mysql_error());

	// Return the line we are interested in
	$line = mysql_fetch_array($result, MYSQL_ASSOC);

	// Store the id of the image we just displayed
	// so we don't display it starigh after
	$_SESSION['last_image_gd'] = $line['id'];

	// Display the image
	echo '<img src="'.$line['path'].'">';

}
Mark
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

Post by magicrobotmonkey »

Yea, I think that's the best way to do it... I don't think its mathematically "correct" but who cares, it works, right?
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

yeah, it seems to work okay, the images that are weighted as HI definatley display a lot more often than the LO ones!

RESULT! :)

Mark
Post Reply