mysql + php: Selecting multiple random results

Question

I've been looking for this for a while but with no success.

I am trying to implement a recomendation bar, for example like in youtube, when you are seeing a video it shows the list or recommended videos on the right.

At this moment I am using this method:

$offset_result = mysql_query( " SELECT FLOOR(RAND() * COUNT(*)) AS `offset` FROM `$tablename` ");
$offset_row = mysql_fetch_object($offset_result ); 
$offset = $offset_row->offset;
$result_rand = mysql_query( " SELECT * FROM `$tablename` LIMIT $offset, 9 " );

This works fine, but sometimes doesn't show any result, and the problem is also that its not completely random, because it shows for example the first ID as 200, so the next result will be id 201 and then 202 and so.

I would like to know if there is a way to show this 9 randon results, for example 1º result id 500, 2º result id 10, 3º result id 788, etc etc?

Thank you

post your code in a better format – Angelo A Oct 24 '12 at 00:55 — Angelo A, Oct 24 '12 at 00:55

score 1 · Answer 1 · answered Oct 24 '12 at 00:58

1

Not entirely sure this answers what you are looking for, but try:

$result_rand = mysql_query("SELECT * FROM " . $tablename . " ORDER BY RAND() LIMIT 9");

answered Oct 24 '12 at 00:58

JamesSwift

863
1
7
16

2

This will work, however note the ORDER BY RAND() can take a while to process if you have many rows within your table – Eric Witchin Oct 24 '12 at 01:00
Yes I dont wanna work with ORDER BY RAND() because my table will get quite big and this way will take too much time... – El Ocioso Oct 24 '12 at 01:18
@HogardelOcio - In that case, you should probably use a modified version of Angelo A's code. I would get the count of records in the table, then generate a set number of IDs to select from the database. This also allows you to reject one of the random IDs if it is the same as the one currently being displayed on the page. – JamesSwift Oct 24 '12 at 01:21

score 0 · Accepted Answer · answered Oct 24 '12 at 00:59

0

You can use php rand() function to create 5 numbers and save them in an array:

http://php.net/manual/en/function.rand.php

<?php
$rand_array = array();
for($i=1;$i<5;$i++) {
    $rand_array[$i] = rand(0,500);
}
?>

and after that create a query with every int with a foreach loop and work with your data.

<?php
foreach ($rand_array as $integer) {
     $q = "SELECT * from $tablename WHERE id='$integer';";
}
?>

Does this helps?

answered Oct 24 '12 at 00:59

Angelo A

2,744
6
28
37

This is an intresting solution, but my table have holes in the ids, because of some I have to delete. Is there a way to do not count ids that dont exists? – El Ocioso Oct 24 '12 at 01:15
Thank you I will addapt it for my needs. – El Ocioso Oct 24 '12 at 02:43

score 0 · Answer 3 · answered Oct 24 '12 at 01:02

0

First you should use mysqli_ functions instead of mysql_ because the latter is deprecated. Second use order by rand() to get random rows:

$rand_result = mysqli_query( "SELECT * FROM $tablename ORDER BY RAND() LIMIT 9;" );

answered Oct 24 '12 at 01:02

Abdussamad

101
1

score 0 · Answer 4 · answered Oct 24 '12 at 01:49

UNTESTED:

SELECT id, @rownum:=@rownum+1 AS rownum, name FROM users u, (SELECT @rownum:=0) r

THis will give a unique number to each row in sequence. Now if you create a temp table with 9 random numbers between 1 and count(*) of your table and JOIN those two together...

Not sure about performance but seems like it might be faster than Rand and order by since I only need 9 random numbers

mysql + php: Selecting multiple random results

4 Answers4