Randomizing MySQL Results

[RandallK]

I am doing a SELECT query that returns 3 rows with 5 different 'elements'.

I then loop through those results with:

while ($row=mysqli_fetch_assoc($result)

echoing the information into a table.

I would like for them to display in a random order, and I was wondering what the best way to do that on the PHP side would be since I am already doing an "ORDER BY" in my query and cannot randomize in MySQL.

Thanks in advance.

[omoutop]

easy....SELECT field FROM table WHERE restriction=value ORDER BY RAND() LIMIT 10

this will show you 10 random results that match your restrictions

OR.. in php, use the shuffle() function. This assumes you have 2 queries.
- (query1) SELECT id FROM..... ORDER BY blah blah
- shuffle() your id order (you must create an array with the existing order to shuffle)
- (query2)SELECT * FROM blah blah ORDER BY shuffled_id_order

[RandallK]

The step two in your example is the part I am having some trouble with.

I want to take the three results I get (already sorted, so cannot RAND() them) and then randomize the results for display, but because the array is multi-dimensional I'm missing something on how to place it in an array, shuffle it properly, and then display it. Here is what I currently have that I am trying to tweak.

$query = "SELECT field1, field2, field3 MATCH(field4) AGAINST ('$field4' IN BOOLEAN MODE) AS R FROM table WHERE field5='Y' AND MATCH(field4) AGAINST ('$field4' IN BOOLEAN MODE) ORDER BY R DESC LIMIT 3";

$result = mysqli_query($dbc, $query);

while ($row=mysqli_fetch_assoc($result)) {
echo ('<td>' . $row['field1'] . $row['field2'] . $row['field3'] . '</td>');
}

Thank you for any suggestions.

[omoutop]

mm not the best approach but perhaps smething like this will do the trick
while ($row=mysqli_fetch_assoc($result)) {
$var[] = $row['field1'].'@@'.$row['field2'].'@@'.$row['field3'];
}
shuffle($var)
foreach ($var as $newvar)
{
$a = explode("@@", $newvar);
echo $[0].' - '.$a[1].' - '.$a[2];
}

[topr8]

>>I want to take the three results I get (already sorted, so cannot RAND() them)

another alternative would be in mySQL to insert the 3 results you want to return into a temporary table and then select from the temporary table in the way omoutop suggested.

or

create a view on the db which returns the 3 results you want, then select all from the view and ORDER BY RAND()

[Readie]

Yet another alternative:

$sql = SELECT * FROM table WHERE id >= ' . $someIntegerVariable . ' ORDER BY id ASC LIMIT 3';
$result = mysql_query($sql);

$numOne = rand(0, 2);
$numTwo = $numOne
while($numTwo != $numOne) {
$numTwo = rand(0, 2);
}

$numThree = $numOne
while($numThree != $numOne && $numThree != $numTwo) {
$numThree = rand(0, 2);
}

$rowOne = mysql_result($result, $numOne, "columnName");
$rowTwo = mysql_result($result, $numTwo, "columnName");
$rowThree = mysql_result($result, $numThree, "columnName");

Although some of the suggestions above look more economic

[RandallK]

I really like topr8's suggestion... short sweet, works well with the existing code.

My problem is coming from the fact this is for a site that might have many people accessing similar pages at the same time, which means the view is going to need to change frequently, possibly within milliseconds.

To use a VIEW in this way I believe I need to either A) Drop the VIEW as soon as I am done so the info isn't used by mistake by another page, or B) use CREATE OR REPLACE when making the table.

Either way I've got to GRANT DROP to the webuser, which is not a great idea if I understand correctly.

Lots of ideas here, anyone have any thoughts on which of the above is the 'best' way to proceed?

[topr8]

if you create a temporary table, it should be deleted at the end of the mySQL session or you can delete it in the code.

however if you use the view method, you wouldn't need to delete the view, if you create the view properly it would always return the 3 results you want even if they change - unless i've misunderstood what you are doing.

personally i'd create a stored function/procedure, and the create temporary table , the temporary table only exists within the scope of the stored procedure and should be deleted when the stored proc terminates, alternatively just drop the temp table at the end of the stored proc, you won't need to GRANT drop permissions to the webuser. just give the webuser permission to execute that stored procedure.

i try to do as much sorting/ordering as possible on the MySQL server (rather than in php) as that's what it is designed to do and it is very fast at it.

[willybfriendly]

Use mysql_fetch_array() and then shuffle() the array.

Perhaps not as elegant, but quite workable. Were I on the production machine I could probably dig up some old example code.