Categorize & sub categorize from contents of one row and more execute even more queries in results I have a table called sales and it has below columns:

'id'
'product_type'
'soldDate'
'text'


'product_type' examples are: Tshirt-XL, Tshirt-L, Tshirt-M, Trousers-XL, Trousers-L, Trousers-M etc.

I have an array called products which is;

$products = array('Tshirt','Trausers');

My output is after executing below code:

Tshirt

Tshirt-L


lorem impsum dolat


Tshirt-XL


lorem impsum.
another text. ball.


Trousers


Trousers-M


text for this one.

And the code itself:

$products = array('Tshirt','Trousers');

foreach ($products as $product) {
    echo "&lt;b&gt;";
    echo $product;
    echo "&lt;/b&gt;&lt;br&gt;";

    $q = "
    SELECT      *,
    SUBSTRING_INDEX(product_type, '-', 1) AS product_typeMain
    FROM        sales
    WHERE       SUBSTRING_INDEX(product_type, '-', 1) = '".$product."'";
    $statement = $db-&gt;prepare($q);
    $results = $statement-&gt;execute();
    $results = $statement-&gt;fetchAll();


    $unique_product_types = array();

    foreach($results as $filter_result){
        if ( in_array($filter_result-&gt;product_type, $unique_product_types) ) {
            continue;
        }
        $unique_product_types[] = $filter_result-&gt;product_type;

        echo $filter_result-&gt;product_type;
        echo "&lt;br&gt;";

        $q = "
        SELECT      *
        FROM        sales
        WHERE       product_type = '".$filter_result-&gt;product_type."'
             ";
        $statement = $db-&gt;prepare($q);
        $results = $statement-&gt;execute();
        $results = $statement-&gt;fetchAll(PDO::FETCH_OBJ);

        foreach ($results as $value) {
            echo "&lt;li&gt;".$value-&gt;text."&lt;/li&gt;";
        }

    }

}


I know there are so many foreach loops, so many queries. Also I have worries about performance (more than 100.000 rows, and more than 100 product_type.) Plus I will need to add another query in last foreach loop to make searches (multiple, not one).

I am looking forward to hear best practices to improve. Thanks in advance.
PDO has some rather handy fetching options.  FETCH_GROUP is good for grouping the first level (product categories), but some extra handling is necessary to group the second level (product name-sizes).  To perform this nested grouping, I'll show the implementation of a temporary variable to track whether or not the current iteration is processing a new group or the same group as the previous iteration.


It is important to point out that you should not be making multiple trips to the database.  Since this script can be sensibly executed with a single trip to the database, it should be.
Prepared statements should be used for stability/security.
&lt;li&gt; tags must live inside &lt;ul&gt; tags for proper html markup.


Tested Code:

$products = ['Tshirt', 'Trousers'];
$placeholders = str_repeat('?,', count($products) - 1) . '?';

$stmt = $conn-&gt;prepare("
     SELECT SUBSTRING_INDEX(product_type, '-', 1) AS product_group, product_type, text 
     FROM sales
     WHERE SUBSTRING_INDEX(product_type, '-', 1) IN ($placeholders)
     ORDER BY product_type
     ");
$stmt-&gt;execute($products);
$results = $stmt-&gt;fetchAll(PDO::FETCH_GROUP);
foreach ($results as $productGroup =&gt; $subarray) {
    echo "&lt;div&gt;{$productGroup}&lt;/div&gt;";
    $typeGroup = null;
    foreach ($subarray as $row) {
        if ($row['product_type'] != $typeGroup) {
            if ($typeGroup) {
                echo "&lt;/ul&gt;&lt;/ul&gt;";
            }
            echo "&lt;ul&gt;&lt;li&gt;{$row['product_type']}&lt;/li&gt;&lt;ul&gt;";
        }
        echo "&lt;li&gt;{$row['text']}&lt;/li&gt;";
        $typeGroup = $row['product_type'];
    }
    echo "&lt;/ul&gt;&lt;/ul&gt;";
}


Sample data / Rendered output:



...yeah, didn't try very hard on the images.