Fetching a random row from MySQL and displaying it with PHP

Question

im trying to select the row quote and author from my table and echo it

my goal is to create a random quote generator and display the actual quote and author.

I have entered 25 quotes in my table with 3 rows (ID, quote, author)

my code is the following and i keep getting the resource id #9 error

<?php

mysql_select_db(name of database);

$quotes = "SELECT author AND quote FROM inspirational_quotes ORDER BY RAND() LIMIT 1";

$result = mysql_query($quotes);

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

echo "$result";
?>

please help

what about "select author,quote instead of author AND quote ? — mallix
– mallix, Commented Jan 30, 2013 at 21:50
Two things. 1) ORDER BY RAND() really doesn't scale well, it's fine when it's a few hundred records, but when it's a few thousand you'll start feeling some pain from it. When it's a few million, you're finished. Check out the book SQL Antipatterns for alternatives. 2) the mysql_* functions in PHP are deprecated in all but name. You should switch to something more modern such as PDO or Mysqli — GordonM
– GordonM, Commented Jan 30, 2013 at 21:50
Does the same random quote appear for each visitor that day? — Cups
– Cups, Commented Jan 30, 2013 at 22:03
Rand() should be fine since i only have 25 quotes! thank you for all your help — Bez Soltani
– Bez Soltani, Commented Jan 30, 2013 at 22:27

RolandoMySQLDBA · Accepted Answer · 2013-01-30 22:16:01Z

7

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

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;

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);

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

Give it a Try !!!

edited Jan 30, 2013 at 22:16

answered Jan 30, 2013 at 21:50

RolandoMySQLDBA

44.4k16 gold badges96 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

RolandoMySQLDBA Over a year ago

I refined the query and the ID collection.

Green Black · Accepted Answer · 2013-01-30 21:50:26Z

1

You cant echo $result as a string

do

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


?>

answered Jan 30, 2013 at 21:50

Green Black

5,0571 gold badge19 silver badges29 bronze badges

Comments

Tchoupi · Accepted Answer · 2013-01-30 21:50:28Z

1

You are not echoing the right variable.

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

answered Jan 30, 2013 at 21:50

Tchoupi

14.7k5 gold badges39 silver badges71 bronze badges

Comments

Hamlet Hakobyan · Accepted Answer · 2013-01-30 21:50:49Z

1

Why AND just comma.

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

MySQL Select syntax

answered Jan 30, 2013 at 21:50

Hamlet Hakobyan

33.5k6 gold badges55 silver badges70 bronze badges

Comments

vincenzodb · Accepted Answer · 2013-01-30 21:53:53Z

1

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

answered Jan 30, 2013 at 21:53

vincenzodb

1711 silver badge11 bronze badges

Collectives™ on Stack Overflow

Fetching a random row from MySQL and displaying it with PHP

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related