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

anche
– anche

2012-10-24 00:55:06 +00:00
Commented Oct 24, 2012 at 0:55 — anche
– anche, Commented Oct 24, 2012 at 0:55

JamesSwift · Accepted Answer · 2012-10-24 00:58:34Z

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, 2012 at 0:58

JamesSwift

8631 gold badge7 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Eric Witchin Over a year ago

This will work, however note the ORDER BY RAND() can take a while to process if you have many rows within your table

El Ocioso Over a year ago

Yes I dont wanna work with ORDER BY RAND() because my table will get quite big and this way will take too much time...

JamesSwift Over a year ago

@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.

anche · Accepted Answer · 2012-10-24 00:59:31Z

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, 2012 at 0:59

anche

2,9046 gold badges30 silver badges40 bronze badges

2 Comments

El Ocioso Over a year ago

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 Over a year ago

Thank you I will addapt it for my needs.

Abdussamad · Accepted Answer · 2012-10-24 01:02:43Z

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, 2012 at 1:02

Abdussamad

1011 bronze badge

Comments

xQbert · Accepted Answer · 2012-10-24 01:49:34Z

0

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

answered Oct 24, 2012 at 1:49

xQbert

35.5k2 gold badges46 silver badges67 bronze badges

Collectives™ on Stack Overflow

mysql + php: Selecting multiple random results

4 Answers 4

3 Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related