Create a drop-down list that options fetched from a MySQL database in PHP

In many scenarios, we may need to create a dropdown input that can display all the options consistent with the current state of the database. This form of input is used many times in real life and the following examples may help to understand the same.

1. A set of students who have unique registration numbers.
2. A set of branch names and their branch ids.
3. A list of categories to which a particular product must belong.

In this article, we will create a drop-down with a list of categories to which a particular product must belong.

Approach: In each of these examples, if we use a drop-down menu that fetches data from the database the user will be able to enter data more accurately and the UI will be more user-friendly. 

We need the following

• A database with a table of categories and another table of products with a foreign key to the category ID to which the particular product belongs.
• HTML form that accepts the data.

Steps:

Database creation:

• Switch on Apache and MySQL from the XAMPP control panel.

Click on “Start” buttons

• Create a database “example_store” by clicking on the new button.

CLick on the “new” button to make a new database

• Enter the database name and click “Create”.

Create a new database with name “example_store”

• Click on the SQL tab and paste the following code and click “GO”.

To run SQL and prepare the database

MySQL queries:

-- Table structure for table `category`
CREATE TABLE `category` (

 `Category_ID` int(11) NOT NULL,

 `Category_Name` varchar(255) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Dumping data for table `category`
INSERT INTO `category` (`Category_ID`, `Category_Name`) VALUES

(1, 'Category A '),

(2, 'Category B');

-- Table structure for table `product`
CREATE TABLE `product` (

 `Product_ID` int(11) NOT NULL,

 `product_name` varchar(255) NOT NULL,

 `category_id` int(11) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Dumping data for table `product`
INSERT INTO `product` (`Product_ID`, `product_name`, `category_id`) VALUES

(1, 'Product A1', 1),

(2, 'Product A2', 1),

(3, 'Product B1', 2);


-- Primary Key Constraints
ALTER TABLE `category`

 ADD PRIMARY KEY (`Category_ID`);

ALTER TABLE `product`

 ADD PRIMARY KEY (`Product_ID`),

 ADD KEY `Category_constraint` (`category_id`);
 
 
 
-- AUTO_INCREMENT for table `category`
ALTER TABLE `category`

 MODIFY `Category_ID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

-- AUTO_INCREMENT for table `product`

ALTER TABLE `product`

 MODIFY `Product_ID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

-- Foreign Key Constraints

ALTER TABLE `product`

 ADD CONSTRAINT `Category_constraint` FOREIGN KEY (`category_id`) 
 REFERENCES `category` (`Category_ID`) ON DELETE 
 CASCADE ON UPDATE CASCADE;

Example: We create a PHP file in a folder called “example_store” in htdocs and create the following form.php webpage which can be accessed in a browser at “localhost/example_store/form.php”.

PHP

<?php

    // Connect to database
    $con = mysqli_connect("localhost","root","","example_store");

    // mysqli_connect("servername","username","password","database_name")

    // Get all the categories from category table
    $sql = "SELECT * FROM `category`";
    $all_categories = mysqli_query($con,$sql);

    // The following code checks if the submit button is clicked
    // and inserts the data in the database accordingly
    if(isset($_POST['submit']))
    {
        // Store the Product name in a "name" variable
        $name = mysqli_real_escape_string($con,$_POST['Product_name']);

        // Store the Category ID in a "id" variable
        $id = mysqli_real_escape_string($con,$_POST['Category']);

        // Creating an insert query using SQL syntax and
        // storing it in a variable.
        $sql_insert =
        "INSERT INTO `product`(`product_name`, `category_id`)
            VALUES ('$name','$id')";

          // The following code attempts to execute the SQL query
          // if the query executes with no errors
          // a javascript alert message is displayed
          // which says the data is inserted successfully
          if(mysqli_query($con,$sql_insert))
        {
            echo '<script>alert("Product added successfully")</script>';
        }
    }
?>


<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport"
          content="width=device-width, initial-scale=1.0">
</head>
<body>
    <form method="POST">
        <label>Name of Product:</label>
        <input type="text" name="Product_name" required><br>
        <label>Select a Category</label>
        <select name="Category">
            <?php
                // use a while loop to fetch data
                // from the $all_categories variable
                // and individually display as an option
                while ($category = mysqli_fetch_array(
                        $all_categories,MYSQLI_ASSOC)):;
            ?>
                <option value="<?php echo $category["Category_ID"];
                    // The value we usually set is the primary key
                ?>">
                    <?php echo $category["Category_Name"];
                        // To show the category name to the user
                    ?>
                </option>
            <?php
                endwhile;
                // While loop must be terminated
            ?>
        </select>
        <br>
        <input type="submit" value="submit" name="submit">
    </form>
    <br>
</body>
</html>

Output:

• The drop-down currently shows only Category A and Category B.If we add few more Categories in the Database, we get them displayed on the dropdown. After inserting more values in Table category.

On inserting Category C and Category D 

• On reloading webpage

REloading the webpage

• Inserting a new Product: We can insert a product C1 in the following manner.

Insert a new product

• We get an alert message and the table product gets updated

After submitting the form