Randomising Database Output

Collecting a random data set from your database can be useful for all kinds of data driven applications including analytics and “grey areas” in video game outcomes (battles, gambling, etc.).

Here I will discuss the use and methods of randomisation in PHP with data from a MySQL database.

Collecting the Data

First we need to grab our database rows.

For this we uses a simple SELECT statement, where in this example is taken from a “users” table with two fields “ID” and “name”.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
$db = MySQLi(DB_HOST, DB_USER, DB_PASS, DB_NAME);
 
$query = "SELECT `ID`, `name` FROM `users` ORDER BY `ID` ASC";
 
$results = $db->query($query);
 
$rows = array();
 
while($r = $results->fetch_assoc())
{
  $rows[] = $r;
}
 
echo '<ol>';
 
foreach($rows as $row)
{
  echo '<li>'.$row['ID'].' = '.$row['name'].'</li>';
}
 
echo '</ol>';

This will print out all of the data from our database into an ordered list, arranged by ID from 1 upwards.

Randomise with PHP

To then take this data and shuffle its values, we can then take the conveniently named shuffle method in PHP and apply it to our $rows array.

Add this code to the above script at line 13. It takes the data and again prints it out into an ordered list, this time with a randomised data set.

13
shuffle($rows);

Using the MySQL Query

Now, since here we are already using a MySQL query, we can utilise the RAND() method by adding it to our $query string.

This should be used as an alternative to the PHP shuffle method, so as such should not include the above line with shuffle in it (unless you want to randomise it twice – but it won’t make your data any more unique).

Replace your query string with this, on line 3.

3
$query = "SELECT `ID`, `name` FROM `users` ORDER BY RAND()";

Notice it’s only the last part (ORDER BY...) that changes.

This code – or at least the idea – can be applied to, and is useful for, all programming languages. The syntax will vary between them but fundamentally the results will be the same.

Making a Facebook app? Why not award the user with a random item when they log in once a day, or show a rearranged list of their friends if it’s otherwise too large to show on a page.

Own a blog? Apply this to your homepage and show some old posts. It will also get their page rankings higher if they are being re-indexed by search engines. It’s like fresh content!

It will also help your visitors find posts they never knew they were looking for. ‘Cos we’re all just browsing after all 😉