Help with sql query

[eluser]Unknown[/eluser]
hi,
I have an sql query which works fine, but I am having real trouble trying to convert it to codeigniter active record!

function getSnippets($question_number) {

$result = mysql_query(

"SELECT

(SELECT answer FROM codePiece WHERE id = cp1) AS answer1,
(SELECT answer FROM codePiece WHERE id = cp2) AS answer2,
(SELECT answer FROM codePiece WHERE id = cp3) AS answer3,
(SELECT answer FROM codePiece WHERE id = cp4) AS answer4,
(SELECT answer FROM codePiece WHERE id = cp5) AS answer5

FROM question
WHERE question.id = $question_number");


if(!$result){
echo mysql_error();

}

$snippetsArray = array();
while ($record = mysql_fetch_assoc($result)){
array_push($snippetsArray, $record);

}

return $snippetsArray;

}

If anyone could help, or even make a suggestion that would be such a great help as I am still new to CI and have tried so many different solutions but nothing works .

Thankyou in advance!

[eluser]rufnex[/eluser]
Hi,

you can try something like that:

$this->db->select('(select answer from codePiece where id = cp1) as answer1');
$this->db->select('(select answer from codePiece where id = cp2) as answer2');
$this->db->select('(select answer from codePiece where id = cp3) as answer3');
$this->db->select('(select answer from codePiece where id = cp4) as answer4');
$this->db->select('(select answer from codePiece where id = cp5) as answer5');
$this->db->where('id', $question_number);
$query = $this->db->get('question');
if($query->num_rows() > 0)
{
print_r($query->result_array());
}

[eluser]CroNiX[/eluser]
That's not a query...that's 6 queries can't you just use a join and make it a single query?

[eluser]CroNiX[/eluser]
Also, you don't have to use active record for everything. There are many complex queries that you just can't do with the limitations of active record. You can always just do $this->db->query('the complex query SQL'); in those cases.

[eluser]Unknown[/eluser]
Thank you so much for your replies and your time .
rufnex your solution worked.. Thankyou .

CroNix also thankyou for your advice. I had a feeling a join was the way to fix it, but I still find joins very hard to grasp with my sql abilities .

[eluser]ivantcholakov[/eluser]
In CI3 limitations of the query builder are less, it is improved. For quite complex cases (UNION clause for example) the method get_compiled_select() helps for SQL generation of the sub-queries, so you again can rely on automatic escaping and SOL-dialect abstraction.