Codeigniter Active Record Class Error vs MySQL Raw Query

Question

In my Model, I wrote this function with MySQL raw query

function get_question_result($lr_id)
{
    $raw_query = 'SELECT question_record.qr_id, LEFT(question.question, 50) as question,
                    question.correct_answer
                    FROM question_record
                    INNER JOIN question ON question.q_id = question_record.q_id
                    WHERE question_record.lr_id = '.$lr_id.' ';
    $query = $this->db->query($raw_query);
    $questionresult = $query->result_array();
    return $questionresult;
}

It worked fine. It gave me the array I want. I continued my project.

Then suddenly I was curious to try it in CI Active Record Class.

function get_question_result($lr_id)
{
    $this->db->select('question_record.qr_id, LEFT(question.question, 50) as question, question.correct_answer');
    $this->db->from('question_record');
    $this->db->join('question', 'question.q_id = question_record.q_id', 'inner');
    $this->db->where('question_record.lr_id', $lr_id);
    $result = $this->db->get()->result_array();
    return $result;
}

It didn't work. It gave me this error

PHP Fatal error:  Call to a member function result_array() on a non-object

Just out of curiosity, where did I do wrong? Was it me writing it wrong or the result data structure with Active Record is just different?

'cause when I tried it again in Active Record without selecting this field

LEFT(question.question, 50) as question

It worked but it didn't give the field I want. Do you guys know why?

M Khalid Junaid M Khalid Junaid · Accepted Answer · 2016-01-02T20:23:59

In your $this->db->select() call you need pass FALSE as second parameter so that active record will not try to add backticks ` for your columns in select statement

function get_question_result($lr_id)
{
    $this->db->select('question_record.qr_id, LEFT(question.question, 50) as question, question.correct_answer',FALSE);
    $this->db->from('question_record');
    $this->db->join('question', 'question.q_id = question_record.q_id', 'inner');
    $this->db->where('question_record.lr_id', $lr_id);
    $result = $this->db->get()->result_array();
    return $result;
}

According to docs

$this->db->select() accepts an optional second parameter. If you set it to FALSE, CodeIgniter will not try to protect your field or table names with backticks. This is useful if you need a compound select statement.

$this->db->select();

Codeigniter Active Record Class Error vs MySQL Raw Query

1 Answers