getProducts then getVariations for each product and display in table view with Codeigniter

Question

Ok, I'm new to coding. I will try to present this question as clearly as possible. I am looking to achieve the following table rows.

    <table>
    <tr>Product 1 without variations<tr>
    <tr>Product 2 with variations<tr>
       <tr class="variation">Variation 1 of Product 2<tr>
       <tr class="variation">Variation 2 of Product 2<tr>
    <tr>Product 3 without variations<tr>
    </table>

So using the MVC pattern in CodeIgniter I need to know how to achieve this. Ideally I want to create the most reusable code, with the least amount of PHP in the view file. Right?

I guess my main question would be, how do i get the $productid back to the $getVariations() function from the foreach loop in the view. Or even more, is that the best way to do things?

Example code:

Model :

    public function getProduct(){

              $query->this->db->query("select * from isc_products");

              return $query->result();


    public function getVariations($productid){

              $query->this->db->query("select * from isc_products_variations where variationproductid = $productid");

              return $query->result();

Controller :

    public function products() {

        $this->load->model("products");

        $data['products'] = $this->products->getProducts();

        $data['variations'] = $this->products->getVariations($productid);

        $this->load->view('view_products',$data);

        }

View :

<!-- table data -->

    <?php foreach ($products as $product) : ?>

        <tr><td><?php echo $product->prodname; ?></td></tr>

        <?php foreach ($variations as $variation) : ?>

               <?php if($variation->parentproductid == $product->productid) : ?> 

                <tr class="variation"><td><?php echo $variation->optionname; ?></td></tr>   

           <?php endif; endforeach; ?>

        <?php endforeach; ?>

Answer 1

Consider joining your products and variations tables in the Model when you query the database for the data. In your model:

  $query->this->db->query("select * from isc_products x,isc_products_variations y where x.productid = y.variationproductid");

This will return each product with it's associated variations. You can directly fetch this data and send it to the view.