Categorize & sub categorize from contents of one row and more execute even more queries in results

Question

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 "<b>";
    echo $product;
    echo "</b><br>";

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


    $unique_product_types = array();

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

        echo $filter_result->product_type;
        echo "<br>";

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

        foreach ($results as $value) {
            echo "<li>".$value->text."</li>";
        }

    }

}

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.

Answer 1

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.
• <li> tags must live inside <ul> tags for proper html markup.

Tested Code:

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

$stmt = $conn->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->execute($products);
$results = $stmt->fetchAll(PDO::FETCH_GROUP);
foreach ($results as $productGroup => $subarray) {
    echo "<div>{$productGroup}</div>";
    $typeGroup = null;
    foreach ($subarray as $row) {
        if ($row['product_type'] != $typeGroup) {
            if ($typeGroup) {
                echo "</ul></ul>";
            }
            echo "<ul><li>{$row['product_type']}</li><ul>";
        }
        echo "<li>{$row['text']}</li>";
        $typeGroup = $row['product_type'];
    }
    echo "</ul></ul>";
}

Sample data / Rendered output:

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