Select random records from a MySQL table

This tutorial shows you how to select records randomly from a MySQL database.

Sometimes it could be very helpful to select a record from a table randomly.
You can find such solution when you see random partners or randomly selected tutorials.

To realise such effect is quite easy. In the following steps you can learn everything.

Step 1.
First of all let's create a test table. In my example I use the following table script:

CREATE TABLE `demo` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(100) default NULL,
`city` varchar(100) default NULL,
`web` varchar(100) default NULL,
`added` datetime default NULL,
PRIMARY KEY (`id`)
)

Step 2.
Fill the table with some dummy data. You can use the following SQL statements:

insert into `demo`(`id`,`name`,`city`,`web`,`added`) values (1,'Mike','New York','www.mike.com',NULL);
insert into `demo`(`id`,`name`,`city`,`web`,`added`) values (2,'John','Dallas','www.john.com',NULL);
insert into `demo`(`id`,`name`,`city`,`web`,`added`) values (3,'Anna','London','www.anna.com',NULL);
insert into `demo`(`id`,`name`,`city`,`web`,`added`) values (4,'David','Oxford','www.david.com',NULL);
insert into `demo`(`id`,`name`,`city`,`web`,`added`) values (5,'Julia','New York','www.julia.com',NULL);

Step 3.
Now it is the time to make some PHP coding. First of all just create a small script which connects to the MySQL database then selects and displays the records as they were stored in the table. I code I use is the following:

<?php

// Create the connection and select the DB
$link = mysql_connect("localhost","root","kovacs1");

if ($link) {
   mysql_selectdb("test",$link);

   // Select records from the DB
   $query  = "SELECT * FROM demo";
   $result = mysql_query($query);

   // Display records from the table
   echo "<table border='1'>";
   while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
      echo "<tr><td>$row[0]</td><td>$row[1]</td><td>$row[2]</td></tr>";
   }
   echo "</table>";
} else {
    echo "Can't connect to the database!";
}

?> Step 4.
The above presented code just displays all records from the table. Now let's make the trick to select only 2 records randomly from the table.
To do this we just have to extend the select statement with the following sql code:

ORDER BY Rand() LIMIT 2

As result the complete query will look like as this:

$query = "SELECT * FROM demo ORDER BY Rand() LIMIT 2";


Step 5.
Now we are ready. The complete code to select 2 records randomly from our demo table is the following:

<?php

// Create the connection and select the DB
$link = mysql_connect("localhost","root","kovacs1");

if ($link) {
   mysql_selectdb("test",$link);

   // Select records from the DB
   $query  = "SELECT * FROM demo ORDER BY Rand() LIMIT 2";
   $result = mysql_query($query);

   // Display records from the table
   echo "<table border='1'>";
   while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
      echo "<tr><td>$row[0]</td><td>$row[1]</td><td>$row[2]</td></tr>";
   }
   echo "</table>";
} else {
    echo "Can't connect to the database!";
}

?>