CODEWITHSUNDEEP
phpmysqlselect
user3354780
user3354780

Reputation: 21

Subcategories not displaying in select menu

I have been messing around with subcategories and created a database new categories for testing purposes.

Table newcategories

category_id (int) AUTO
name (varchar) NOT NULL
parent (int) NULL
category_desc (text) NULL
sort_order (int) NOT NULL

I entered some categories and subcategories into the db but I have been having issues when trying to display main categories with corresponding sub categories in a select menu.

<?php
    // // // query database to return all existing main categories
    $selectMainCat='SELECT * 
                    FROM newcategories 
                    WHERE parent is NULL 
                    ORDER BY sort_order ASC';

    $smc=$conn->query($selectMainCat);
    while($rowsmc = $smc->fetch_assoc()) { 
        $parent_id = $rowsmc['category_id']; 
?>

        <optgroup label="<?php echo "{$rowsmc['name']}";?>">
        <option value="<?php echo"{$rowsmc['category_id']}"; ?>" 
<?php 
        if (isset($catID) && $catID=="{$rowsmc['category_id']}") 
            echo "selected";
?>
>
<?php 
            echo "{$rowsmc['name']}";?></option>
<?php
    // // // query database to return all sub categories
            $selectSubCat='SELECT * 
                           FROM newcategories 
                           WHERE parent = "$parent_id" ';

            $ssc=$conn->query($selectSubCat);
            while($rowssc = $ssc->fetch_assoc()){ 
?>
                <option value="<?php echo"{$rowssc['category_id']}"; ?>" 
<?php 
                if (isset($catID) && $catID=="{$rowssc['category_id']}") 
                    echo "selected";
?>
>
<?php 
                     echo "{$rowssc['name']}";?></option>
<?php
                }
?>
</optgroup>
<?php
            }
?>

Below is what I have entered into the db

category_id     name        parent  category_desc   sort_order
1               Fruits      NULL    NULL             1
2               Vegetables  NULL    NULL             2
3               Apple       1       NULL             1
4               Arugula     2       NULL             1
5               Cabbage     2       NULL             2
6               Honeycrisp  3       NULL             1
7               Braeburn    3       NULL             2

HTML OUTPUT

<select class="form-control m-b" name="catID" id="catID">
  <option value="" disabled selected> Select Main Category</option>
  <option value="" disabled></option>
  <optgroup label="Fruits">
    <option value="1" >Fruits</option>
  </optgroup>
  <optgroup label="Vegetables">
    <option value="2" >Vegetables</option>
  </optgroup>
</select>

Upvotes: 0

Views: 38

Answers (1)

tadman
tadman

Reputation: 211560

The problem you have here is the value is not being interpolated correctly since you're using the non-interpolating quotes. This can be fixed by doing the query correctly using placeholder values:

$ssc = $conn->prepare('SELECT * FROM newcategories WHERE parent=?');
$ssc->bind_param('i', $parent_id);
$result = $ssc->execute();

while ($rowssc = $result->fetch_assoc()) { 
  ...
}

It's extremely risky to put data of any kind directly in a query, especially something that comes directly from the user via $_GET, $_POST or $_REQUEST. Even $_COOKIE and $_SESSION can be trouble since these may contain values previously supplied by a user, or which a user has some degree of control over.

Upvotes: 1

Related Questions