Fetching data from relational database in codeigniter

Question

I have got 2 tables; table1 and table2. Both of them are related to eachother with a common groupid I am able to query the second table successfully using the following code:

$query = $this->db->query("SELECT * FROM `table2` WHERE `memberid`='$id'"); 
$data['relation'] = $query->result_array();

Now using groupid result I want to query the first table i.e. table1

I have tried the following methods, without any success:

for($ii = 0 ; $ii < count($data['relation']) ; $ii++){
        $id = $data['relation'][$ii]['groupid'];
        $query1 = $this->db->query("SELECT * FROM `group` WHERE `id`='$id'");
}             

$data1['group'] = $query1->result_array();

$fine = array_merge($data, $data1);
print_r(count($fine)); // the count result is 1 ideally should be 2

The above code only returns the last row of the table1 however I am looking for all the results.

When I run the above code inside the "for" loop, it shows me a count of 33:

    for($ii = 0 ; $ii < count($data['relation']) ; $ii++){
        $id = $data['relation'][$ii]['groupid'];
        $query1 = $this->db->query("SELECT * FROM `group` WHERE `id`='$id'");

$data1['group'] = $query1->result_array();

$fine = array_merge($data, $data1);
print_r(count($fine)); // the count result is 33 ideally should be 2
}

I know how to achieve this in core php however not too sure how to do it in CI. I am new to CI, any help will be greatly appreciated.

Thanks, Utpal

generally speaking: if you're running nested loops, and the inner query is using data from the query, then almost always the queries should be re-written as a single JOINed query. And as you're writing the queries, you're probably vulnerable to sql injection attacks. — Marc B
– Marc B, Commented Feb 4, 2015 at 14:32
Consider using the tools that the framework provides you - or there's really no point in using a framework — sjagr
– sjagr, Commented Feb 4, 2015 at 14:35

Jeet Kapadia · Accepted Answer · 2015-02-04 14:52:20Z

0

$ok = array();
     for($ii = 0 ; $ii < count($data['relation']) ; $ii++){
            $id = $data['relation'][$ii]['groupid'];
            print_r ($id);
            echo "<br>";
            $query1 = $this->db->query("SELECT * FROM `group` WHERE `id`='$id'");
            $data1['group'][$ii]= $query1->result_array();

    }
        //print_r($data1['group']);
        $fine = array_merge($data, $data1);
        print_r($fine);

You need to create a array ($data1) outside this for loop and define $query->result_array(); inside forloop as shown above

answered Feb 4, 2015 at 14:52

Jeet Kapadia

184 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Fetching data from relational database in codeigniter

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related