SELECT inside SELCT in active record codeigniter

Question

I want to calculate the time difference I've tried query and query work on mysql

SELECT  *, CONCAT_WS(" ", date1, hour1) AS rhour1,  CONCAT_WS(" ", date2, hour2) AS rhour2, (SELECT TIMEDIFF(rhour2,rhour1) AS diffhour) from TABLE1

and I write in the active record by using codeigniter query builder:

$this->db->select('*, CONCAT_WS(" ", date1, hour1) AS rhour1,  CONCAT_WS(" ", date2, hour2) AS rhour2, (SELECT TIMEDIFF(rhour2,rhour1) AS diffhour)');
$this->db->from('table1');
$this->db->join('table2','table1.code_number = table2.code_number');    
$query = $this->db->get();

the result i can't get value diffhour :(

what's wrong with my code active record?

you should use table name with all you columns name and remove select in side select ,should be like this `TIMEDIFF(rhour2,rhour1) AS diffhour` — Pradeep, Jul 05 '18 at 06:42
I've tried code like this $this->db->select('*'); $this->db->select('CONCAT_WS(" ", date1, hour1) AS rhour1, CONCAT_WS(" ", date2, hour2) AS rhour2') $this->db->select('(SELECT TIMEDIFF(rhour2,rhour1) AS diffhour)'); and like this $this->db->select('*'); $this->db->select('CONCAT_WS(" ", date1, hour1) AS rhour1, CONCAT_WS(" ", date2, hour2) AS rhour2') $this->db->select('TIMEDIFF(rhour2,rhour1) AS diffhour'); the result still same i cant get value diffhour there is another solution? — maitimo, Jul 05 '18 at 06:59
what error u get ? something like this unknown column name rhour1 and rhour2 or some other error — Pradeep, Jul 05 '18 at 07:07
if i use code $this->db->select('(SELECT TIMEDIFF(rhour2,rhour1) AS diffhour)'); the result Undefined diffhour if i use code $this->db->select('TIMEDIFF(rhour2,rhour1) AS diffhour'); the result unknown column rhour2 but ive tried on mysql its work SELECT *, CONCAT_WS(" ", date1, hour1) AS rhour1, CONCAT_WS(" ", date2, hour2) AS rhour2, (SELECT TIMEDIFF(rhour2,rhour1) AS diffhour) from TABLE1 — maitimo, Jul 05 '18 at 07:16

score 1 · Accepted Answer · answered Jul 05 '18 at 07:19

Hope this will help you :

Note : make sure you have added table name with all column name just like this table1.code_number

$this->db->select('*');
$this->db->select('CONCAT_WS(" ", date1, hour1) AS rhour1');
$this->db->select('CONCAT_WS(" ", date2, hour2) AS rhour2');
$this->db->select('TIMEDIFF(CONCAT_WS(" ", date2, hour2),CONCAT_WS(" ", date1, hour1)) AS diffhour');

$this->db->from('table1');
$this->db->join('table2','table1.code_number = table2.code_number');    
$query = $this->db->get();

pls check my answer and let me know what u get? – Pradeep Jul 05 '18 at 07:19 — Pradeep, Jul 05 '18 at 07:19

SELECT inside SELCT in active record codeigniter

1 Answers1