How to Return Random Rows From a MySQL Table Using PHP


In this article, we show how to return random rows from a MySQL table using PHP.

This can be useful for many purposes, such as if you want to check a random user or a random record.

To do this in MySQL, you use the built-in rand() function.

So the above is the MySQL code to return the rows of the table in random order.

We'll now show how to tie this in with PHP.

PHP Code

The PHP code to return the rows of a table in any random order is shown below.

So the following code gets the data required to make a connection to a database and actually selects a database.

We then create a variable named $result that selects the table, table_name, and orders it in random order using the built-in MySQL rand() function.

You then can later decide what you want to do with these selected rows.

I created a MySQL table called PopularToys. This is shown below.

MySQL PopularToys table

Applying the PHP code above and displaying the rows in tabular form, the following result is achieved.

Actual PHP Output

BMX Bike 80.00
Fisher Price Jeep Wranger 250.00
Stuffed teddy bear 10.00
Remote-controlled helcopter 150.00
Remote-controlled car 25.00
Skywalker 12 foot Trampoline 240.00
Lego city fire station set 100.00
Sky Viper Drone 70.00
Star wars puzzle 12.00
Super water slide 50.00

Combine the rand() Function with Another Criterion

So the rand() function many times will be combined with another search criterion.

Say, we want to show the 3 random rows of a table. We would combine the ORDER BY rand() with LIMIT 3.

Below is the following PHP code to select 3 random rows (records) from the table.

So the above PHP code selects the columns, toy and price, from the PopularToys table and orders the rows in random order and limits the rows returned to 3. So this query selects 3 random rows from the table.

Using the same table, PopularToys, and adding, ORDER BY rand() LIMIT 3, we now get the following result.

Actual PHP Output

Super water slide 50.00
Remote-controlled helcopter 150.00
Star wars puzzle 12.00

Random Row Selector

This form above can be used to select a random row on a table.

If you create a dropdown menu, you can read the value chosen in the dropbox down menu with PHP and store it in a variable. You can then put this variable after the MySQL LIMIT keyword and decide how many rows are randomly selected.

The end result is a form shown below that can select any number of random rows.

How Many Random Rows Should Be Selected?

Related Resources

How to Delete a Row of a MySQL Table Using PHP

How to Delete All Rows of a MySQL Table Using PHP

How to Select a Specific Row of a MySQL Table Using PHP

How to Update a Value in a MySQL Table Using PHP

HTML Comment Box is loading comments...