Picking data at random with mySQL is easy

Here's a very simple tip that is great when you know about it and saves a lot of hassles returning data from a database then using PHP functions to randomise it.

The trick is the ORDER BY clause that is passed in a mysql query.

Let's say there are ten rows of data that you are returning from the database, well that's easy with LIMIT 10. But then what if you want them to come out in a random order each time?

That's also easy, just use a query like this:

SELECT * FROM mytablename ORDER BY rand() LIMIT 10

Notice that you need to pass the brackets () after rand for this to work, and there you have it: each time you run the query the ten rows will be returned in a random order!

Re : Picking data at random with mySQL is easy

This is a good little tip

What I would like to know is how random the information that this query returns is.

For instance if you do it 100 times will you really get random ordering returned each time or will it start to repeat?

Is it safer to use one of the PHP random functions rather than relying from it in the mysql query even though there is no doubt the mysql one is easier?

Re : Picking data at random with mySQL is easy

Hi Jenna

Glad that you like the tip.

In terms of how random the information is I can't say for sure, however I have used the query quite a lot in various applications and have not noticed that there is any repetition or the same thing being returned all the time, so I would say that in general it is good enough.

If you need a really really good random number then I can't say for sure as I haven't tested or researched it in that level of detail.