codeigniter data retrieve issue?

I want to retrieve data from a table1 and I have to retrieve data from another table table2 with foreign key of table1 and display in views. How is it possible?

My controller is:

$data['products'] = $this->MProducts->getProducts($customer_id);
        foreach($data['products'] as $product){
            $cat_id = $product['category_id'];
            $data['category_name'] = $this->MCats->getCategoryById($cat_id);
        }

My view is:

f开发者_如何学Pythonoreach ($products as $list){
        echo "<tr valign='top'>\n";
        echo "<td align='center'>".$list['name']."</td>\n";

        echo "<td align='center'>".$category_name."</td>\n";
        echo "<td align='center'>";
        echo $list['status'];
        echo "</td>\n";

        echo "<td align='center'>".$list['marked_price']."</td>\n";
        echo "<td align='center'>".$list['discount_percent']."</td>\n";
        echo "<td align='center'>";
        echo anchor('products/partner/edit/'.$list['id'],'edit');
        echo " | ";
        echo anchor('products/partner/delete/'.$list['id'],'delete');
        echo "</td>\n";
        echo "</tr>\n";

In $category_name it gives the last value of category in all rows.

You should read about joins here.

But you would do something like:

$this->db->select('products.*, category.category_name');
$this->db->join('products', 'category.category_id = products.category_id');
$this->db->where('products.customer_id', $customer_id); // i guessed this part
$this->db->get('products');

// EDIT: also you have an mistake in your loop. It should look like this:

foreach($data['products'] as $key=>$product){
    $cat_id = $product['category_id'];
    $data['products'][$key]['category_name'] = $this->MCats->getCategoryById($cat_id);
}

then to should each products' category in your view use this:

echo "<td align='center'>".$list['category_name']."</td>\n";