Pick random result from query. - Printable Version +- CodeIgniter Forums (https://forum.codeigniter.com) +-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20) +--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23) +--- Thread: Pick random result from query. (/showthread.php?tid=49552) |
Pick random result from query. - El Forum - 02-24-2012 [eluser]Fantass[/eluser] Hello, heres my query Code: $this->db->having('active', 1); 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 then i've try adding the line Code: $this->db->order_by("id", "RAND"); Someone could help me? THANKS. Pick random result from query. - El Forum - 02-24-2012 [eluser]Kamarg[/eluser] You probably need parens after RAND in your order by. Pick random result from query. - El Forum - 02-24-2012 [eluser]CroNiX[/eluser] http://www.paperplanes.de/2008/4/24/mysql_nonos_order_by_rand.html Pick random result from query. - El Forum - 02-24-2012 [eluser]Fantass[/eluser] SOLVED: Code: $this->db->order_by('id', 'random'); Thanks all. Pick random result from query. - El Forum - 02-24-2012 [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. Pick random result from query. - El Forum - 02-24-2012 [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. |