cant get a random mysql selection going :(

[eluser]ommy[/eluser]
Hi there,
I ran into a problem trying to use the order_by:random method. It's probably something stupid i'm not seeing on this goofy saturday. But a lot of googling and reading up on the distinct, mysql, php etc. didnt help yet.

I'm taking the leap and asking here. There's bound to be someone here kind enough to point out my ignorance

ps: This is the error codeigniter/mysql is spitting out:

Code:

A Database Error Occurred
Error Number: 1054
Unknown column ' RAND() LIMIT 1' in 'order clause'
SELECT DISTINCT * FROM (`gallery`) WHERE `active` = 1 AND `public` = 1 ORDER BY ` RAND() LIMIT 1

Thanks in advance for any help!
Tommy

[eluser]jedd[/eluser]

Code:

Unknown column ' RAND() LIMIT 1' in 'order clause'
SELECT DISTINCT * FROM (`gallery`) WHERE `active` = 1 AND `public` = 1 ORDER BY ` RAND() LIMIT 1

Well, yes - it's the backtick before RAND

What's your code for generating this query?

[eluser]ommy[/eluser]
forgive me for the pseudo code. but i doubt its a syntax error.
the sequence of the db actions is exact though.

thanks for yr reply

Code:

db orderby id random
db limit 1
db getwhere active =1 public=1 -> row

[eluser]jedd[/eluser]
[quote author="ommy" date="1252787014"]
forgive me for the pseudo code.
[/quote]

Is there a reason you can't cut-n-paste the actual code you're using?

but i doubt its a syntax error.

If only your doubt was sufficient to resolve the problem.

The error message you're showing above is fairly explicit - it says it can't understand the column 'RAND ...' , and there's a backtick clearly visible. The latter would induce the former error.

the sequence of the db actions is exact though.

It's not a sequencing problem.

[eluser]ommy[/eluser]
There was a reason indeed. I was checking the your reply from a mobile device and didnt have the files on that device.
Here's the actual copy-pasted code.

Code:

$this->db->order_by('id','random');
$this->db->limit(1);
$row = $this->db->get_where('gallery',array('active' => 1, 'public'=> 1))->row();

I messed around with situations a bit more and it seems that the following ways do work:

Code:

$random_row = mysql_fetch_row(mysql_query("select * from gallery order by rand() limit 1"));
$random_row = $this->db->query('select * from gallery order by rand() limit 1')->row();

i thought it was solved with this, but turned out to ignore the rand():

Code:

$this->db->order_by('id','rand()');
$random_row = $this->db->get_where('gallery',array('active' => 1, 'public'=> 1))->row();

and finally i just tried regular old mysql, which worked perfectly:

Code:

$row = $this->db->query('select * from gallery where active=1 and public=1 order by rand() limit 1')->row();

could the backtick you're talking about be a typo in the constant for random?
I'm probably on the wrong track with this. Just trying to figure out what it is i missed.
Doesn't seem like too hard of an operation.

Thanks for the reply. I'll remind myself never to be sure about syntax

[eluser]jedd[/eluser]
You don't keep a full cvs of your source on your blackberry?!?!

Odd, the first set you show there - that should work. random's supported on mysql, and you're right about this not being a complex construct.

What happens if you relocate your limit to the get_where (third param) - what's the actual query produced then? If that works, then it looks like a bug.

I don't use AR calls, so I'm not the best person to be answering this (actually that's why I wanted to see your code, in case you were using a self-constructed query).

[eluser]ommy[/eluser]
same error gets thrown. I've tried to use the profiler to get to see the actual query.
But it seems the error cancels out the profiler being printed. So i'll guess i'll have to take the error's word for it when it comes to the actual built query
I'll check out if i can find the method causing this, maybe it's just a monday-morning typo

[eluser]ommy[/eluser]
been playing around with the mysql driver and the activerecord class and the backtick indeed looks like a tiny bug that shows up because no column is specified.
Hope my playing around leads to something constructive

[eluser]ommy[/eluser]
the ar_orderby for compile_select at 1545 of the active record class is the culprit. I don't think it's the cause of the backtick though.

[eluser]ommy[/eluser]
retarded solution number one (DB_active_rec.php – line 1544):

Code:

//$sql .= implode(', ', $this->ar_orderby); // original line

$bugcheck = implode(', ', $this->ar_orderby);
$sql .= substr($bugcheck, 0, 2)==$this->_escape_char." "?substr($bugcheck,2):$bugcheck;

helps me do:

Code:

$this->db->order_by('id','random');
$row = $this->db->get_where('gallery',array('active'=>1,'public'=>1),1)->row();

works for now, but i'm guessing it'll create more problems than it'll solve.
If anyone can find the true cause of this odd backtick, yr welcome to share

Thanks a bunch already