MySQL. Random rows. Convert rows into PHP arrays.

Question

I have a MySQL table with four columns, submited_name, submited_URL, submited_type, uniqueID. In this table i have about 1000 records. I need to select 10 random entries. The problem is that I need to split selected random values into separate PHP arrays. One for each column. My arrays should look something like this:

$ten_random_names = array ('name26', 'name55', 'name107', ect.);
$ten_random_URL = array ('url for name26', 'url for name55', 'url for name107', ect..);
$ten_random_types = array ('type for name26', 'type for name55', 'type for name107', ect..);

possible duplicate of [MySQL Select by random](http://stackoverflow.com/questions/3788271/mysql-select-by-random) — GordonM, Jan 17 '12 at 14:46
possible duplicate of [What is the best way to pick a random row from a table in MySQL?](http://stackoverflow.com/questions/142242/), [Selecting Random Rows in MySQL](http://stackoverflow.com/questions/1283640/), [Alternatives to ORDER BY RAND()](http://stackoverflow.com/questions/1823306/) — outis, Jan 18 '12 at 07:03

Marc B · Answer 1 · 2012-01-17T14:44:38.157

5

The basics:

$sql = "SELECT name, url, type FROM ... WHERE ... ORDER BY random() LIMIT 10"; // inefficient, but works
$results = mysql_query($sql) or die(mysql_error());

$names = $url = $types = array();
while($row = mysql_fetch_assoc($results)) {
   $names[] = $row['name'];
   $url[] = $row['url'];
   $type[] = $row['type'];
}

edited Jan 17 '12 at 14:44

answered Jan 17 '12 at 14:37

Marc B

348,685
41
398
480

Shouldn't you add `LIMIT 10` in the query? – ypercubeᵀᴹ Jan 17 '12 at 14:41

score 2 · Accepted Answer · answered Jan 17 '12 at 14:40

2

$query = "SELECT *
          FROM tablename
          ORDER BY RAND()
          LIMIT 10";
$result = mysql_query($query);

$ten_random_names = $ten_random_URL = $ten_random_types = array();
while ($row = mysql_fetch_assoc($result)) {
  $ten_random_names[] = $row['submited_name'];
  $ten_random_URL[] = $row['submited_URL'];
  $ten_random_types[] = $row['submited_type'];
}

answered Jan 17 '12 at 14:40

DaveRandom

86,228
11
149
173

The mysql extension is outdated and on its way to deprecation. New code should use mysqli or PDO, both of which have important advantages, such as support for prepared statements. – outis Jan 18 '12 at 07:04

MySQL. Random rows. Convert rows into PHP arrays.

2 Answers2