How to Create Drop down list in PHP with MySQL
In this article, we are explain How to Create drop down list in PHP with MySql from database. Here the records we get from the category table will be used to display the drop-down list. Then the options are dynamically added to the list box, giving us flexibility in choosing the options. Here we first fill the list with options taken directly from the table. Next, let’s limit the options by adding a WHERE clause to the SQL statement used to retrieve data from the table.
How to create Drop down list in PHP with MySql
First, here we are add a category into the category table and display data from database in drop down list using PHP. Fetch the category database from the category table then listing to the select option category name as option and category id as value in option tag.
See also Dynamic Dependent Drop down in PHP using jQuery AJAX
Create Database and Table by SQL query
Web need to create database and table, so here I create webscodex database and table. categories table holds the records which will be save in categories table and create drop down list options fetch data from database. You can simply create table as following SQL query.
-- Database: `webscodex`
-- --------------------------------------------------------
-- Table structure for table `categories`
--
CREATE TABLE `categories` (
`id` int(11) NOT NULL,
`category_name` varchar(50) NOT NULL,
`status` tinyint(4) NOT NULL,
`created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- Dumping data for table `categories`
INSERT INTO `categories` (`id`, `category_name`, `status`, `created_at`) VALUES
(1, 'Fashion', 1, '2023-07-31 23:56:49'),
(2, 'Mobiles', 1, '2023-07-31 23:56:49'),
(3, 'Electronics', 1, '2023-07-31 23:56:56'),
(4, 'Home & Furniture', 1, '2023-07-31 23:56:56'),
(5, 'Grocery', 1, '2023-07-31 23:58:39'),
(6, 'Appliances', 1, '2023-07-31 23:58:39'),
(7, 'Beauty & Toys', 1, '2023-07-31 23:58:57'),
(10, 'Movies & Music', 1, '2023-08-01 15:23:05'),
(11, 'Movies & Music', 0, '2023-08-01 15:23:16'),
(12, 'Movies & Music', 0, '2023-08-01 15:24:00');
-- Indexes for dumped tables
--
-- Indexes for table `categories`
--
ALTER TABLE `categories`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
-- AUTO_INCREMENT for table `categories`
--
ALTER TABLE `categories`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=13;
COMMIT;Create Database Configuration
In this step, we require to create database configuration file, here we will set database name, username and password. So let’s create config.php file on your project and put bellow code:
See also How to Send Email from localhost in PHP using SMTP
config.php
<?php
// Database configuration
$hostname = "localhost";
$username = "root";
$password = "";
$dbname = "webscodex";
// Create database connection
$con = new mysqli($hostname, $username, $password, $dbname);
// Check connection
if ($con->connect_error) {
die("Connection failed: " . $con->connect_error);
}
?>Create HTML Form for Add Category and Display Drop-down list
In this File, Create HTML form using Bootstrap a category input field and status checkbox for category status to insert into the database table and fetch the category data from the category table and display drop down list.
index.php
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>How to add and Display Drop down list in PHP with MySQL</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet">
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.bundle.min.js"></script>
</head>
<body>
<div class="container">
<div class="row">
<h1 class="text-success mt-5 mb-5 text-center">How to add and Display Drop down list in PHP with MySQL</h1>
<div class="col-md-6 offset-3">
<?php
if (!empty($_GET['message'])) {
echo "<div class='alert alert-success'>".$_GET['message']."</div>";
}
?>
<form action="action.php" method="post">
<div class="mb-3">
<label class="form-label" for="add_category">Add Category</label>
<input type="text" class="form-control" name="add_category" placeholder="Add Category" required />
</div>
<div class="mb-3">
<label class="form-label" for="status">Status</label>
<input type="checkbox" name="status" />
</div>
<input type="submit" name="save_category" class="btn btn-success mb-5" value="Add">
<div class="mb-3">
<label class="form-label" for="category">Select Category</label>
<select type="select" class="form-control" id="category" required>
<option>Select Category</option>
<?php
// Include Configuration File
include_once "config.php";
$query = "SELECT * FROM categories WHERE status = '1'";
$result = $con->query($query);
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
?>
<option value="<?php echo $row['id'] ?>"><?php echo $row['category_name'] ?></option>
<?php
}
}
?>
</select>
</div>
<input type="submit" name="submit" class="btn btn-primary mb-5" value="Submit">
</form>
</div>
</div>
</div>
</body>
</html>Insert Category Value In Table
action.php
In this File, Insert category value and status in category table. Check category is not empty checkbox value database.
<?php
// Include Configuration File
include_once "config.php";
if (isset($_POST['save_category']) && !empty($_POST['add_category'])) {
$category = $_POST['add_category'];
$status = !empty($_POST['status']) ? 1 : 0;
$query = "INSERT INTO `categories`(`category_name`, `status`) VALUES ('$category','$status')";
if ($con->query($query)) {
$message = "Category add successful";
header("Location: index.php?message=".$message."");
}else{
$message = "Category does not addedd please try again!";
header("Location: index.php?message=".$message."");
}
}
?>For more blog posts click on the below