Nested foreach loop cateogories and products

0
votes

I have the following tables:

Categories: category_id, category_name

enter image description here

Products: id, category_id, product_name

enter image description here

I am using bootstrap 4, I want to show each category as a accordion title and list all products that belong to that category in the body of the accordion.

Using php 7

    function getProducts(){
        $db = new DB();
        $rows = $db->db_select("SELECT category.category_id, category.category_name, products.id, products.category_id, products.product_name
                FROM products 
                INNER JOIN category ON products.id = category.category_id 
                ORDER BY category.category_name 
                ");
        if($rows === false){
            $error = db_error();
        } else {
            return $rows;
        }
    }   

$data = new CORE();
$products = $data->getProducts();



<div class="accordion" id="accordionExample">
<? foreach($products as $p){ ?>
  <div class="card">
    <div class="card-header" id="headingOne">
      <h5 class="mb-0">
        <button class="btn btn-link" type="button" data-toggle="collapse" data-target="#collapseOne" aria-expanded="true" aria-controls="collapseOne">
          <? echo $p['category_name'] ?>
        </button>
      </h5>
    </div>

    <div id="collapseOne" class="collapse show" aria-labelledby="headingOne" data-parent="#accordionExample">
      <div class="card-body">
        <? echo $p['product_name'] ?>
      </div>
    </div>
  </div>
<? } ?>
</div>

Database Data:

3
phpmysqlforeach
can you show us your code or anything you have tried so far – Nipun Tharuksha
added code but not sure how to nest this to show each category and then the products under it – Jmostaind55
please show your database structure and some dummy data to provide a better answer – Nipun Tharuksha
Added database images – Jmostaind55
I can see you have received good answers for the question. So could you please mark a answer as the answer and your upvote also valuable for others too – Nipun Tharuksha

3 Answers

1
votes

you could change your query to something like this:

SELECT category.category,GROUP_CONCAT(products.id, '|', products.name ) AS product FROMcategoryINNER JOINproductsON category.category_id=products.category_id GROUP BY category.category


category product Hats 8|Snapback,9|Fitted Pants 6|Jogging,7|Cut offs Shirts 1|Polo,2|Long Sleeve,3|Vneck Shoes 5|Boots,4|Converse


foreachproductsplit/explodesplit/explode,split/explode|foreach
0
votes

First I call for two functions to fetch Categories and Products as below.

function getCategories(){
        $db = new DB();
        $rows = $db->db_select("SELECT category.category_id, category.category_name FROM Categories");
        if($rows === false){
            $error = db_error();
        } else {
            return $rows;
        }
    }   
    function getProducts(){
        $db = new DB();
        $rows = $db->db_select("SELECT products.id, products.category_id, products.product_name
                FROM products ");
        if($rows === false){
            $error = db_error();
        } else {
            return $rows;
        }
    }   

$data = new CORE();
$categories = $data->getCategories();
$products = $data->getProducts();

Then I recursively use these arrays inside foreach loops .The point is that in here I use two 1foreach` loops and first loop waits until second loop(inside) complete there process. 

//Take the first value of array and then second value and so on.

     foreach($Categories as $Category){

     echo $Category['name'];

//Take the first value of array and then second value and so on.

foreach($Products as $Product){

//Check the current category id with current product id. In here **category id dosent get changes untill products foreach loop completed**.

if( $Category['category_id'] == $Product['category_id'] ) {

  echo $Product['name'];
}
}
}
0
votes

Please try this query if get products retaed to categories

$this->db->join('products','products.category_id = categories.category_id')
->order_by('categories.category_id')->get('categories')->result_array();