Mysql join issue

[afridy]

Hi folks,
having a bad time with a join statement (codigntior active records)

           $this->db->select("*"); //SELECT * 
           $this->db->from("$this->table"); //expenses
           $this->db->join('expense_types','expense_types.expense_type_id = expenses.expense_type_id');  
           $this->db->join('sub_payments','sub_payments.record_id = expenses.record_id','right');

Tables

Output

My problem is, the array does not return the record id 31 to the record_id column. var_dump shows null for that. but 32 is returned.

Updated

SELECT *
FROM `expenses`
JOIN `expense_types` ON `expense_types`.`expense_type_id` = `expenses`.`expense_type_id`
LEFT JOIN `sub_payments` ON `expenses`.`record_id` = `sub_payments`.`record_id`
WHERE `expenses`.`record_id` LIKE '%%' ESCAPE '!'
OR  `expenses`.`expense_date` LIKE '%%' ESCAPE '!'
OR  `expense_types`.`type` LIKE '%%' ESCAPE '!'
OR  `expenses`.`amount` LIKE '%%' ESCAPE '!'
OR  `expenses`.`note` LIKE '%%' ESCAPE '!'
ORDER BY `expenses`.`record_id` DESC

when i executed the sql produced by above query in phpmyadmin directly, it works.

The CI Model code of return result part of above query is

      function make_datatables(){  
           $this->make_query();  
           if($_POST["length"] != -1){  
                $this->db->limit($_POST['length'], $_POST['start']); //LIMIT 
           }  
           $query = $this->db->get(); 
           $result=$query->result();
           //print_r($result);
           //print_r($this->db->last_query());
           return $result;  

Thank you.

[r937]

@afridy

when i executed the sql produced by above query in phpmyadmin directly, it works.

i don’t understand your LIKEs (they seem useless)

however, the LEFT JOIN seems okay

perhaps your question should be in the php forum?

[m_hutley]

@r937

i don’t understand your LIKEs (they seem useless)

LIKE “%%” == IS NOT NULL ? (I’m guessing. It seems pretty uselss to me too.)

@afridy

          $this->db->join('sub_payments','sub_payments.record_id = expenses.record_id','right');`

@afridy

LEFT JOIN `sub_payments` ON `expenses`.`record_id` = `sub_payments`.`record_id`

one of these things is not like the other~…

Your “it works” list shows NULL in the sub_id and id fields, indicating that there is no matching sub_payments entry with record_id 31.

Your query did exactly what you told it to do. It RIGHT JOIN’d a table in which one of the rows was missing, so it threw the data away.

Notice that your fields left-to-right for record_id are 31,NULL. If you stick both of those into an array, in-order, the NULL will be the thing left over.
This is a case of SELECT * biting you in the arse. Left join the table, and specify your select fields properly, and you’ll get the data you desire.

[afridy]

Hi guys,
the liks are actually for data table search. i should have remove that when posting any way. let me try this and see

specify your select fields properly, and you’ll get the data you desire.

[afridy]

works charm !!! Thank you guys for wonderful help.

           $this->db->select("expenses.record_id,expenses.expense_date,expenses.expense_type_id,sub_payments.sub_id,expenses.amount,expenses.note"); //SELECT * 
           $this->db->from("$this->table"); 
           $this->db->join('expense_types','expense_types.expense_type_id = expenses.expense_type_id');  
           $this->db->join('sub_payments','expenses.record_id = sub_payments.record_id','left');

Lesson Learned

SELECT * will bite the arse.