CODEWITHSUNDEEP
phpmysqlrandom
Brooke.
Brooke.

Reputation: 3731

Randomly get multiple rows from a table

Right now I'm selecting 3 random rows from a table using ORDER BY Rand(); as pointed out all over the web this method is very slow. I am using it on a database with only 30 rows and it takes a long time to return the value. Most of the other solutions i've found only return one row. What's the best way to return multiple random rows?

$get_projects_query = 
  mysql_query(
    "SELECT p_id FROM project_data WHERE p_featured='1' ORDER BY Rand() LIMIT 3"
  ) or die(mysql_error());

while($project_row = mysql_fetch_array($get_projects_query)) {?>
  //Do stuff
} 
//end three random featured projects loop)

Upvotes: 0

Views: 417

Answers (4)

chris_so
chris_so

Reputation: 882

As everyone else said. Having only 30 items in your query do like this:

  1. do your query without RAND()
  2. build an array / object with all the items - E.g.: items[]
  3. generate 3 random numbers (x1, x2, x3) between 1 and the length of the array and then display them: items[1], items[2], items[3]

Or your query has only for the moment 30 results but in time it will be bigger and bigger?

Upvotes: 0

Paul Schreiber
Paul Schreiber

Reputation: 12589

If there are only 30 rows, you have a couple other options.

Option #1

  1. fetch all 30 rows.
  2. call shuffle()
  3. read the first 3 off the pile

Option #2

  1. Generate 3 random numbers from 0 to 29. Call them r1, r2, r3.
  2. SELECT ... WHERE p_id IN (r1, r2, r3)

Option #3

  1. SELECT p_id FROM ...
  2. while ($row = mysql_fetch_assoc($result)) $idList[] = $row["p_id"];
  3. shuffle($idList)
  4. SELECT ... WHERE p_id IN ($idList[0], $idList[1], $idList[2])

Upvotes: 1

drewrobb
drewrobb

Reputation: 1604

First select the table count. Order by an index column (probably the primary key). Then select limit 1 with offset = to a random integer in the range of 0 to the table count.

Upvotes: 1

cordialgerm
cordialgerm

Reputation: 8503

If you only have 30 rows why not download all 30 rows to your client and then generate 3 random row indexes

Upvotes: 0

Related Questions