how to fetch a random item/text quote from a column of a table using php/mysql

Question

bellow you can see my table. In this table we have 300 quotes that I'm trying to fetch my at random and display it on the page but I have not succeeded. The column containing the texts has the name "fortune_text". here is my attempt code:

<?php
    $username = "fortunes";
    $password = "xxxx";
    $hostname = "localhost";

    //connection to the database
    $dbhandle = mysql_connect($hostname, $username, $password)
    // connect to database
    mysql_select_db("fortunes");
    // query the databse
    $query = mysql_query("SELECT 'fortune_text' FROM 'fortunes' ORDER BY RAND()");
    echo "$query";
    ?>

You want all 300 quotes to be outputted at random? Or one set of random quote? — Adib, Jan 17 '15 at 14:50
I want one quote to be displayed at random each time the page is refreshed — zig, Jan 17 '15 at 14:52
I see this question a lot. Please read [common database debugging for PHP and MySQL](http://jason.pureconcepts.net/2013/04/common-debugging-php-mysql/). — Jason McCreary, Jan 17 '15 at 14:53
What @Ghost provided (adding LIMIT 1) is your solution. Another potential silly solution is having PHP RAND instead of randomizing the database. — Adib, Jan 17 '15 at 14:53
*PSA:* The `mysql_*` functions are [deprecated in PHP 5.5](http://php.net/manual/en/faq.databases.php#faq.databases.mysql.deprecated). They are not recommended for writing new code as it will prevent you from upgrading in the future. Instead, use either [MySQLi](http://php.net/manual/en/book.mysqli.php) or [PDO](http://php.net/manual/en/book.pdo.php) and [be a better PHP Developer](http://jason.pureconcepts.net/2012/08/better-php-developer/). — Jason McCreary, Jan 17 '15 at 14:54

score 0 · Answer 1 · answered Jan 17 '15 at 15:01

0

try using shuffle() function of php to randomize array that comes from database and then echo the first index of that variable.

answered Jan 17 '15 at 15:01

Varun

76
5

that would require you to fetch all 300 quotes, while needing only one. Better to fetch a single quote from the DB. – Pevara Jan 17 '15 at 15:05

score 0 · Answer 2 · answered Jan 17 '15 at 15:12

0

Hi here is the modified version of your sample, which fetch text by rand order. I suggest you to use limit when you print result to page if you have later many rows, checking for error if any. Important line is echo mysql_result($result);

<?php
    $username = "fortunes";
    $password = "xxxx";
    $hostname = "localhost";

    //connection to the database
    $dbhandle = mysql_connect($hostname, $username, $password)
    // connect to database
    mysql_select_db("fortunes");
    // query the databse
    $result = mysql_query("SELECT 'fortune_text' FROM 'fortunes' ORDER BY RAND()");
    if (!$result) {
      die('Could not query:' . mysql_error());
    }
    echo mysql_result($result);
    mysql_close($dbhandle);
?>

answered Jan 17 '15 at 15:12

Elensar

124
1
9

thank you for your answer. this code does not work. It does not even connect to the database! – zig Jan 17 '15 at 15:23
So great for you! You have mysql connection error. Check your mysql server and credentials. First thing that must work is connection. – Elensar Jan 17 '15 at 15:29
yes not it turns I can't access I get this error: Connected successfullyCould not query:Access denied for user ''@'localhost' (using password: NO) – zig Jan 17 '15 at 15:42
@zig Ok, so check if you have user fortunes already in mysql server and also this user must have standard privileges(select data) to fortunes db. – Elensar Jan 17 '15 at 15:51
yes I have with the appropriate right : grant select on fortunes.fortunes to 'fortunes'@'localhost' identified by 'xxxx'; – zig Jan 17 '15 at 16:35

how to fetch a random item/text quote from a column of a table using php/mysql

2 Answers2