-3

I am trying to write a php / mysql script that will pull 5 random records from the DB and display them. At the moment I am using the following to pull one record out and display it in a module, but im not sure how to pull out 5 at a time only. I will have approx 200-300 records in the DB in total.

I am currently using the following code to pull the single record out at random :

<?php
$result = mysql_query("SELECT * FROM `zgjzb_chronoforms_data_submitbusiness` ORDER BY     RAND() LIMIT 0,4;");
$row = mysql_fetch_array($result);
?>
Iain Simpson
  • 8,011
  • 13
  • 47
  • 66
  • 7
    `LIMIT 5`. don't use mysql_*, don't use `ORDER BY RAND` on a large table –  Nov 12 '12 at 21:20
  • Use a while loop, this is clearly shown in the many examples available in the [PHP manual](http://php.net/mysql_fetch_array) – Jocelyn Nov 12 '12 at 21:20
  • 1
    duplicate of: http://stackoverflow.com/questions/9945691/doing-a-while-loop-to-get-10-random-results – tereško Nov 12 '12 at 21:26
  • @tereško - It's a duplicate, but note that two of the three answers (including the accepted answer) are terrible. The only good answer to that question is [this one](http://stackoverflow.com/a/9946238/535871), but it requires a stored procedure. – Ted Hopp Nov 12 '12 at 21:50
  • @TedHopp , yes, it requires a stored procedure, because fast selection of `N` **unique** entries is a not-completely-trivial problem in SQL. – tereško Nov 12 '12 at 22:20

1 Answers1

9

You don't want to use ORDER BY RAND(). MySQL has to build a temporary table. If your table has a unique id column, something like this is much better:

SELECT * FROM `zgjzb_chronoforms_data_submitbusiness` 
WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM `table` )
ORDER BY id LIMIT 1;

See this blog post for other approaches that work well in php.

Ted Hopp
  • 232,168
  • 48
  • 399
  • 521
  • 4
    Finally a decent answer. Thanks for that – PeeHaa Nov 12 '12 at 21:23
  • Thanks, will this pull out 5 records ?, or do I just change LIMIT to 5 ? – Iain Simpson Nov 12 '12 at 21:24
  • 2
    @IainSimpson - It will pull out one record. You don't want to use `LIMIT 5` because it will pull out 5 _consecutive_ records, which aren't really random. It's cheap enough, though, that you can just do it multiple times until you get 5 unique records. – Ted Hopp Nov 12 '12 at 21:27
  • i don't think 200-300 is so large that order by rand() is going to hurt performance wise. But i'm not going to benchmark it to check :-) –  Nov 12 '12 at 21:30
  • 1
    @Dagon - I'm not going to benchmark it either. :) – Ted Hopp Nov 12 '12 at 21:33
  • 1
    dam you! my evil plan to cajole you in to it failed! :-) –  Nov 12 '12 at 21:33
  • @IainSimpson change the `LIMIT 5` .. You need to try and test it yourself for something. Then you know understand better and why.. – Rafee Nov 12 '12 at 21:36