A Good Solution To Not Use Order By Rand()

[beyzad]

Hi there.

 

I need a query to get 20 random data from my database using some conditions.

 

So here is my code:

 

<?php
$max = single_field_q("SELECT MAX(`ads_product_id`) FROM `ads_products`");
$rand = rand(20 , $max);
$products = multi_row_q("SELECT * FROM `ads_products` WHERE `ads_product_id`<" . $rand . " ORDER BY `ads_product_id` DESC LIMIT 20");
?>

 

It will work very well. But if i use my conditions, This will sometimes returns less than 20 results. And sometimes zero results

 

I know why it returns less than 20. Now i need a solution for this.

 

Thanks.

Edited October 17, 2012 by beyzad

[PFMaBiSmAd]

Your code is not getting 20 random products. It is getting a block of upto 20 products ending at a random product id.

 

If you have removed a number of earlier product id's, when $rand is a low value, there aren't enough product id's that are less-than $rand.

 

If you truly want 20 random products, retrieve all your product id's into an array. shuffle the array, then use array_slice to get 20 of the id's, then implode them and use them in an IN() statement in one query.

Edited October 17, 2012 by PFMaBiSmAd

[Jessica]

I was going to comment earlier that the way I do it is select every ID into an array, use array_rand or shuffle, then do another query using only those IDs. However I wasn't sure if there was a more elegant approach. If PFM is recommending that approach I feel okay about it too.

[beyzad]

Hi again.

 

Yeah. my problem in exactly what you said 'PFMaBiSmAd'. Because of very very large DB, I was looking for the best solution and i think the way you said is cool.

 

Thanks.

[beyzad]

Hi again.

 

I was thinking about this:

 

there are 2 methods for what i want:

 

1- Using ORDER BY RAND()

2- Using the method that 'PFMaBiSmAd' said.

 

In both methods above, ALL fields from that table will be selected, then 20 of them will be exported randomly. The difference is in first method, MySQL Will select random rows and in second method, PHP will do that. Plus in the second method, we have 1 more condition in our query.

 

Is the second method better than first by the way?

 

Thanks

[Barand]

you might also want to look at this "sticky" from the MySQL forum

 

http://forums.phpfreaks.com/topic/36709-the-mysql-sticky/?do=findComment&comment=178450

[beyzad]

you might also want to look at this "sticky" from the MySQL forum

 

http://forums.phpfre...ky/#entry178450

 

Hello.

 

I already did sir. my question is the difference between the two solutions above.

 

Thanks

[fenway]

ORDER BY RAND() performs very poorly on any reasonable amount of data. Which, IMHO, is fine, because very few real-world problems need totally random (or truly random) anyway.