How to retrieve 1 random row from MySQL

Question

I am making a random post generator and the code looks like this:

<?php

$idea_generator = array();

    $ideas_sql = "SELECT * FROM Ideas";
    $query = mysql_query($ideas_sql);



        while($row = mysql_fetch_assoc($query)) {
                $id = $row['id'];
                $name = $row['name'];
                $description = $row['description'];
                $keywords = $row['keywords'];
                //Need to add a link function




            array_push($idea_generator, $name);
                sort($idea_generator);
                $number = count($idea_generator);
                //randomly selects idea
                $winner = $idea_generator[rand(0, $number -1)];
                print strtolower($winner);

                }

?>

My problem is that this code returns 3 items out of the test database in different orders. This project is designed to take out only 1 item from this database. Should I stick with this php array method or is there an easier way to accomplish this goal.

You're not getting anything from `phpmyadmin`.... you're selecting data from a `MySQL` database... `phpmyadmin` is simply a __client tool__ for accessing a `MySQL` database, your script isn't using `phpmyadmin` at all, it's accessing a `MySQL` database through PHP's MySQL extension... this is an important distinction — Mark Baker, Jan 19 '14 at 00:48
Ok. I plan on using a database through phpmyadmin for this project. Is there anyway to retrieve only 1 item using something similar to this script. — user3167386, Jan 19 '14 at 00:49
@user3167386 there are several ways you can handle this, but it would be helpful to first review the manuals relating to the tools you are using, and to understand the distinction between an admin tool (**phpmyadmin**) and the actual database (**mysql**), and the tool you are using, which is **PHP**'s **MySQL Extension**. It would also be helpful to look up either, http://www.php.net/manual/en/book.pdo.php for PDO, or http://au2.php.net/mysqli for the current functions to use to access **mysql** data via **php** — , Jan 19 '14 at 00:58
Please be aware that [`mysql_*` functions are deprecated](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) and that you're missing error handling. — Jonathan, Jan 19 '14 at 01:00

score 3 · Accepted Answer · answered Jan 19 '14 at 00:54

3

Change your query to this to retrieve one row at random:

$ideas_sql = "SELECT * FROM Ideas ORDER BY RAND() LIMIT 1;";

As this query only returns one row, you no longer need to select one random row in your PHP code. Your final code could be like this:

$ideas_sql = "SELECT * FROM Ideas ORDER BY RAND() LIMIT 1;";
$query = mysql_query($ideas_sql);

$row = mysql_fetch_assoc($query);
// This is your 'winning' row

$id = $row['id'];
$name = $row['name'];
$description = $row['description'];
$keywords = $row['keywords'];

answered Jan 19 '14 at 00:54

Jonathan

6,572
1
30
46

This looks like it works better. I'm still confused as to what I should echo. I've tried the $ideas_sql, $query, and $row but none of them work. $row gives me an error about array to string conversion. $query gives me something about a recourse id. And $ideas_sql just gives me the line that selects the items from the database. – user3167386 Jan 19 '14 at 01:18
`$row` is an [array](http://www.php.net/manual/en/language.types.array.php) containing the data from your MySQL database. I think you should be able to `echo` `$row['name']` (or `$name`). – Jonathan Jan 19 '14 at 01:25
Perfect. Thank your so much. I have been stuck on this for so long and I'm so happy it's finally done. – user3167386 Jan 19 '14 at 01:27
@user3167386 And note Jonathan's previous comment - as well as advice on sql injection in general and the importance of prepared statements – Strawberry Jan 19 '14 at 01:29

How to retrieve 1 random row from MySQL

1 Answers1