68834

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++; }

<strong>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?</strong> 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 <a href="https://stackoverflow.com/questions/3502096/mysqlhighload-random-row-from-big-query-result" rel="nofollow">other methods</a> I've seen around as they use number with ORDER BY.

Answer1:

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

Answer2:

Make it at query side

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

Answer3:

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 <a href="http://www.electrictoolbox.com/msyql-alternative-order-by-rand/" rel="nofollow">alternatives</a>; 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);

Recommend

  • show message when no rows selected mysql php
  • Number of entries in DB PHP
  • Offset from end / reverse offset?
  • Compare a column between 2 csv files and write differences using Python
  • output json array in php
  • href inside href [duplicate]
  • Storing data from SQL in array
  • Getting proper map boundaries
  • why 0.1+0.2-0.3= 5.5511151231258E-17 in php [duplicate]
  • Show records ordered with maximum price first in PHP & MySQL
  • Passing parameter through “window.location.href”
  • Getting syntax error in mysql-php. You have an error in your SQL syntax;
  • JSON - slashes not escaping
  • Problem with Django using Apache2 (mod_wsgi), Occassionally is “unable to import from module” for no
  • PDO error when wrong host name
  • Randomizing -and remembering that randomisation- multiple choice questions in php
  • Add dynamic data to line chart from mysql database with highcharts
  • gspread or such: help me get cell coordinates (not value)
  • Scrapy recursive link crawler
  • Is there a javascript serializer for JSON.Net?
  • NetLogo BehaviorSpace - Measure runs using reporters
  • Window Size for Mac application
  • Where to put my custom functions in Wordpress?
  • How to handle AllServersUnavailable Exception
  • Display Images one by one with next and previous functionality
  • ORA-29908: missing primary invocation for ancillary operator
  • How to get next/previous record number?
  • align graphs with different xlab
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • Linker errors when using intrinsic function via function pointer
  • Windows forms listbox.selecteditem displaying “System.Data.DataRowView” instead of actual value
  • Buffer size for converting unsigned long to string
  • python regex in pyparsing
  • How do you join a server to an Active Directory (domain)?
  • Android Google Maps API OnLocationChanged only called once
  • How does Linux kernel interrupt the application?
  • LevelDB C iterator
  • Binding checkboxes to object values in AngularJs
  • How can i traverse a binary tree from right to left in java?
  • How can I use threading to 'tick' a timer to be accessed by other threads?