phpcodeigniterselectactiverecordsql-function

Convert query with SQL function calls in SELECT clause to CodeIgniter active record

I want to calculate the time difference.

I've tried query() and the 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 the value for diffhour :(

What's wrong with my code active record?

Solution

  • 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();