user6571534 user6571534 - 4 months ago 9
SQL Question

Query first, then randomize, and then retrieve remaining fields

I have a table with about 70 columns and 120,000 rows of data. What I want to do is randomize a record and then displaying the values of others columns of this record.
If I do fetch all data,

$result=mysqli_query($link, 'SELECT id, column1, column2, column3, ..., column 70 from table');
while ($row=mysqli_fetch_array($result))
{
$info[] = array('id'=>$row['id'], 'column1'=>$row['column1'], ...);
}
$randindex = rand(0,count($info));
$id = $info[$randindex]['id'];
echo $info[$randindex]['column1']; echo $info[$randindex]['column2']; ....


I'm afraid that this will significantly slow down the process. So I want to query only the ID before randomization, and then use the randomized ID to retrieve the other values of that record in the database.

$result=mysqli_query($link, 'SELECT id from table');
while ($row=mysqli_fetch_array($result))
{
$info[] = $row['id'];
}
$randindex = rand(0,count($info));
$id = $info[$randindex];


and then retrieve all other fields of this particular record somehow. I asked how to do this in SQL here but I would like to know if there is any more efficient way by other means besides SQL. Do I need to do a loop like this?

Answer

you can use ORDER BY RAND() directly in the sql query:

SELECT * FROM table ORDER BY RAND() LIMIT 1

ORDER BY RAND() actually makes random order of you rows, and then you just do LIMIT 1 in order to get only one row, the first one.

Comments