PHP: print sub category item group by mother catergory

Question

I must to create a menù that contain a list of subcategory items group by mother category and take these values from product table:

Category

SubCategory

Category

SubCategory

I got this:

<?php
 $query_cat = $db->prepare("SELECT * FROM products GROUP BY category ORDER BY category");
 $query_cat->execute();

 print '<li><strong>'. $category . '</strong></li>'; //Category

 while ($cat_row = $query_cat->fetch()) {  //Subcategory
       print '<li><a href="/category/'. $cat_row['4'] .'">'. $cat_row['4'] .'</a></li>'; 
 }
 ?>

The products table structure is

ID, name, stars, brand, category, priceavg, first-category

How i can get the category name and put in top of the element?

Thanks to all for the help provided.

Benissimo, dunque, la tabella è composta da ID, name, stars, brand, category, priceavg, first-category — andreaem, Feb 13 '16 at 13:40
super category = category and sub category = first-category ??? — Maxi Schvindt, Feb 13 '16 at 13:41
Normally, if you use category and subcategory, exist a table Category and a category has category father(referenced to other category) if is a subcategory. — Maxi Schvindt, Feb 13 '16 at 13:47
@Cuchu sorry for the missing explaination: super category = first-category, sub-category = category — andreaem, Feb 13 '16 at 13:56
you may use GROUP_CONCAT for getting the subcategory as a single string, and then explode them for viewing the as subcategory. — Murad Hasan, Feb 13 '16 at 14:36

score 2 · Accepted Answer · edited May 23 '17 at 12:07

2

You can use array_group_by function as below

$query_cat = $db->prepare("SELECT name, category, first_category FROM products ORDER BY category");
$query_cat->execute();

$rows = $query_cat->fetchAll(PDO::FETCH_ASSOC);
$items = array_group_by($rows, 'first_category');

foreach($items as $key => $value) {
    echo '<li><strong>' . $key . '</strong></li>';
    foreach($value as $row) {
        echo '<li><a href="/category/'.$row['category'].'">'.$row['category'].'</a></li>';
    }
}

The array_group_by is a function that groups an array by a key or set of keys shared between all array members. Once you've retrieved your SQL data as an associative array, you can apply the array_group_by function an obtain an array of array such as categories and subcategories.

After the array_group_by this is a sample result of how your $items array is structured https://3v4l.org/5WkDH

Final live example here https://3v4l.org/cg9NN

Tip Why is SELECT * considered harmful? Consider to not use SELECT * FROM in your queries

EDIT If you want compatibility to PHP lower than 5.3, you just have to change [] with array() short array notation. Live example here https://3v4l.org/0VUn7 (tested from PHP 4.3 to latest)

edited May 23 '17 at 12:07

Community

1
1

answered Feb 13 '16 at 14:13

grazie, fetch_assoc() dovrebbe essere fetch(PDO::FETCH_ASSOC); giusto? Ottengo errore fatale Call to undefined function array_group_by() – andreaem Feb 13 '16 at 14:19
yes, you must retrieve SQL results as associative array – Feb 13 '16 at 14:20
the `array_group_by` function must be added as a library. Look at https://github.com/jakezatecky/array_group_by – Feb 13 '16 at 14:20
I didn't know if you were using `mysqli` or `PDO`. I edited the `fetch_assoc`in order to get it right for PDO – Feb 13 '16 at 14:23
I got Warning: Illegal string offset 'category' in /lib/array_group_by.php on line 24, instead the list got only initial letter of the array and some numbers where print surely related to the other table content – andreaem Feb 13 '16 at 14:45
Nothing, i got Warning: Illegal string offset 'first_category' ... 1 Warning: Illegal string offset 'category' ... I ... 0 ... A ... etc – andreaem Feb 13 '16 at 15:11
Your code won't work on PHP 5.3 and lower, which might be the version that is used by the O.P.. Sometimes, updating version is impossible (legacy code, for example). All you have to do is change every single time you create an array to use `array()` instead. Which is like .... 2... – Ismael Miguel Feb 13 '16 at 15:12
I'm using PHP Version 5.5.9-1ubuntu4.13 and plan to use this version in production. can you explain more about change every single time? – andreaem Feb 13 '16 at 15:17
Working using fetchAll(PDO::FETCH_ASSOC) instead of fetch(PDO:FETCH_ASSOC); – andreaem Feb 13 '16 at 15:29
@andreaem This answer assumes you use PHP 5.4 or newer. The provided function breaks on PHP 5.3 and lower, due to a change in the syntax to create an array. Using the old syntax, will work in PHP 4 and up. – Ismael Miguel Feb 13 '16 at 15:32

PHP: print sub category item group by mother catergory

1 Answers1