Display Random Row From Table On Database

[CloudSex13]

Hi all,

 

I'm trying to develop a script that allows one random row to be selected from a table in a database called Outcomes and display information for only that one outcome, according to the chances of getting that outcome. I guess this script could also be similar to displaying a joke at random on a website, if the jokes were contained as rows in a database and had their own chance amount of when a user could get it. I started developing some code below, but I cannot seem to figure out why it will not work. Any suggestions would be greatly appreciated.

 

$randomOutcome = mysql_query("SELECT * FROM Outcomes WHERE LocationID='$currentLocation' ORDER BY OutcomeID");

$getRandomOutcome = mysql_fetch_array($randomOutcome);

$outcomeID = $getRandomOutcome['OutcomeID'];
$outcomeLocationID = $getRandomOutcome['LocationID'];
$outcomeTitle = $getRandomOutcome['OutcomeTitle'];
$outcomeImage = $getRandomOutcome['OutcomeImage'];
$outcome = $getRandomOutcome['Outcome'];
$outcomeChance = $getRandomOutcome['OutcomeChance'];

$amountOfOutcomes = mysql_num_rows($randomOutcome);

$pickOutcome = rand(1, $outcomeChance);

foreach ($pickOutcome as $theOutcome) {

echo "".$outcomeTitle." | ".$outcomeImage." | ".$outcome."";

}

[Ken2k7]

You can have SQL order it randomly and select one. So I added LIMIT 1.

 

$randomOutcome = mysql_query("SELECT * FROM Outcomes WHERE LocationID='$currentLocation' ORDER BY RAND() LIMIT 1");
$getRandomOutcome = mysql_fetch_array($randomOutcome) or trigger_error(mysql_error());
$outcomeTitle = $getRandomOutcome['OutcomeTitle'];
$outcomeImage = $getRandomOutcome['OutcomeImage'];
$outcome = $getRandomOutcome['Outcome'];
echo $outcomeTitle . ' | ' . $outcomeImage . ' | ' . $outcome;

[CloudSex13]

Ken2k7 to my rescue, of course.

 

That makes complete sense.

 

What about setting up a system of chances to get a result? I have OutcomeChance as a field in the table on the database as of now.

[Ken2k7]

What value does that hold and how does it work?

[CloudSex13]

Let's say I have two inserted rows in my table.

 

OutcomeID = 1 has OutcomeChance value of "10"

OutcomeID = 2 has OutcomeChance value of "50"

 

I figured that I'd have to use the rand(), so above without the RAND() in the SQL I had rand(1, $outcomeChance).

 

Am I on the right side of the tracks, here?

[CloudSex13]

Blah. Anyone?

 

[Andy-H]

$rand = rand(0,100);

$randomOutcome = mysql_query("SELECT * FROM Outcomes WHERE LocationID='$currentLocation' AND OutcomeChance > $rand ORDER BY RAND() LIMIT 1");

[CloudSex13]

Thanks Andy-H for the reply.

 

That certainly does make sense, but each row in the table on the database would have it's own unique chance of being pulled out. Should I not do this? What would be a wise alternative if so?

 

Thank you.

[Ken2k7]

What do 10 and 50 mean?

[Andy-H]

That depends on what the data is being used for, is it an advertising website?

 

If so it would be unfair on the clients to show some images more than others unless you have packages based on amount paid - times displayed.

So, what are you using the data for?

Also, in your original code you used rand (which returns an integer) and then used foreach (which accepts an array argument) on the returned integer.

[CloudSex13]

@Ken2k7

 

When you have a rand function, such as: rand(x, y) - "10" and "50" are y. (i.e. rand(1, 10) or rand(1, 50))

 

@Andy-H

 

No, it's not advertisement to answer your question. It's for jokes - I want some joke outcomes to be rarer, and some joke outcomes common-er. The data for OutcomeChance would be used for:

 

rand(1, $outcomeChance);

 

It's just that these $outcomeChance variables represent fields in a row in a table in a database for that outcome (i.e. joke), so how would you be able to select one at random and still keep the outcome chance of getting that random one intact?

 

Deathly confuses me.

[Andy-H]

YOu would have to have something to compare it to, for example as a percentage, 

 

if (rand(1, $outcomeChance) >= rand(1, 100))
{
   //Display data
}

 

I suppose that could be one way, although I think I am about as confused as you are, also what do you mean by 'Intact' and how many are you planning on displaying per request?

[CloudSex13]

Outcomes are stored in a table on the database as rows.

 

The fields for the structure of the table is:

 

OutcomeID
OutcomeLocationID
OutcomeTitle
OutcomeImage
Outcome
OutcomeChance

 

I'm looking for, when clicked on a link (i.e. outcome.php), a random outcome will display on the page. When the page is deciding what outcome to select, it will take into consideration all outcome chances (i.e. the OutcomeChance field in the table on the database which could have the value of any integer greater than 1 to represent "y" in what I know I'll need, the function: rand(X, Y)) and after an outcome is selected at random after taking into consideration all outcomes and their OutcomeChance, it will then display on the page.

 

By "intact", I ultimately mean what I just said above to answer your question, and I'm looking to display only one result per link click.

 

Does that make any further sense? Sorry about the bad explanations, but thanks man.

[Andy-H]

<?php

$randomOutcome    = mysql_query("SELECT OutcomeTitle, OutcomeImage, Outcome, OutcomeChance FROM Outcomes WHERE LocationID='" . $currentLocation . "' ORDER BY RAND()");

