CODEWITHSUNDEEP
phpmysqlcodeigniter
Joseph Izang
Joseph Izang

Reputation: 755

Randomly Selecting Rows with MySQL

To randomly select records from one table; do I have to always set a temporary variable in PHP? I need some help with selecting random rows within a CodeIgniter model, and then display three different ones in a view every time my homepage is viewed. Does anyone have any thoughts on how to solve this issue? Thanks in advance!

Upvotes: 1

Views: 2132

Answers (5)

Jim
Jim

Reputation: 1624

I have this piece of code in production to get a random quote. Using MySQL's RAND function was super slow. Even with 100 quotes in the database, I was noticing a lag time on the website. With this, there was no lag at all.

$result = mysql_query('SELECT COUNT(*) FROM quotes');
$count = mysql_fetch_row($result);
$id = rand(1, $count[0]);
$result = mysql_query("SELECT author, quote FROM quotes WHERE id=$id");

Upvotes: 2

timdev
timdev

Reputation: 62914

If you don't have a ton of rows, you can simply:

SELECT * FROM myTable ORDER BY RAND() LIMIT 3;

If you have many rows, this will get slow, but for smaller data sets it will work fine.

As Steve Michel mentions in his answer, this method can get very ugly for large tables. His suggestion is a good place to jump off from. If you know the approximate maximum integer PK on the table, you can do something like generating a random number between one and your max PK value, then grab random rows one at a time like:

$q="SELECT * FROM table WHERE id >= {$myRandomValue}";
$row = $db->fetchOne($q); //or whatever CI's interface to grab a single is like

Of course, if you need 3 random rows, you'll have three queries here, but as they're entirely on the PK, they'll be fast(er than randomizing the whole table).

Upvotes: 5

Steve Michel
Steve Michel

Reputation: 91

Ordering a big table by rand() can be very expensive if the table is very large. MySQL will need to build a temporary table and sort it. If you have primary key and you know how many rows are in the table, use LIMIT x,1 to grab a random row, where x is the number of the row you want to get.

Upvotes: 2

Eineki
Eineki

Reputation: 14959

you need a query like this:

SELECT * 
FROM tablename
WHERE somefield='something'
ORDER BY RAND() LIMIT 3

It is taken from the second result of http://www.google.com/search?q=mysql+random and it should work ;)

Upvotes: 2

Phillip Knauss
Phillip Knauss

Reputation: 678

I would do something like:

SELECT * FROM table ORDER BY RAND() LIMIT 1;

This will put the data in a random order and then return only the first row from that random order.

Upvotes: 4

Related Questions