Active Record trouble

[eluser]Taff[/eluser]
I like the idea of active record, but it is causing me some trouble. Hopefully someone can tell me why it isn't working as well as a "normal" equivalent.

These are 2 functions out of my model which are nigh on identical:

Code:

function getTrackedAnswers($answer_id){
    
    $this->db->select('sgq_questions_id, sga_value');
    $this->db->where('sga_sgq_id','sgq_id');
    $this->db->where('sga_id','560');
    $this->db->from('search_guide_answers');
    $this->db->from(array('search_guide_questions','search_guide_answers' ));
    $query=$this->db->get();
    $query = $query->row();
    $return $query;
        
    }

Produces in the config profiler:

SELECT `sgq_questions_id`, `sga_value` FROM (`search_guide_answers`, `search_guide_questions`) WHERE `sga_sgq_id` = 'sgq_id' AND `sga_id` = '560'

but an empty array when I var_dump.

array(0) { }

Code:

function getTrackedAnswersNoAR($answer_id){
        $sql="SELECT sgq_questions_id, sga_value FROM search_guide_questions, search_guide_answers WHERE sga_sgq_id=sgq_id AND sga_id='560'";
        echo $sql;
        $res=mysql_query($sql);
        $row=mysql_fetch_array($res);
        return $row;
    }

The echo SQL produces:

SELECT sgq_questions_id, sga_value FROM search_guide_questions, search_guide_answers WHERE sga_sgq_id=sgq_id AND sga_id='560'

but gives me the result I would expect if I var_dump it...

array(4) { [0]=> string(4) "1198" ["sgq_questions_id"]=> string(4) "1198" [1]=> string(1) "0" ["sga_value"]=> string(1) "0" }

Can anybody see what I am doing wrong?

Thanks for any assistance.
Taff

[eluser]Phil Sturgeon[/eluser]
You have two things here I can notice here at first glance.

The first is not an error, just an oddity. You are using from twice. The 2nd overrides the first useage, so you don't need both.

The 2nd problem is that you are using a field name as a value. It will look at the field name as a string, as you may notice by the different quotes. Back tick (`) is a field, apostrophe is a string value.

For your wheres to work, try:

Code:

$this->db->where_raw('sga_sgq_id=sgq_id');
$this->db->where('sga_id',560);

That might not be spot on as im going from memory (at work, bored) but should get you in the right direction.

[eluser]Taff[/eluser]
Thanks for your reply.

I couldn't find anything in the user guide in regard to raw.

Code:

$where = "field=otherfield";
$this->db->where($where);

doesn't give me anything different.

Code:

$where = "field=otherfield";
$this->db->where($where,NULL,FALSE);

doesnt either.

I would have thought that a where query where one field is required to match another would be a relatively widely used procedure. I am adapting some queries that already exist, and would like to do without join where possible.

Hopefully you can find the syntax you have been using in the past.
Thanks,
Taff

[eluser]nEJC[/eluser]
I've just run into this same problem. This

Code:

$this->db->where('mc.section_id=s.id');

and

Code:

$this->db->where('mc.section_id','s.id');

both returned empty resultset. But the following works

Code:

$this->db->where('mc.section_id = s.id');

Notice extra spaces around equals sign.

I wonder would this be listed as a bug or feature? If its a feature it should probably be mentioned in documentation...

[eluser]ray73864[/eluser]
i find it second nature to just put a space before and after an equal sign (or anything like that really, + sign, etc...) since it makes it look neater and makes the different parts look separate from each other.