Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Print a random row from a mysql query

I was looking for a way of creating a collaborative translation widget. So I have a mysql database and table (called translations), and a little script to allow users to translate one page at a time.

But I'm not quite convinced with my script. I don't think it's efficient enough. First, the mysql gets all the rows with the empty 'en' column, and then a single one of them is showed by screen through a while. Is there any other way of doing this? This is the code:

//Retrieve all the data from the "translations" table
$result = mysql_query("SELECT * FROM translations WHERE en IS NULL OR en=''") or die(mysql_error());

$Randnum=rand(0,mysql_num_rows($result)-1); //Gets a random number between 0 and the maximum number of rows
$i=0;   //Start to 0
while($Col = mysql_fetch_array($result))  //While there are rows to evaluate
    {
    if ($i==$Randnum)
        {
        echo "\"".$Col['es']."\"<br><br>Translate it to English: <br>";
        }
    $i++;
    }

I was looking for something like "echo $Col[$Randnum]['es']" or "echo $Col.$Randnum['es']" instead of using the whole while loop to print a single random row. How can I implement this? If it's just a matter of optimization. If you could come with an script or idea to assign to $Col just ONE row with a random number and the empty 'en' col, that'd be even better! (I think it's not possible this last bit). The 'en' row is text so I don't know how to implement other methods I've seen around as they use number with ORDER BY.

like image 941
Francisco Presencia Avatar asked Apr 01 '12 19:04

Francisco Presencia


People also ask

How do I display random rows in SQL?

To get a single row randomly, we can use the LIMIT Clause and set to only one row. ORDER BY clause in the query is used to order the row(s) randomly. It is exactly the same as MYSQL. Just replace RAND( ) with RANDOM( ).

How do you fetch random data?

There are a few ways to select random data without duplicates in Excel. Generally, you'd use the RAND function to assign a random number to each cell, and then you pick a few cells by using an Index Rank formula. Copy the above formula to as many cells as many random values you want to pick.

How do I shuffle data in MySQL?

If you need the rows in a random order, grab the required number of rows and then use PHP's shuffle function on the array of rows returned. There can be quite a performance penalty with using ORDER BY RAND() in a query, depending on how many records there are.


2 Answers

You can use ORDER BY RAND() LIMIT 1 in your query to fetch a single random row from the database.

like image 165
ThiefMaster Avatar answered Oct 05 '22 04:10

ThiefMaster


Make it at query side

SELECT * FROM translations WHERE en IS NULL OR en='' ORDER BY rand() LIMIT 0,1
like image 26
safarov Avatar answered Oct 05 '22 03:10

safarov