+- 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: Help with sql query (/showthread.php?tid=60928)
Help with sql query - El Forum - 08-03-2014
[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!
Help with sql query - El Forum - 08-03-2014
[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());
}
Help with sql query - El Forum - 08-03-2014
[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?
Help with sql query - El Forum - 08-03-2014
[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.
Help with sql query - El Forum - 08-03-2014
[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
.
Help with sql query - El Forum - 08-04-2014
[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.