$amountOfOutcomes = mysql_num_rows($randomOutcome);
$process          = FALSE;

  if ($amountOfOutcomes == 0)
  {
   $outcome = 'Nothing to display';
   $process = TRUE;
  }

 while ( $getRandomOutcome = mysql_fetch_row($randomOutcome) && $process == FALSE )
 {
 	if ( rand(1, $getRandomOutcome[3]) > rand(1, $getRandomOutcome[3] ) )
 	{
 		$outcome = $getRandomOutcome[0] . ' | ' . $getRandomOutcome[1] . ' | ' . $getRandomOutcome[2] . '<br >
 		';
 		$process = TRUE;
 	}
 }


 	if ( !isSet($outcome) )
 	{
 		$outcome = 'Nothing to display';
 	}


echo $outcome;


?>

 

Will that do?

[CloudSex13]

Thanks for your help!

 

I tested that possible solution, and it's giving me the "Nothing to display" error from the following piece of code:

 

if ( !isSet($outcome) ) {

$outcome = 'Nothing to display';

}

 

Any ideas?

[CloudSex13]

I also noticed that OutcomeChance from the outcome row was not defined anyway. Could this be the problem?

[Andy-H]

//try this
echo $amountOfOutcomes . '<br >';

 

Sorry but I gotta go get some sleep, college in 6 hours. Hope you get it fixed.

 

 

Maybe, also try echoing $getRandomOutcome[3];

[CloudSex13]

Hmm...

 

Neither of those worked.

 

:/

[Ken2k7]

When you have a rand function, such as: rand(x, y) - "10" and "50" are y. (i.e. rand(1, 10) or rand(1, 50))

And what would rand(1,50) represent? That would give you a number between 1 and 50, but what does that number mean?

[CloudSex13]

Oh. Ah. I see. Wow I completely misread that function's definition.

 

How could one go about defining the chances of getting each outcome, then? Like, if outcome 2 is rarer than outcome 1, how would I tell PHP that outcome 2 is rarer and outcome 1 is more common, but still pick a random outcome keeping in consideration each outcome's common/rareness?

[Ken2k7]

Try this -

$currentLocation = intval($currentLocation);
$query = mysql_query('SELECT SUM(OutcomeChance) FROM Outcomes WHERE LocationID = ' . $currentLocation) or trigger_error(mysql_error());
if ($sum = mysql_fetch_row($query)) {
     $rand = rand(1, intval($sum));
     $query2 = mysql_query('SELECT * FROM Outcomes WHERE OutcomeChance >= ' . $rand . ' AND LocationID = ' . $currentLocation . ' ORDER BY RAND() LIMIT 1;') or trigger_error(mysql_error());
     if ($row = mysql_query($query2)) echo $row['OutcomeTitle'] . ' | ' . $row['OutcomeImage'] . ' | ' . $row['Outcome'];
     else echo 'No rows in table with Location id ' . $currentLocation;
} else echo 'No rows in table with Location id ' . $currentLocation;

[CloudSex13]

Thanks for the help, Ken2k7.

 

I ran the code through my site, but it's giving me the first

No rows in table with Location id 1

error, but the thing is, I'm 100% sure that there's rows in the Outcomes table where LocationID is equal to 1.

 

Strange?

[Andy-H]

<?php

$randomOutcome    = mysql_query("SELECT OutcomeTitle, OutcomeImage, Outcome, OutcomeChance, SUM(OutcomeChance) AS ChanceTotal FROM Outcomes WHERE LocationID='" . $currentLocation . "' ORDER BY RAND()")or trigger_error(mysql_error() . '<br >ON LINE: ' . __LINE__);

$amountOfOutcomes = mysql_num_rows($randomOutcome);
$process          = FALSE;

  if ($amountOfOutcomes == 0)
  {
   $outcome = 'Nothing to display';
   $process = TRUE;
  }

 while ( $getRandomOutcome = mysql_fetch_assoc($randomOutcome) && $process == FALSE )
 {

  extract($getRandomOutcome, EXTR_PREFIX_ALL, 'out');

 	$avg    = ($out_ChanceTotal / $amountOfOutcomes );
 	$random = rand(1, $out_OutcomeChance);

$n = rand(0,1);

if ( isSet($n) )
{
   $equation = $random < $avg;
}
else
{
   $equation = $random > $avg;
}

 	if ( $equation )
 	{
 		$outcome = $out_OutcomeTitle . ' | ' . $out_OutcomeImage . ' | ' . $out_Outcome . '<br >';
 		$process = TRUE;
 	}
 }


 	if ( !isSet($outcome) )
 	{
 		$outcome = 'Nothing to display';
 	}


echo $outcome;


?>

 

???

[CloudSex13]

I haven't gotten a chance to check this, yet - but thanks for your help, Andy-H.

[Ken2k7]

Oh doh! My mistake.

 

$currentLocation = intval($currentLocation);
$query = mysql_query('SELECT SUM(OutcomeChance) FROM Outcomes WHERE LocationID = ' . $currentLocation) or trigger_error(mysql_error());
if ($sum = mysql_fetch_row($query)) {
     $rand = rand(1, intval($sum[0]));
     $query2 = mysql_query('SELECT * FROM Outcomes WHERE OutcomeChance >= ' . $rand . ' AND LocationID = ' . $currentLocation . ' ORDER BY RAND() LIMIT 1;') or trigger_error(mysql_error());
     if ($row = mysql_fetch_assoc($query2)) echo $row['OutcomeTitle'] . ' | ' . $row['OutcomeImage'] . ' | ' . $row['Outcome'];
     else echo 'No rows in table with Location id ' . $currentLocation;
} else echo 'No rows in table with Location id ' . $currentLocation;