skip to Main Content

Grouping items by their category works but its not placing all the products of the category in one optgroup but repeating it.

All the items should be in one group not separate as an duplicate group.

Select options example

<select style="width: 25%;" name="item[]" id="item1" class="item browser-default custom-select-new"> 
    <option value="" disabled selected>Click to See Products</option>
<?php
$conn_register = mysqli_connect('localhost', 'root', '', 'register');
$Comid = "27";

$levy_input = "";
$records = mysqli_query($conn_register, "SELECT * FROM customer_product WHERE customer LIKE '$Comid'");
while ($data = mysqli_fetch_array($records)) {
    $price = $data['new_total_rate'];
    $product = $data['product'];

    $sqlii = "SELECT DISTINCT category 
                FROM addproducts 
                WHERE `name` LIKE '$product' 
                ORDER BY 'category' ";
    $resultii = $conn_register-> query($sqlii);
    $prof= $resultii -> fetch_assoc();
    $Pcategory = $prof["category"];

    echo '<optgroup label="'. $Pcategory .'">';
                            
    echo '<option value="' . $data['product'] . '"  
            data-new_price_rate="' . $data['new_total_rate'] . '" 
            data-description="' . $data['description'] . '" 
            data-tax="' . $data['tax'] . '"
            data-PriceRate_NoDiscount="' . $data['new_price_rate'] . '">'
        . $data['product'] . '</option>
    </optgroup>';     
}
?>
</select>

2

Answers


  1. Chosen as BEST ANSWER

    I have solved the problem, i joined the two query's with a left join and added a if statement to prevent repeating in the while loop.

     <?php
     $conn_register = mysqli_connect('localhost', 'root', '', 'register');
     $Comid = "28";
    
     $records = mysqli_query($conn_register, "SELECT customer_product_product AS name,
    category,new_total_rate,quantity,customer_product_tax,customer_product_description
     FROM customer_product AS cp
     JOIN addproducts AS ap ON customer_product_product = ap.name
     WHERE customer_product_customer = '$Comid'
     ORDER BY category ");
     ?>
    
     <select>
     <option selected disabled>select product</option>
     <?php
     $New_test_cat = "";
     while ($qqa = mysqli_fetch_array($records)) {
     $test_cat = $qqa['category'];
    
     if ($test_cat !== $New_test_cat) {
     $New_test_cat = $test_cat;
     echo '<optgroup label="'.$New_test_cat.'">';
     }
    
     echo '<option value="'.$qqa['name'].'" >'.$qqa['name'].' </option>';
     
     }
     echo '</optgroup>';
     ?>
     </select>
    

  2. Note that you unconditionally output an <optgroup> with each iteration; thus, if you don’t want to output each time through, you’ll need to (at the very least) make the output conditional. In many circumstances this wouldn’t be sufficient, as sequential rows may be in different groups; instead, you’d need to group the results in PHP, and then iterate over the results. Here, with a few alterations an ORDER BY clause will ensure the items in each category are processed sequentially. The current statements aren’t sufficient, as the categories are retrieved in a separate statement, which not only prevents the ORDER BY from making items in a category contiguous but is inefficient, as it makes many more requests than necessary.

    The two statements can be combined with a JOIN:

    SELECT cp.product AS name,
           cp.description,
           cp.tax,
           cp.new_price_rate,
           cp.new_total_rate,
           ap.category
      FROM customer_product AS cp
        LEFT JOIN addproducts AS ap ON cp.product = ap.name
      WHERE customer LIKE ? -- NB: parameter for prepared statement
      ORDER BY ap.category
    

    If there’s a 1:N relationship between customer_product.product and ap.name, then the query will need to be modified so as to only return one row for each customer_product row (a simple approach is to group by all the cp rows).

    To determine when to output an <optgroup>, the code will need to detect when the category changes. This is easily accomplished by storing the current category, and comparing it to the category for a new row.

    Generally speaking, you must consider what happens on the first, intermediate and last iterations.

    The first iteration is important in that you want to force the output of an optgroup. As long as the stored category will never equal a category from the DB, this is essentially automatic.

    Additionally, no </optgroup> close tag should be output on the first iteration. A simple way to skip this the first time through is to use a variable to hold the tag to output, which is initialized to just the open tag, and then add the close tag the first time an <optgroup> is output. Another is to have a flag recording whether there’s an optgroup to close (initialized to FALSE and set to TRUE when <optgroup> is output), and outputting the close tag only if the flag is true.

    After the last iteration, the last <optgroup> must be closed. Under the assumption that there’s at least 1 product in 1 category, you should be able to unconditionally output a close tag after the loop.

    Note that the sample code in the question mixes many different kinds of tasks, primarily DB access and output. This violates separation of concerns. Instead, each should be placed in separate modules. The exact methodology to achieve this is well beyond the scope of this Q&A, but a simplified method is used in the sample code below.

    Be sure to encode any non-HTML strings using htmlspecialchars, both to prevent injection and broken HTML.

    <?php
    // initially, there's no category
    $category = NULL;
    // the category tag; will be updated to close the previous element the first time it's output
    $catTag = '<optgroup ';
    // attributes for each <option> element
    $attrs = [
        'value' => 'name',
        'data-new_price_rate' => 'new_total_rate',
        'data-description' => 'description',
        'data-tax' => 'tax',
        'data-PriceRate_NoDiscount' => 'new_price_rate',
    ];
    ?>
    <select>
      <option disabled>Click to see products</option><!-- Note: this smells a bit. -->
      <?php
        // note there's no sign of DB access; the products could come from anywhere
        foreach ($customerProducts->fetch($comid) as $product) {
            // The core of the answer: output a new optgroup only then when the category changes
            if ($category != $product['category']) {
                echo $catTag, 'label="', htmlspecialchars($product['category']), "">n";
                // from now on, close the previous element when there's a new optgroup
                $catTag = "</optgroup>n<optgroup ";
            }
            // output the current product as an option element
            ?>
            <option<?php foreach ($attrs as $attr => $prop) {
                        echo ' ', $attr, '="', htmlspecialchars($product[$prop]), '"';
                    } ?>><?= htmlspecialchars($product['name']) ?></option>
            <?php
            $category = $product['category'];
        }
      ?>
      </optgroup>
    </select>
    

    The sample assumes that every product is in 1 category, so $product['category'] isn’t null. However, if any product isn’t in a category, it should still work unless every product in the results is in no category, in which case there will be no <optgroup>s and the final </optgroup> won’t close anything, producing invalid HTML.

    The following (untested) sample is only for the purposes of separating DB access from the HTML generation. Search elsewhere for information on topics such as DALs, prepared statements, and the Traversable interface (which is supported by mysqli_result, though PDO has better support, including allowing you to set the result type to something other than an associative array with PDOStatement->setAttribute).

    class CustomerProducts {
        static $statements = [
            'read' => 'SELECT […]',
        ];
        function __construct($db) {
            $this->db = $db;
            $this->read = $db->prepare(self::$statements[read]);
        }
        function fetch($id) {
            $this->read->bind_param('i', $id);
            if ($this->read->execute()) {
                return $this->read->get_result();
            } else {
                // handle failure with e.g. an exception
                throw …;
            }
        }
    }
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search