CODEWITHSUNDEEP
phpmysqlsqllistwhile-loop
Erik Nucibella
Erik Nucibella

Reputation: 121

How to put in a <select> categories and Subcategories from a DB?

i really want to do something like this http://www.w3schools.com/tags/tryit.asp?filename=tryhtml_optgroup but i have The main Categories and the subcategories in a DB, and i don't really know how to manage this in a while cycle. How can i do this ? Actually the cycle is something like that, but because of my necessities i can't use it anymore

<select  class="form-control" id="subcategory" name="subcategory" required>
<?php
   $categories="SELECT Name,Category FROM SubCategory ORDER BY Category ASC, Name ASC;";
   $query_categories=mysqli_query($connh,$categories);

      while($rows_categories=mysqli_fetch_array($query_categories)){

echo'<option>'.$rows_categories['Category'].'/'.$rows_categories['Name'].'</option>';  
                                                                    } 
?>
</select>

p.s: Category is the main Category, Name is the subcategory

I want to have the main category into <optgroup> but because of one Category contains more than 1 subcategory, I don't know how to place <optgroup> in a way that it shows the main category just 1 time and not for each record.

Upvotes: 0

Views: 5006

Answers (5)

Passionate Coder
Passionate Coder

Reputation: 7294

<?php
$conn = mysqli_connect($mysql_hostname, $mysql_user, $mysql_password,$mysql_database) or die("Could not connect database");
$result = mysqli_query($conn,"select distinct Category from SubCategory");
echo '<select  class="form-control" id="subcategory" name="subcategory" required>';
while($row = mysqli_fetch_assoc($result)){  
    echo '<optgroup label="'. $row['Category'] .'">';
     $data =  mysqli_query($conn,"SELECT Name,Category FROM SubCategory where Category = '".$row['Category']."'");
while($value = mysqli_fetch_assoc($data)){  
    echo '<option>'. $value['Name'] .'</option>';
}
 echo '</optgroup>';
}
echo '</select>';
?>

Upvotes: 0

Tim Sparks
Tim Sparks

Reputation: 71

Create an array with your main categories, with subcategories as a sub-array.

<select  class="form-control" id="subcategory" name="subcategory" required>
<?php
$option_list = array();

$categories="SELECT Name,Category FROM SubCategory ORDER BY Category ASC, Name ASC;";
$query_categories=mysqli_query($connh,$categories);

while($rows_categories=mysqli_fetch_array($query_categories)){  
    $option_list[$rows_categories['Category']][] = $rows_categories['Name'];
}

// this is what we're aiming for
// $option_list = array("Maincat1"=>array("subcat1","subcat2","subcat3"),"Maincat2"=>array("subcat4","subcat5"));


foreach($option_list as $maincat=>$subcats){
    echo "<optgroup label='".$maincat."'>";
    foreach($subcats as $subcat){
        echo "<option value='$subcat'>$subcat</option>";
    }
    echo "</optgroup>";
}
?>
</select>

Upvotes: 0

Kaja Mydeen
Kaja Mydeen

Reputation: 585

<select  class="form-control" id="subcategory" name="subcategory" required>
<?php
$categories="SELECT Name,Category FROM SubCategory ORDER BY Category ASC, Name ASC;";
$query_categories=mysqli_query($connh,$categories);
$category_name_old = '';
while($rows_categories=mysqli_fetch_array($query_categories)){

    if($rows_categories['Category'] != $category_name_old) {
        echo "<optgroup label=".$rows_categories['Category'].">";
        echo "<option>".$rows_categories['Name']."</option>";
    }
    else {
        echo "<option>".$rows_categories['Name']."</option>";
    }
    $category_name_old = $rows_categories['Category'];
    if($category != $category_name_old) {
        echo "</optgroup>";
    }
}  
                                                                    } 
?>
</select>

You can do like this

Upvotes: 0

Vin&#237;cius Medeiros
Vin&#237;cius Medeiros

Reputation: 2989

In your case, need flag if current main category has added on your html, if I understand fields in your database, Name and Category has string values and you don't need two queries or two whiles but only check variable, like this:

<select  class="form-control" id="subcategory" name="subcategory" required>
<?php
$categories="SELECT Name,Category FROM SubCategory ORDER BY Category ASC, Name ASC;";
$query_categories=mysqli_query($connh,$categories);

$currentMainCategory = null;
while($rows_categories=mysqli_fetch_array($query_categories)){
    // check if your current category was diferent of row category
    if($currentMainCategory != $rows_categories['Category']) {
         // check if is not null (for close </optgroup>)
         if(!is_null($currentMainCategory)) {
              echo '</optgroup>';
         }
         // set your new current category for this loop
         $currentMainCategory = $rows_categories['Category'];
         echo '<optgroup label="'. $rows_categories['Category'] .'">';
    }
    // do not forget put value of this category if you needed
    echo'<option>' . $rows_categories['Name'] . '</option>';  
}

// then check again for close last optgroup if is opened
if(!is_null($currentMainCategory)) {
   echo '</optgroup>';
}
?>
</select>

Upvotes: 1

Tim Selaty Jr.
Tim Selaty Jr.

Reputation: 598

You're not too far off from accomplishing your basic goal, which is to display the categories and sub-categories in an optgroup.

You can either:

  1. Query MySQL with two separate queries and combine them using PHP against both the SubCategory and Category tables.

  2. Perform two foreach loops, each containing an optgroup for the respective Category and SubCategory results.

or

  1. Query MySQL with one query using OUTER JOIN to retrieve one result set against both the SubCategory and Category tables (possibly even a UNION).
  2. Perform two foreach loops, each containing an optgroup for the respective Category and SubCategory results, but only display the Category results if it came from the Category table and vice versa.

Here's some reference material:

MySQL Joins: https://www.sitepoint.com/understanding-sql-joins-mysql-database/

MySQL Union: Combining the results of two separate MySQL queries

PHP Foreach loop: http://php.net/manual/en/control-structures.foreach.php

PHP Array Merging: http://www.w3schools.com/php/func_array_merge.asp

Upvotes: 0

Related Questions