Php update drop down list

starting with the form, there is no hidden attribute for <option ...> tags, so, that part is meaningless and should be removed. the 1st option choice value should be an empty string, which you do have (so that you can detect when no choice was made) and the 1st option text should be a prompt telling the user to select one of the available choices.

next, overall, what is the goal you are trying to achieve? - to edit/update existing data. to accomplish this, wouldn't you use the existing data to populate form fields/pre-select options? to do so, you would output the selected attribute in the correct <option ...> tag. the easiest way of doing this is to have a list of the choices in an array (either by storing the choices in a database table and retrieving them or defining an array in your php code), loop over the list, and dynamically produce the <option ...>...</option> html, with the selected attribute in the correct <option ...> tag. you should also use integers for the option values. integers are easier to validate, take up less storage, and will result in the fastest queries. this will also let you do things like make your application work with different user languages simply by changing the display text, not all the data storage.

then for your form processing code, you need to validate all input data before using it and setup and output descriptive validation errors for each possible thing that can be wrong with the submitted data. if you do this. your code will either work or it will tell you why it isn't working. also, once the post method form has been submitted, all form fields (except for un-checked checkbox/radio fields) will be set, so using isset() for these fields will hide coding mistakes and needlessly clutter up your code.

converting code to use prepared queries is easy, provided you are using the php PDO extension -

1) remove any php variables, any concatenation dots, and single-quotes around the values and replace each one with a ? place-holder.

2) call the ->prepare() method.

3) call the ->execute(...) method, supplying an array to it consisting of the variables you removed in step #1.

4) for queries that return a result set, fetch the data using a fetch method appropriate to the type of data.

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

    $con = mysqli_connect("localhost","root","","example_store");

    $sql = "SELECT * FROM `category`";

    $all_categories = mysqli_query($con,$sql);

    if(isset($_POST['submit']))

    {

        $name = mysqli_real_escape_string($con,$_POST['Product_name']);

        $id = mysqli_real_escape_string($con,$_POST['Category']);

        $sql_insert =

        "INSERT INTO `product`(`product_name`, `category_id`)

            VALUES ('$name','$id')";

          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

                while ($category = mysqli_fetch_array(

                        $all_categories,MYSQLI_ASSOC)):;

            ?>

                <option value="<?php echo $category["Category_ID"];

                ?>">

                    <?php echo $category["Category_Name"];

                    ?>

                </option>

            <?php

                endwhile;

            ?>

        </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

How do you make a drop down list change depending on selection in PHP?

How can I connect Dropdownlist with database in PHP?

How do I create a dynamic drop down list in HTML?

How can I fetch data After selecting from dropdown list in PHP?