Fetching a random row from MySQL and displaying it with PHP

Answer

Solution:

First of all, I think you want

<?php

mysql_select_db(name of database);
$quotes = "SELECT author,quote FROM inspirational_quotes ORDER BY RAND() LIMIT 1";
$result = mysql_query($quotes);

WHILE ($row = mysql_fetch_array($result)):
ENDWHILE; 

echo "$result";
?>
Copy code

but I have an additional suggestion

Preload all the quote IDs

CREATE TABLE quoteID
(
    ndx int not null auto_increment,
    id int not null,
    PRIMARY KEY (ndx)
);
INSERT INTO quoteID (id) SELECT id FROM inspirational_quotes;
Copy code

Now choose based on the id from quoteID table

SELECT B.author,B.quote FROM quoteID A INNER JOIN inspirational_quotes B
USING (id) WHERE A.ndx = (SELECT CEILING(MAX(ndx) * RAND()) FROM quoteID);
Copy code

This should scale just fine because the return value for @rnd_id comes from a list of ids with no gaps in the quoteID table.

<?php

mysql_select_db(name of database);
$quotes = "SELECT B.author,B.quote FROM quoteID A INNER JOIN "
. "inspirational_quotes B USING (id) "
. "WHERE A.ndx = (SELECT CEILING(MAX(ndx) * RAND()) FROM quoteID)";

$result = mysql_query($quotes);

$row = mysql_fetch_array($result);

echo "$result";
?>
Copy code

Give it a Try !!!

Answer

Solution:

You cant echo $result as a string

do

    WHILE ($row = mysql_fetch_array($result)):
     echo $row['author'] . " " . $row['quote'];
ENDWHILE; 


?>
Copy code

Answer

Solution:

You are not echoing the right variable.

echo $row['author'] . ": " . $row['quote'];
Copy code

Answer

Solution:

WhyAND just comma.

SELECT author, quote FROM inspirational_quotes ORDER BY RAND() LIMIT 1
Copy code

MySQL Select syntax

Answer

Solution:

I suggest you to randomize results by PHP to improve performance. eg.:

$r = mysql_query("SELECT count(*) FROM inspirational_quotes");
$d = mysql_fetch_row($r);
$rand = mt_rand(0,$d[0] - 1); 
$r = mysql_query("SELECT author,quote FROM inspirational_quotes LIMIT $rand, 1");
Copy code