Returning a string/value multiple times

[Synestic]

Hi Daniweb!

I have a question regarding my e-commerce website. On my product overview, I have this code:

<?php
                $replace_what = array('/','  ', ' - ', ' ',    ', ', ',');
                $replace_with = array('-',' ', '-', '-', ',', '-');
//Start of (1)
                $mysqli = new mysqli(***);
                if (mysqli_connect_errno()) {
                printf("Connect failed: %s\n", mysqli_connect_error());
                exit();
                }

                $type = (string) $_GET["type"];
                echo'<div id="li_type">'.$type.'</div>'; //This is just the type of the part at the top of the page

                if ($stmt = $mysqli->prepare("SELECT id,name,type,sub,description FROM products WHERE type=? ORDER BY sub DESC")) {
                $stmt->bind_param("s", $type);
                $stmt->execute();
                $stmt->bind_result($id,$name,$type,$sub,$description);
                $stmt->fetch();
//Start of (2)      
                $oldsub = $sub;
                $linkname = str_replace($replace_what, $replace_with, $name);
                echo'
                <div class="li_sub">'.$oldsub.'</div> 
                <div class="li_nav">
                    <ul>
                    <li><a href="Link to the product page linked to this $id">'.$name.'</a></li>';
//Start of (3)                              
                while ($row = $stmt->fetch()) {

                    $linkname = str_replace($replace_what, $replace_with, $name);
                    if ($sub == $oldsub) {
                    echo'
                    <li><a href="Link to the product page linked to this $id">'.$name.'</a></li>';
                    } else {

                        echo'</ul></div>

                        <div class="li_sub">'.$sub.'</div>
                        <div class="li_nav">
                        <ul>
                        <li><a href="Link to the product page linked to this $id">'.$name.'</a></li>';

                    }

                    $oldsub = $sub;



                }
                echo'</ul></div>';
                /* close statement */
                $stmt->close();
                }
        ?>

What this does is
1 - Retrieve all data
2 - Create the first product entry and define "$oldsub"
3 - Loop through all results.
$oldsub will be set at the end of each cycle of the loop to $sub so next loop it can check whether the $sub (or product subcategory) has changed or not. If it has, It'll create a new subcategory, if it hasn't it'll only output the $name and a link to the page of the ID

This will output something like:

Typenr1

*Subcategory 1 (this is $sub)
    Product 1 (this is the $name, pressing the url will bring you to mysite.com/$id)
    Product 2
    Product 3
*Subcategory 2 (This is when $oldsub != $sub)
    Product 4
*Subcategory 3
    Product 5
    Product 6

My MySQL database looks like this :

    Name        ID      Type        Sub
    Product 1   1       Typenr1     Subcategory 1
    Product 2   2       Typenr1     Subcategory 1
    Product 3   3       Typenr1     Subcategory 1
    Product 4   4       Typenr1     Subcategory 2
    Product 5   5       Typenr1     Subcategory 3
    Product 6   6       Typenr1     Subcategory 3

Now you know how my site works (I hope), I have 2 questions

1 - Is this a good way of doing this ?

2 - Some of my products need to be in multiple subcategories. An example:

Typenr1

*Subcategory 1 (this is $sub)
    Product 1 (this is the $name, pressing the url will bring you to mysite.com/$id)
    Product 2
    Product 3
    product 6
*Subcategory 2 (This is when $oldsub != $sub)
    Product 4
    Product 1
*Subcategory 3
    Product 1
    Product 5
    Product 6

I found this to be impossible without creating multiple rows for the same product ID, but with a different SUB.
Is it possible to have one row per ID but define multiple SUBs in one row ? eg.

        Name        ID      Type        Sub
        Product 1   1       Typenr1     Subcategory 1;Subcategory 2;Subcategory 3
        Product 2   2       Typenr1     Subcategory 1
        Product 3   3       Typenr1     Subcategory 1
        Product 4   4       Typenr1     Subcategory 2
        Product 5   5       Typenr1     Subcategory 3
        Product 6   6       Typenr1     Subcategory 3;Subcategory 1

I hope the length of this post didn't scare anyone to help me..

Thanks in advance!
Brad

[blocblue]

Hi Brad,

In answers to your questions:

1. Yes, it's fine to do it that way.

2. If you're consistent in the way you delimit the subcategories in your database field, e.g. using a semi-colon, then there's no reason why you couldn't use a single row. I would be tempted to use an array to group your products by subcategory before outputting the overview though.

E.g.

$grouped_products = array();

// Iterate through product results
while ($product = $stmt->fetch()) 
{
    // Split sub-categories on ;
    $sub_categories = explode('; ', $product['sub']);

    // Append product to array indexed by sub-category
    foreach($sub_categories as $sub_category)
        $grouped_products[$sub_category][] = $product;
}

// Iterate through grouped products
foreach($grouped_products as $sub_category => $products): ?>
    <div class="li_sub"><?php echo $sub_category; ?></div>
    <ul>

    <?php foreach($products as $product): ?>

        <li><?php echo $product['name']; ?></li>

    <?php endforeach; ?>

    </ul>
<?php endforeach;