+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Development (https://forum.codeigniter.com/forumdisplay.php?fid=6)
+--- Forum: CodeIgniter 3.x (https://forum.codeigniter.com/forumdisplay.php?fid=17)
+--- Thread: SQL string to query builder of Codeigniter (/showthread.php?tid=62047)
SQL string to query builder of Codeigniter - happyape - 06-04-2015
What is the best method to make use of CI's DB query builder class in the example below?
Example query -
SELECT
*, IF( play_date < CURDATE() , 1, 0 ) playdate
, DATE_FORMAT(game_date, '%d/%m/%Y') game_date
, status
, (TO_DAYS(match_date) - TO_DAYS(CURDATE())) match_in_days
, (select coachname FROM coaches WHERE coach_id = contact.assigned_to_coach_id) coachname
FROM contact
INNER JOIN contact_status ON contact_status.status_id = contact.status_id
AND contact.status != 'inactive'
HAVING followup_in_days < 0
I know how to use query builder and I also know that I can run the above query this $this->db->query($above_sql);
But what I am interested to achieve is to be able to add $this->db->limit(), $this->db->where_in() etc etc query builder function in conjunction with above query. Is there any way to achieve that?
The reverse of what I am asking is $this->db->get_compiled_select() which gives us the SQL as string. I need to be able to input a string then let the query builder take over.
e.g.
$this->db->sql_string($above_sql);
$this->db->where_in('id', $id_array);
$this->db->limit(5, 50);
$query = $this->db->get();
Hope that makes sense.
RE: SQL string to query builder of Codeigniter - techbat - 06-04-2015
Try this code,
PHP Code:
$this->db->select("contact.*, IF( play_date < CURDATE() , 1, 0 ) playdate, DATE_FORMAT(game_date, '%d/%m/%Y') game_date, status,(TO_DAYS(match_date) - TO_DAYS(CURDATE())) match_in_days, (select coachname FROM coaches WHERE coach_id = contact.assigned_to_coach_id) coachname", false)
->join('contact_status', 'contact_status.status_id = contact.status_id', 'inner')
->where(array('contact.status<>' => 'inactive'))
->having('followup_in_days < 0');
$this->db->where_in('id', $id_array);
$this->db->limit(5, 50);
$query = $this->db->get('contact');
RE: SQL string to query builder of Codeigniter - happyape - 06-05-2015
Yes, I had actually figured it out already. I just needed to use $this->db->select() with second param as FALSE. Thanks for your answer.
RE: SQL string to query builder of Codeigniter - techbat - 06-05-2015
glad to help you.