Randomingly choosing a quote from Database, PHP

SquireQuack · 10-28-2005, 02:12 PM

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!

mholt · 10-28-2005, 02:47 PM

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

Here:

PHP Code:

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

????: NamePros.com http://www.namepros.com/programming/135721-randomingly-choosing-a-quote-database-php.html
 
//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'];

????: NamePros.com http://www.namepros.com/showthread.php?t=135721
    $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

sdsinc · 10-28-2005, 06:56 PM

Easy:

select [field] from [table]
order by rand()
limit 1

and you fetch one random record at a time

mholt · 10-28-2005, 09:20 PM

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

luxinterior · 10-28-2005, 09:31 PM

Originally Posted by compuXP

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

sdsinc · 10-29-2005, 08:15 AM

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 · 10-30-2005, 09:31 AM

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

Quote:

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
????: NamePros.com http://www.namepros.com/showthread.php?t=135721

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 [table] 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-n...ws-from-table/

SquireQuack · 10-30-2005, 07:56 PM

Quote:

PHP Code:

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

????: NamePros.com http://www.namepros.com/showthread.php?t=135721
 
//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);

????: NamePros.com http://www.namepros.com/showthread.php?t=135721
 
//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

PHP Code:

  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

PHP Code:

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

with

PHP Code:

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

It returns with my first quote.

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

mholt · 10-30-2005, 08:26 PM

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 · 10-31-2005, 06:50 AM

Thanks for you help compuXP!

the script I used:

PHP Code:

  $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>";

10-28-2005, 02:12 PM	THREAD STARTER #1 (permalink)
SquireQuack NamePros Member Join Date: Aug 2005 Posts: 125	Randomingly choosing a quote from Database, PHP 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! __________________ uploadÂ² \|AIMForum.com - Ready, AIM, Chat!\|Google Talk Forum - Where the world talks about Google! Forum Elves - Quality Forum Posting At Affordable Prices! -\|Order A Package\|PaidPosts.com Help Boost Your Forum\|BannerManage.com Sell direct advertisements on your website effortlessly!

10-28-2005, 02:47 PM	#2 (permalink)
mholt DNOA Member Join Date: May 2004 Posts: 5,040	I actually wrote one for my old website (www.vibewave.com) that retrieves a random quote... Here: PHP Code: //get a random quote... using arrays. ????: NamePros.com http://www.namepros.com/programming/135721-randomingly-choosing-a-quote-database-php.html //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']; ????: NamePros.com http://www.namepros.com/showthread.php?t=135721 $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

10-28-2005, 06:56 PM	#3 (permalink)
sdsinc Domains my Dominion Join Date: Aug 2005 Location: Web 1.0 Posts: 9,552	Easy: select [field] from [table] order by rand() limit 1 and you fetch one random record at a time __________________ NameNewsletter.com - free lists of available domain names ZoneFiles.net (beta) - ccTLD and gTLD droplists

10-29-2005, 08:15 AM	#6 (permalink)
sdsinc Domains my Dominion Join Date: Aug 2005 Location: Web 1.0 Posts: 9,552	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. __________________ NameNewsletter.com - free lists of available domain names ZoneFiles.net (beta) - ccTLD and gTLD droplists

10-31-2005, 06:50 AM	THREAD STARTER #10 (permalink)
SquireQuack NamePros Member Join Date: Aug 2005 Posts: 125	Thanks for you help compuXP! the script I used: PHP Code: `$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>";` __________________ uploadÂ² \|AIMForum.com - Ready, AIM, Chat!\|Google Talk Forum - Where the world talks about Google! Forum Elves - Quality Forum Posting At Affordable Prices! -\|Order A Package\|PaidPosts.com Help Boost Your Forum\|BannerManage.com Sell direct advertisements on your website effortlessly!

10-28-2005, 09:20 PM	#4 (permalink)
mholt DNOA Member Join Date: May 2004 Posts: 5,040	Unfortunately, wouldn't rand() return any number, not just those in the db?

10-30-2005, 08:26 PM	#9 (permalink)
mholt DNOA Member Join Date: May 2004 Posts: 5,040	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.

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)