CODEWITHSUNDEEP
phpmysqlforeach
Jmostaind55
Jmostaind55

Reputation: 33

Nested foreach loop cateogories and products

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:

Upvotes: 0

Views: 1281

Answers (3)

PHP Geek
PHP Geek

Reputation: 4033

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();

Upvotes: 0

Jou M.
Jou M.

Reputation: 21

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

it will give you a result like:


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


then all you have to do is the foreach on the product array to split/explode the data and display it. First split/explode by comma , then each result split/explode by the pipe |. You will still 2 foreach but with one SQL query.

Upvotes: 1

Nipun Tharuksha
Nipun Tharuksha

Reputation: 2567

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'];
}
}
}

Upvotes: 0

Related Questions