phpsqlmysqlcodeignitername-collision

CodeIgniter query with comma-JOINs returns column values from unintended table


I am trying to execute this query in CodeIgniter 2.2. I read the documentation http://www.codeigniter.com/user_guide/database/results.html.

My controller code is this

$query = $this->db->query("
    SELECT a.id, a.child, a.immune, a.immun_date,
           b.id, b.fname, b.lname,
           c.id, c.name
    FROM immun a,
         children b,
         immun_master c
    WHERE a.child = b.id
        AND c.id = a.immune
");
$immun = array();
foreach ($query->result() as $row) {
    $immun[] = array(
        $row->id,
        $row->child,
        $row->immune,
        $row->immun_date,
    );
}

The results that are turned is this:

array (
        0 => 
    array (
         0 => '2',
         1 => '1001',
         2 => '2',
         3 => '2011-04-23',
     ),
       1 => 
   array (
          0 => '3',
          1 => '1001',
          2 => '3',
          3 => '2011-04-30',
     ),
       2 => 
   array (
          0 => '6',
          1 => '1002',
          2 => '6',
          3 => '2011-04-30',
       ),
      3 => 
   array (
          0 => '5',
          1 => '1002',
          2 => '5',
          3 => '2011-04-29',
      ),
      4 => 
    array (
          0 => '1',
          1 => '1003',
          2 => '1',
          3 => '2011-01-06',
      ),
       5 => 
     array (
          0 => '3',
          1 => '1005',
          2 => '3',
          3 => '2010-10-04',
      ),
      6 => 
     array (
          0 => '3',
          1 => '1231',
          2 => '3',
          3 => '2014-08-01',
     ),
    )

These are wrong results. I was expecting is the merged results of the query. Below is what I get when I run the query in phpmyadmin

id  child  immune  immun_date  id  fname   lname  id  name  
1   1001    2      2011-04-23 1001 Johny    Jame   2  Swine Flu Vaccine 
2   1001    3      2011-04-30 1001 Johny    Jame   3  Bird Flu Vaccine 
3   1002    6      2011-04-30 1002 Chelsea  James  6  Hepatitis B 
4   1002    5      2011-04-29 1002 Chelsea  James  5  Measles Vaccine 
5   1003    1      2011-01-06 1003 Charles  Jacob  1  H1N1 Vaccine 
6   1005    3      2010-10-04 1005 Hansome  Little 3  Bird Flu Vaccine 
7   1231    3      2014-08-01 1231 Jennifer Ylanan 3  Bird Flu Vaccine 

Now, it would be nice if I could get CI to return the same set of merged data. I think it is only returning the data from immun and CI is not joining data from the other tables.

How to get the data I need?


Solution

  • function immChild()
    { 
        $this->db->select('c.id, c.name, b.id, b.fname, b.lname, a.id, a.child, a.immune, a.immun_date');
        $this->db->join('immun_master as c', 'c.id = a.immune', 'true');
        $this->db->join('children as b', 'a.child = b.id', 'true');
        $query = $this->db->get('immun as a')->result();
        
        return $query;      
    }
    

    This is the correct query for the joins in CodeIgniter. In my original post, I did not have conditionals. Once I added the conditions. I got the correct result set returned.