Randomingly choosing a quote from Database, PHP

[SquireQuack]

I just started PHP and I'm still trying to figure somethings out.

Could anyone provide a code for randomly choosing and displaying a quote from a database (two columns: quote, author)?

Thanks!

 

[user-7256]

I actually wrote one for my old website (www.vibewave.com) that retrieves a random quote...

Here:

//get a random quote... using arrays.

//Be sure to connect to the db.
$query1 = "SELECT * FROM quotes";
$result1 = mysql_query($query1);
while ($row1 = mysql_fetch_array($result1))
{
	$id1 = $row1['id'];
	$array1[count($array1) + 1] = $id1;
	
	shuffle($array1);
}
shuffle($array1);

//ACCESS THE RANDOM NUMBER IS BY $array1[0]
//Now we have the id of the random quote.

$random = $array1[0];

$q1 = "SELECT * FROM quotes WHERE id='$random' LIMIT 1";
$r1 = mysql_query($q1);

while ($row1 = mysql_fetch_array($r1))
{
	$content = $row1['content'];
	$author = stripslashes($row1['author']);
}

//Then just echo $content and $author (the person who said it) when you need it.
?>

It may not be efficient but it works

 

[Kate]

Easy:

select [field] from

order by rand()
limit 1

and you fetch one random record at a time

 

[user-7256]

Unfortunately, wouldn't rand() return any number, not just those in the db?

 

[luxinterior]

Unfortunately, wouldn't rand() return any number, not just those in the db?

No it wouldn't and yes that is the correct way to do it.

Lux

 

[Kate]

Order by Rand() just means "order the resultset in random order" (instead of ASC or DESC for example) so you won't get a record ID which is not part of the resultset.

 

[tgo]

Rand is not the best way but it works.

If its a small database then go for it, but if its more than 100 records dont use that way, the reason is ORDER BY RAND() forces Mysql to generate alot of random numbers.

Quote from below resource

What happens when you run such a query? Let’s say you run this query on a table with 10000 rows, than the SQL server generates 10000 random numbers, scans this numbers for the smallest one and gives you this row. Generating random numbers is relatively expensive operation, scaning them for the lowest one (if you have LIMIT 10, it will need to find 10 smallest numbers) is also not so fast (if quote is text it’s slower, if it’s something with fixed size it is faster, maybe because of need to create temporary table).

I code ASP and dont code PHP so I cant give you any code but I think you would be better off with this type of thing

SELECT COUNT(*) FROM DBNAME
get the total records here
Close recordset

calculate your random number between 1 and the total records.
I dont know PHP dont take this literaly
srand((double)microtime()*1000000);
$iRandom = rand(0,$totalrecords);

Now that you have a random number you can use the LIMIT in the Query properly.

SELECT [field] FROM

LIMIT $randomvariable, 1

The above limits the rows to one, and starts at the random number

Please dont cut paste my code here as i stated I code ASP and my PHP here is most likely wrong.

For more info on this
http://www.titov.net/2005/09/21/do-not-use-order-by-rand-or-how-to-get-random-rows-from-table/

 

[SquireQuack]

//get a random quote... using arrays.

//Be sure to connect to the db.
$query1 = "SELECT * FROM quotes";
$result1 = mysql_query($query1);
while ($row1 = mysql_fetch_array($result1))
{
	$id1 = $row1['id'];
	$array1[count($array1) + 1] = $id1;
	
	shuffle($array1);
}
shuffle($array1);

//ACCESS THE RANDOM NUMBER IS BY $array1[0]
//Now we have the id of the random quote.

$random = $array1[0];

$q1 = "SELECT * FROM quotes WHERE id='$random' LIMIT 1";
$r1 = mysql_query($q1);

while ($row1 = mysql_fetch_array($r1))
{
	$content = $row1['content'];
	$author = stripslashes($row1['author']);
}

//Then just echo $content and $author (the person who said it) when you need it.
?>

It may not be efficient but it works

it's returning this error aoubt how in

while ($row1 = mysql_fetch_array($r1))

$r1 is not a valid MySQL resource for mysql_fetch_array

I'm sure I did everything right (including connecting to the database). To prove it: when I replace

$q1 = "SELECT * FROM quotes WHERE id='$random' LIMIT 1";

with

$q1 = "SELECT * FROM quotes WHERE 1 LIMIT 1";

It returns with my first quote.

Just wondering...how do I fix the error? Thanks!

 

[user-7256]

Apparenlty the variable $random doesn't contain a value, or contains an invalid one. Tweak the script by putting in echo($random); then an exit; statement on the next line. Tell me what shows up.

 

[SquireQuack]

Thanks for you help compuXP!

the script I used:

$q1 = "SELECT * FROM quotes ORDER BY rand() LIMIT 1";
$r1 = mysql_query($q1);

$row1 = mysql_fetch_array($r1);
$content = $row1['quote'];
$author = stripslashes($row1['author']);


echo "<font size=\"-1\"><i>".$content."</i><br>";
echo "<div align=right><b>".$author."</b></div></font>";