CODEWITHSUNDEEP
phpmysqlsqlcodeigniter
user1794918
user1794918

Reputation: 1159

Codeigniter join query failing

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 can see that is it only returning the table query for immun and CI is not join data from the other table.s I read somewhere that CI was not build to handle complex queries? Is that true?

Any ideas how to get the data I need? Thanks!

Upvotes: 1

Views: 207

Answers (2)

user1794918
user1794918

Reputation: 1159

     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 cross join for codeigniter. In my original post, I did not have conditionals. I found it here

https://ellislab.com/codeIgniter/user-guide/database/active_record.html

in the section about join. I seen that there was a place for conditions of the join. Once I added the conditions. I got the correct result set returned.

Upvotes: 0

dvtelles
dvtelles

Reputation: 200

You could see the query CI ran in your database.

Put the following code on the controller that render the page where this query is used:

$this->output->enable_profiler(TRUE);

This way CI will output a profiler in the end of the page with lots of information, including the executed queries needed for rendering the page. This should help.

Another hint, you must use alias in case you need to select columns with equal names from different tables. CI don't handle it well.

Upvotes: 1

Related Questions