How to use GROUP_CONCAT and EXPLODE function to display in checkbox?

Question

I'm new to PHP. So I have a few problems here:

So, this my "item_choice" table:

╔══════════╦═════════╦═══════════════╗
║  c_id    ║ item_id ║ choice_name   ║
╠══════════╬═════════╬═══════════════╣
║   1      ║ 100     ║   Set A       ║
║   2      ║ 100     ║   Set B       ║
║   3      ║ 100     ║   Set C       ║
║   4      ║ 100     ║   Set D       ║               
╚══════════╩═════════╩═══════════════╝

This is my "item_subchoice" table:

╔══════════╦═════════╦═══════════════╦═══════════════╗
║  sc_id   ║ c_id    ║    option     ║    price      ║
╠══════════╬═════════╬═══════════════╬═══════════════╣
║   1      ║   1     ║   Fries       ║      4        ║
║   2      ║   1     ║   Coleslaw    ║      4        ║
║   3      ║   1     ║ Mac n Cheese  ║      5        ║
║   4      ║   2     ║   Fries       ║      4        ║               
╚══════════╩═════════╩═══════════════╩═══════════════╝

So far this is my result (the square thingy is the checkbox):

Set A:
╔═╗  
╚═╝ Fries,Coleslaw,Mac n Cheese  4,4,5

What I'm trying to achieve:

Set A:
╔═╗  
╚═╝ Friese  4,
╔═╗  
╚═╝ Coleslaw  4
╔═╗  
╚═╝ Mac n Cheese 5

This is my current code:

<div class="form-row">

    <?php

    if (isset($_GET['itemid'])) {
        $choice_id = $_GET['itemid'];

        $get_choice_id = "SELECT
                            t1.*, 
                            GROUP_CONCAT(t2.sc_id) AS sc_ids,
                            GROUP_CONCAT(t2.option) AS choice_options,
                            GROUP_CONCAT(t2.price) AS choice_prices
                          FROM 
                            item_choice t1 
                          LEFT JOIN item_subchoice t2 ON t2.c_id = t1.c_id 
                          GROUP BY t1.c_id";

        $query = mysqli_query($con, $get_choice_id);

        foreach (($query) as $row) {
            $choice_id = $row['c_id'];
            $choice_name = $row['choice_name'];
            $subchoice_id = $row['sc_ids'];
            $subchoice_option = $row['choice_options'];
            $subchoice_price = $row['choice_prices'];

            echo"<div class='form-group col-md-12'>";
            echo"<hr style='height:1px;'>";
            echo"<label style='font-size:15px;'> Add Ons - $choice_name: </label>";

            echo"</div>";

            echo"<div class='form-group col-md-12'>";
            echo"<input type='checkbox' id='addon-checkbox' name='check_list[]' onclick='check(this)'";
            echo" <label value='$subchoice_id'>$subchoice_option +$subchoice_price</label>";

            echo"</div>";
        }
    }

    ?>
</div>

I've tried explode function and also array_merge too, but I can't get it to work. Thanks in advance!

Answer 1

You can probably just do a normal select, and ORDER BY the sets - and then check if the set has changed in PHP when looping over the results.

Also note that the addon-checkbox can't be an ID like that, since its not unique - there can be multiple rows, and the ID of an element must be unique.

$current_set = null;
$query = $con->query("SELECT ic.*, isc.sc_id, isc.option, isc.price
                        FROM item_choice AS ic
                        LEFT JOIN item_subchoice AS isc 
                            ON ic.c_id = isc.c_id
                        ORDER BY ic.choice_name");
while ($row = $query->fetch_assoc()) {
    if ($current_set != $row['choice_name']) {
        $current_set = $row['choice_name'];
        echo '<h1>'.$current_set.'</h1>';
    }
    ?>
    <div class="form-group col-md-12">
        <hr style="height: 1px;">
        <label style='font-size:15px;'> Add Ons - <?= $row['choice_name'] ?>: </label>
    </div>
    <div class="form-group col-md-12">
        <input type="checkbox addon-checkbox" name='check_list[]' onclick="check(this)" value="<?= $row['sc_ids'] ?>" />
        <label><?= $row['choice_options'] ?> + <?= $row['choice_prices'] ?></label>
    <?php
}