Displaying random mysql records - PHP

Question

Im trying to display a random record from my table but with no luck this is what I have upto. This is coded in PHP

$range_result = mysql_query("SELECT MAX() AS max_id , MIN() AS min_id FROM pages");
$range_row = mysql_fetch_object( $range_result ); 
$random = mt_rand($range_row->min_id , $range_row->max_id);
$result_random = mysql_query( "SELECT * FROM pages WHERE id >= $random LIMIT 0,1");
echo $result_random;

Wondering if this is an obvious mistake? I have connected to the database fine.

JB

You could try adding "order by RAND()" however this is know to demand a fair bit more processing by the server. — Carl Owens, Dec 07 '12 at 00:41
http://stackoverflow.com/questions/1244555/how-can-i-optimize-mysqls-order-by-rand-function — Frank Farmer, Dec 07 '12 at 00:41
Check for sql errors using mysql_query's return value and mysql_error() — Frank Farmer, Dec 07 '12 at 00:42

Steven Moseley · Accepted Answer · 2012-12-07T01:19:38.400

5

You should do this with straight SQL with a mysqli_multi_query for performance:

// First query gets the random ID, second gets the associated row
$sql = "SELECT @id:=FLOOR(RAND()*(MAX(id) - MIN(id))) + MIN(id) FROM pages; 
        SELECT * FROM pages WHERE id = @id;"

// Create a mysqli object and execute the multi-query
$mysqli = new mysqli($host, $user, $pass, $db_name);
$mysqli->multi_query($sql);
$result = $mysqli->store_result();

// Grab the first row from the first resultset
if ($row = $result->fetch_assoc()) {
    print_r($row);
}

edited Dec 07 '12 at 01:19

answered Dec 07 '12 at 00:40

Steven Moseley

15,871
4
39
50

1

I think the downvote came from the initial post that had WHERE ORDER BY in a mysql_ function. Not sure though – Kai Qing Dec 07 '12 at 00:48
Pretty sure WHERE ORDER BY would cause an error, and mysql_ always gets attacked on this site. Just saying... wasn't me though. – Kai Qing Dec 07 '12 at 00:50
Ah, missed that... was a little sloppy, indeed. But my edited answer is good... AND it's tested this time. ;) – Steven Moseley Dec 07 '12 at 00:51
@TheSmose aww, here, you earned it. – sooper Dec 07 '12 at 00:53
Perfect thanks @TheSmose. A quick last question, how would I echo out the title instead of the ID of that record *field is called page_title* :) – JB UCL Dec 10 '12 at 01:21
@JBUCL - just change `print_r($row);` to `echo $row['page_title'];` – Steven Moseley Dec 10 '12 at 06:03
I receive - defined index: page_title? :( – JB UCL Dec 11 '12 at 01:50
@JBUCL that makes no sense - is `page_title` an integer? – Steven Moseley Dec 14 '12 at 13:45

Kai Qing · Answer 2 · 2012-12-07T00:55:52.400

Why don't you just use rand():

"SELECT * FROM pages ORDER BY RAND() Limit 1"

But to answer, what is the MIN and MAX selecting if not defined? Should you perhaps try:

"SELECT MAX(`id`) AS max_id , MIN(`id`) AS min_id FROM pages"

And I don't think you need the offset in the limit. So just LIMIT 1 would probably be fine.

As the comments state, RAND() might be slow for larger data sets, but in many cases this is not a problem and the lag is minimal unless you have an exceptional amount of data. Read frank's link to see details. Personally, though, I've never really had a problem on RAND() speed.

And as Michael said, you should probably try to fetch the results before echoing what you believe to be the result set.

And also, mysql_ functions are deprecated. You should switch to mysqli or PDO

+1 because `ORDER BY RAND()` is a simple working solution for many applications where you don't have huge tables. — Steven Moseley, Dec 07 '12 at 01:24

score 0 · Answer 3 · answered Dec 07 '12 at 00:44

While you would be much better off ordering by RAND(), your method would work. Your problem is that you're trying to echo out a mysql result set. You need to do something like this after your code:

$row = mysql_fetch_assoc($result_random);
echo $row['field1'] . " " . $row['field2'];

Where field1 and field2 are names of columns in your table.

Displaying random mysql records - PHP

3 Answers3