Print a random row from a mysql query

Question

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.

score 7 · Accepted Answer · edited Apr 04 '12 at 23:59

7

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

edited Apr 04 '12 at 23:59

Francisco Presencia

8,732
6
46
90

answered Apr 01 '12 at 19:09

ThiefMaster

310,957
84
592
636

Will it work even with text? Even if it works, wouldn't it select the text that has the first ASCII as rand() said? (for example, if many rows start with 'H', won't it select only the first one EVERY time that rand() is 72?) – Francisco Presencia Apr 01 '12 at 19:17
@FrankPresenciaFandos: It is completely unrelated to the data in your table. It internally generates a random number for each row and then orders by that number. – ThiefMaster Apr 01 '12 at 19:18
Then it doesn't work like "ORDER BY en" does, which would actually order the result by the text inside those columns. I didn't know that, thanks! – Francisco Presencia Apr 01 '12 at 19:22

score 2 · Answer 2 · answered Apr 01 '12 at 19:11

2

Make it at query side

SELECT * FROM translations WHERE en IS NULL OR en='' ORDER BY rand() LIMIT 0,1

answered Apr 01 '12 at 19:11

safarov

7,793
2
36
52

score 1 · Answer 3 · answered Apr 01 '12 at 19:18

There are a few ways of doing this.

@ThiefMaster's answer will work - but "order by rand()" has pretty major performance problems on large tables. So, I'd populate your table with sample data of roughly the size you want to be able to grow to, and test the performance. If it's not a problem, leave it as it is - premature optimization is the root of all evil!

There are some alternatives; they rely on running two, separate queries; however, assuming you've got indices, that shouldn't be a problem.

Reworked for your scenarion, this becomes:

mysql_query('SELECT @count := COUNT(*) FROM translations WHERE en IS NULL OR en=''');
mysql_query('SET @offset = CONVERT(FLOOR(RAND() * @count), SIGNED)');
mysql_query('PREPARE mystatement FROM "SELECT * FROM translations WHERE en IS NULL OR en='' LIMIT ?, 1"');
$res = mysql_query('EXECUTE mystatement USING @offset');
$row = mysql_fetch_assoc($res);
print_r($row);

I don't expect this table to grow larger than 2000/3000 rows in the future, but thank you for expanding the info. I also liked the quote 'premature optimization is the root of all evil'. — Francisco Presencia, Apr 01 '12 at 19:27
It's not mine - and someone pointed out that putting "premature" in front of pretty much anything makes it sound bad... — Neville Kuyt, Apr 01 '12 at 19:29
Sorry, I meant, thanks for the quote that lead me to read a lot of different interesting stuff on the internet (; — Francisco Presencia, Apr 01 '12 at 20:25

Print a random row from a mysql query

3 Answers3

Linked