How to create a dropdown list containing the categories in the database?

Hey… I created a file to add a category, and a file to add a subcategory. In this file, I want to create a drop-down list that includes (categories in the database), I have created a database called “store” - contains: the category table for categories contains two fields, one for identifier and the other for the category name, and a table “subcat” for subcategories contains On 3 fields … ID field, subcat_nm field for subcategory, parent_id field for class ID that is, >> cat_id = parent_id

`CREATE TABLE `category` (
  `cat_id` int(4) NOT NULL AUTO_INCREMENT,
  `cat_nm` varchar(30) NOT NULL,
  PRIMARY KEY (`cat_id`)
);


CREATE TABLE `subcat` (
  `subcat_id` int(4) NOT NULL AUTO_INCREMENT,
  `parent_id` int(4) NOT NULL,
  `subcat_nm` varchar(35) NOT NULL,
  PRIMARY KEY (`subcat_id`)
);`

config.php

<?php
error_reporting( E_ALL & ~E_DEPRECATED & ~E_NOTICE );
ob_start();
session_start();

define('DB_DRIVER', 'mysql');
define('DB_SERVER', 'localhost');
define('DB_SERVER_USERNAME', 'root');
define('DB_SERVER_PASSWORD', '');
define('DB_DATABASE', 'store');


define('PROJECT_NAME', 'test');
$dboptions = array(
              PDO::ATTR_PERSISTENT => FALSE, 
              PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, 
              PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
              PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
            );

try {
  $DB = new PDO(DB_DRIVER.':host='.DB_SERVER.';dbname='.DB_DATABASE, DB_SERVER_USERNAME, DB_SERVER_PASSWORD , $dboptions);  
} catch (Exception $ex) {
  echo $ex->getMessage();
  die;
}

//get error/success messages
if ($_SESSION["errorType"] != "" && $_SESSION["errorMsg"] != "" ) {
    $ERROR_TYPE = $_SESSION["errorType"];
    $ERROR_MSG = $_SESSION["errorMsg"];
    $_SESSION["errorType"] = "";
    $_SESSION["errorMsg"] = "";
}
?>

These are codes for adding a subcategory
The file addsubcategory.php

<? php
require_once './config.php';
?>
  <div class = "row">
    <div class = "panel panel-primary">
      <div class = "panel-body">
        <form class = "form-horizontal" name = "contact_form" id = "contact_form" enctype = "multipart / form-data" method = "post" action = "process_subcategory.php">
          <input type = "hidden" name = "mode" value = "<? php echo ($ _GET [" m "] ==" update ")?" update_old ":" add_new ";?>">>
          <input type = "hidden" name = "subcat_id" value = "<? php echo intval ($ results [0] [" subcat_id "]);?>">
          <fieldset>
<div class = "form-group">
<label class = "col-lg-4 control-label" for = "subcat_nm"> Choose category </label>
              <div class = "col-lg-5">
<? php
        // Here I want to create a dropdown list containing the categories from the database
           ?>
              </div>
            </div>
<br> <p>
            <div class = "form-group">
              <label class = "col-lg-4 control-label" for = "subcat_nm"> Add subcategory </label>
              <div class = "col-lg-5">
                <input type = "text" value = "<? php echo $ results [0] [" subcat_nm "]?>" placeholder = "" id = "subcat_nm" class = "form-control" name = "subcat_nm" required = "required">
              </div>
            </div>
            <div class = "form-group">
              <div class = "col-lg-5 col-lg-offset-4">
                <button class = "btn btn-primary" type = "submit"> Submit </button>
              </div>
            </div>
          </fieldset>
        </form>
      </div>
    </div>
  </div>

process_subcategory.php file

<? php
require './config.php';
$ mode = $ _REQUEST ["mode"];
if ($ mode == "add_new") {
  $ parent_id = trim ($ _ POST ['parent_id']);
  $ subcat_nm = trim ($ _ POST ['subcat_nm']);
  $ error = FALSE;
  if (! $ error) {
    $ sql = "INSERT INTO` subcat` (`parent_id`,` subcat_nm`) VALUES ". "(: parent_id,: subcat_nm)";
    try {
      $ stmt = $ DB-> prepare ($ sql);
      // bind the values
$ stmt-> bindValue (": parent_id", $ parent_id);
      $ stmt-> bindValue (": subcat_nm", $ subcat_nm);
      // execute Query
      $ stmt-> execute ();
      $ result = $ stmt-> rowCount ();
      if ($ result> 0) {
        $ _SESSION ["errorType"] = "success";
        $ _SESSION ["errorMsg"] = "Added successful.";
      } else {
        $ _SESSION ["errorType"] = "danger";
        $ _SESSION ["errorMsg"] = "The addition failed.";
      }
    } catch (Exception $ ex) {
      $ _SESSION ["errorType"] = "danger";
      $ _SESSION ["errorMsg"] = $ ex-> getMessage ();
    }  }
  header ("location: addsubcategory.php");
}
?>

Please Helppppp

Your code is never going to work with all those spaces in the variables

1 Like

My first guess was a text editor with some kind of autocorrect rules was “fixing”. But maybe the Uni added some control characters to poke copy paste? Anyway, it’s easier to read this 8 month old commit I found

I don’t see how “add” code and a dropdown are connected unless it is being used in a way I’ve never seen used before. They’ve always been read, i.e. SELECT not INSERT

Is there any need for separate category and sub-category tables? I wonder whether it might be easier to just have a single table, and if there is no parent-category id, then it’s a category, otherwise it’s a sub-category.

In addsubcategory.php, where does your array $results come from? I don’t see that you execute a query anywhere to have a set of results.

As for the question, all you need to do is execute a SELECT query to get the list of categories, retrieve the results, and wrap them in the appropriate html, with the id as the option value for each category.

Good question. The answer is no. Technically, there is no such thing as sub categories. They are all categories. Some are parents, some are children, some are both. One single table is all that is needed.

Here is one article that describes the two methods to do it.
http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

I recently came across a nice slide deck that compares multiple models of how to hierarchical data in MySQL. I’d never heard about closure tables before, but they appear to be a really nice solution to this problem.

See https://www.slideshare.net/mobile/billkarwin/models-for-hierarchical-data

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.