CODEWITHSUNDEEP
phpmysqloptimization
Francisco Presencia
Francisco Presencia

Reputation: 8851

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.

Upvotes: 5

Views: 1268

Answers (3)

ThiefMaster
ThiefMaster

Reputation: 318518

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

Upvotes: 7

Neville Kuyt
Neville Kuyt

Reputation: 29629

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);

Upvotes: 1

safarov
safarov

Reputation: 7804

Make it at query side

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

Upvotes: 2

Related Questions