MYSQL & Random

[Anon]

Im rather new to PHP and MYSQL and I need the code to take text randomley from a MYSQL DB.

Its going to be used for a Random Quotes section which displays a random quote taken from the database each time the page is loaded.

Can anyone help please =]

[Anon]

First you need to know hou many entries there are in the table (which I'll call 'quotes').

$query="SELECT COUNT(*) FROM quotes";
$result=mysql_query($query);
$total=mysql_result($result,0,0);

Now, let's make sure we have our random number generator warmed up:

srand((double)microtime()*1000000);

And generate a random number in the range 0..$total-1:

$random=rand(0,$total-1);

Now use use that in a SELECT statement's LIMIT clause:

$query="SELECT thisquote FROM quotes LIMIT $random,1";

And fetch the resulting item from the datbase the same way as before:

$result=mysql_query($query);
$randomquote=mysql_result($result,0,0);

This can be done much more sophisticatedly (perhaps using MySQL's RANDOM function in some way), but this is a good enough start.

[fitzbean]

The extremely simplified and prefered way of doing this is as follows:

query="SELECT thisquote FROM quotes ORDER BY rand() LIMIT 1";

That's it! Let the power of mySQL shine through 🙂

[[deleted]]

"The extremely simplified and prefered way of doing this is as follows"

Not really.

This method is mysql-only and is definately not the fastest method as it requires that you re-order the entire table while you only need one record. This can be quote heavy on large tables.

The preferred method is grumbler's method, or a (more compatible) variation on that;
select all PK values from the table, pick a random one in PHP, and fetch the corresponding record.

---

A forum, a FAQ, email notification, what else do you need?

Time to YAPF!: http://www.yapf.net/

[Anon]

Yes; selecting a random PK is in fact my preferred choice - especially since it can be extended to select multiple random entries with a single query (just LIMITing won't). But I had no guarantee that there was a primary key to select on.

[fitzbean]

well, this is a php board, and mysql is the vastly prefered choice if I'm not mistaken, and for this guy's purposes (a quote table), I hardly think speed would play a huge role. It is alot simplier though either way 😉