• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Pick random result from query.

#1
[eluser]Fantass[/eluser]
Hello, heres my query

Code:
$this->db->having('active', 1);
        $this->db->having('widerange', 2);
        $this->db->having('counter < assigned');
        $query = $this->db->get('anuncios');
Wich return me various results according to the search but i want to pick up a random one
I've tried with Mysql this query

Code:
SELECT * FROM `anuncios`  HAVING `active` = 1 AND `counter` < `assigned` AND `widerange` = 2 ORDER BY RAND() limit 1
What return me exactly what i want.

then i've try adding the line
Code:
$this->db->order_by("id", "RAND");
but nothing Sad...

Someone could help me?
THANKS.

#2
[eluser]Kamarg[/eluser]
You probably need parens after RAND in your order by.

#3
[eluser]CroNiX[/eluser]
http://www.paperplanes.de/2008/4/24/mysq..._rand.html

#4
[eluser]Fantass[/eluser]
SOLVED:

Code:
$this->db->order_by('id', 'random');

Thanks all.

#5
[eluser]CroNiX[/eluser]
That still uses RAND(). You will be sorry if you run that on a moderately large table. Google "mysql rand()" for a million articles on why this is a huge performance killer. It has to retrieve all results, assign a number to each one, etc, which can literally take minutes depending on the size of the table. Running shuffle() on the result would probably be a lot more efficient.

#6
[eluser]aquary[/eluser]
I'd do "count" all the randomable rows, then use PHP rand() to pick some number within the range, then use the number as an offset with limit 1...

It was the fastest way I could think of.


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.