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 What is the best way to pick a random row from a table in MySQL?, Selecting Random Rows in MySQL, Alternatives to ORDER BY RAND() — outis
– outis, Commented Jan 18, 2012 at 7:03

Marc B · Accepted Answer · 2012-01-17 14:44:38Z

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, 2012 at 14:44

answered Jan 17, 2012 at 14:37

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ypercubeᵀᴹ Over a year ago

Shouldn't you add LIMIT 10 in the query?

DaveRandom · Accepted Answer · 2012-01-17 14:40:27Z

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, 2012 at 14:40

DaveRandom

88.8k11 gold badges159 silver badges174 bronze badges

1 Comment

outis Over a year ago

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.

Collectives™ on Stack Overflow

MySQL. Random rows. Convert rows into PHP arrays.

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related