php - Get categories list for every product same query - multidimensional array

Question

I have three tables.

1. products - with contains my products list (id, category_name, category_description, category_meta_title, category_meta_desc, category_sort, parent_id)
2. categories - with contains my categories list (id, product_name, product_description, product_meta_title, product_meta_desc, product_sort, aff_link, price, old_price, image, thumbnail, medium_image)
3. category_assign - who links products to categorie. (id, category_id, product_id)

I want get a list of all my products and also a second level array with all assigned categories for every products

What i've made:

My query:

protected function getProdFullList(){
            $sql = "
    SELECT p.id
         , p.product_name
         , p.product_meta_title
         , p.product_meta_desc
         , p.product_sort
         , p.aff_link
         , p.price
         , p.old_price
         , c.category_name 
      FROM products p
      JOIN category_assign pc
        ON p.id = pc.product_id 
      JOIN categories c
        ON c.id = pc.category_id 
     GROUP 
        BY p.id
         , c.id
    ";
            $stmt = $this->connect()->query($sql);
            $results = $stmt->fetchAll(PDO::FETCH_GROUP);
            return $results;
        }

Code to display my products list:

<?php foreach($rows as $products => $categs){

        echo '<tr>';
        echo '<td>'.$products.'</td>';
        echo '<td><a href="editproduct?edit='.$row['id'].'">'.$row['product_name'].'</a></td>';
        echo '<td>'.$row['price'].'</td>';
        echo '<td>'.$row['old_price'].'</td>';
        echo '<td>';
         foreach($categs as $categ){
        echo $categ['category_name'].' ';
      }
      echo '</td>';
        echo '<td><a id="simpleConfirm" href="products?remove='.$row['id'].'" class="btn btn-primary">Delete</a></td>';
    
    echo '</tr>';

            }

What i get:

So, i'm only able to get product id and categories list for every product.

Var_dump looks like

array(6) {
    [1] => array(1) {
        [0] => array(8) {
            ["product_name"] => string(9)
            "Product 1" ["product_meta_title"] => string(18)
            "Product test title" ["product_meta_desc"] => string(22)
            "Product test meta desc" ["product_sort"] => string(1)
            "0" ["aff_link"] => string(13)
            "test-link.com" ["price"] => string(3)
            "100" ["old_price"] => string(3)
            "101" ["category_name"] => string(10)
            "Category 2"
        }
    }[2] => array(1) {
        [0] => array(8) {
            ["product_name"] => string(9)
            "Product 2" ["product_meta_title"] => string(5)
            "dsfgg" ["product_meta_desc"] => string(1)
            "g" ["product_sort"] => string(2)
            "11" ["aff_link"] => NULL["price"] => string(2)
            "99" ["old_price"] => string(3)
            "100" ["category_name"] => string(10)
            "Category 2"
        }
    }[3] => array(1) {
        [0] => array(8) {
            ["product_name"] => string(9)
            "Product 3" ["product_meta_title"] => string(6)
            "sdadsa" ["product_meta_desc"] => string(6)
            "dsadsa" ["product_sort"] => string(1)
            "0" ["aff_link"] => NULL["price"] => string(3)
            "111" ["old_price"] => string(2)
            "11" ["category_name"] => string(10)
            "Category 2"
        }
    }[4] => array(5) {
        [0] => array(8) {
            ["product_name"] => string(9)
            "Product 4" ["product_meta_title"] => string(6)
            "sdadsa" ["product_meta_desc"] => string(6)
            "dsadsa" ["product_sort"] => string(1)
            "0" ["aff_link"] => NULL["price"] => string(3)
            "111" ["old_price"] => string(2)
            "11" ["category_name"] => string(10)
            "Category 1"
        }[1] => array(8) {
            ["product_name"] => string(9)
            "Product 4" ["product_meta_title"] => string(6)
            "sdadsa" ["product_meta_desc"] => string(6)
            "dsadsa" ["product_sort"] => string(1)
            "0" ["aff_link"] => NULL["price"] => string(3)
            "111" ["old_price"] => string(2)
            "11" ["category_name"] => string(10)
            "Category 2"
        }[2] => array(8) {
            ["product_name"] => string(9)
            "Product 4" ["product_meta_title"] => string(6)
            "sdadsa" ["product_meta_desc"] => string(6)
            "dsadsa" ["product_sort"] => string(1)
            "0" ["aff_link"] => NULL["price"] => string(3)
            "111" ["old_price"] => string(2)
            "11" ["category_name"] => string(10)
            "Category 3"
        }[3] => array(8) {
            ["product_name"] => string(9)
            "Product 4" ["product_meta_title"] => string(6)
            "sdadsa" ["product_meta_desc"] => string(6)
            "dsadsa" ["product_sort"] => string(1)
            "0" ["aff_link"] => NULL["price"] => string(3)
            "111" ["old_price"] => string(2)
            "11" ["category_name"] => string(10)
            "Category 4"
        }[4] => array(8) {
            ["product_name"] => string(9)
            "Product 4" ["product_meta_title"] => string(6)
            "sdadsa" ["product_meta_desc"] => string(6)
            "dsadsa" ["product_sort"] => string(1)
            "0" ["aff_link"] => NULL["price"] => string(3)
            "111" ["old_price"] => string(2)
            "11" ["category_name"] => string(10)
            "Category 5"
        }
    }[5] => array(1) {
        [0] => array(8) {
            ["product_name"] => string(9)
            "Product 5" ["product_meta_title"] => string(8)
            "sdadsa22" ["product_meta_desc"] => string(7)
            "dsadsa2" ["product_sort"] => string(1)
            "0" ["aff_link"] => NULL["price"] => string(3)
            "111" ["old_price"] => string(2)
            "11" ["category_name"] => string(10)
            "Category 1"
        }
    }[6] => array(2) {
        [0] => array(8) {
            ["product_name"] => string(9)
            "Product 6" ["product_meta_title"] => string(8)
            "sdadsa22" ["product_meta_desc"] => string(7)
            "dsadsa2" ["product_sort"] => string(1)
            "0" ["aff_link"] => NULL["price"] => string(3)
            "111" ["old_price"] => string(3)
            "111" ["category_name"] => string(10)
            "Category 1"
        }[1] => array(8) {
            ["product_name"] => string(9)
            "Product 6" ["product_meta_title"] => string(8)
            "sdadsa22" ["product_meta_desc"] => string(7)
            "dsadsa2" ["product_sort"] => string(1)
            "0" ["aff_link"] => NULL["price"] => string(3)
            "111" ["old_price"] => string(3)
            "111" ["category_name"] => string(10)
            "Category 4"
        }
    }
}

Instead getting product_name, product_description,etc... in every first level array, i got only product id. Also, I got them in every second array, where i supossed to get only the name of category.

question from:https://stackoverflow.com/questions/65641643/get-categories-list-for-every-product-same-query-multidimensional-array

Answer 1

Waitting for answers