How To Select2 Multiple Select Values From Database In PHP

Select2 is an very easy and simple Javascript library to implement Select multiple values from database. Select2 gives you a customizable select box with support for searching, tagging, remote data sets, infinite scrolling, and many other highly used options.

Table of Contents

Select2 Multiple Select with PHP

In this tutorial we will create a database connection file and an index file. In the database file, we create a database connection to store the two selected multiple choice values.

In the index file, we add a text input box to store values and a checkbox to select multiple values, and add a select2 Javascript file to create a multiple-selection auto-completion in PHP.

See also

Folder Structure

 // ProjectFolderName/
      |__ index.php
      |__ config.php
      |__ action.php

Create Database and Table by SQL query

We 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 PRIMARY KEY AUTO_INCREMENT,
  `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');

-- --------------------------------------------------------
-- Table structure for table `products`
--
CREATE TABLE `products` (
 `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `product_name` varchar(100) NOT NULL,
  `image` varchar(100) NOT NULL,
  `description` text NOT NULL,
  `price` int(50) NOT NULL,
  `status` tinyint(4) NOT NULL,
  `created` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
--
-- Dumping data for table `products`
--
INSERT INTO `products` (`id`, `product_name`, `image`, `description`, `price`, `status`, `created`) VALUES
(1, 'HP Windows 11 All-in-One 12th Gen', '41HPbhGPHzS.JPG', 'HP Windows 11 All-in-One 12th Gen Intel Core i3-27inches 68.6 cm 8GB RAM/512GB SSD/FHD, Micro-Edge, Anti-Glare Display/Wireless Keyboard & Mouse/Intel UHD Graphics/Win 11/MSO 2021, 27-cb1345in', 64, 1, '2023-07-29 16:01:10'),
(2, 'Canon Digital Camera', 'pexels-math-90946.JPG', 'Rishabh Home Decoration Elegant Clear Glass for Home Decor | Decorative Lighting | Lantern with Jute Handle (Clear, Large) (RHD-07 (2)) ', 59, 1, '2023-07-29 16:01:16'),
(3, 'HP Windows 11 All-in-One 12th Gen', 'pexels-math-90946.JPG', 'HP Windows 11 All-in-One 12th Gen Intel Core i3-27inches 68.6 cm 8GB RAM/512GB SSD/FHD, Micro-Edge, Anti-Glare Display/Wireless Keyboard & Mouse/Intel UHD Graphics/Win 11/MSO 2021, 27-cb1345in', 49, 1, '2023-07-29 16:01:19');

-- --------------------------------------------------------
--
-- Table structure for table `product_category_map`
CREATE TABLE `product_category_map` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `product_id` int(50) NOT NULL,
  `category_id` int(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `product_category_map`
--
INSERT INTO `product_category_map` (`id`, `product_id`, `category_id`) VALUES
(1, 1, 1),
(2, 1, 2),
(3, 1, 3),
(4, 1, 1),
(5, 1, 2),
(6, 1, 1),
(7, 1, 2),
(8, 1, 3),
(9, 2, 2),
(10, 2, 3),
(11, 2, 2),
(12, 2, 3),
(13, 2, 4);

Create Database Connection

In this step, we require to create database connection, here we will set database name, username and password. So let’s create config.php file on your project and put bellow code:

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); 
   }

?>

Bootstrap Multi Select2 Form Example

Import Bootstrap 5 to style the PHP autocomplete component, then import the select2 CSS file inside the HTML file’s head section.

Create a Form within the form tag, add an input text field, and a select box. with multi select values from category table and another simple select box with product dropdown fetch data from both table and displayed it.

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 select multiple values from database in PHP?</title>
   <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" />
   <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/select2@4.1.0-rc.0/dist/css/select2.min.css" />
</head>
<body>
   <div class="container">
      <div class="row">
         <h1 class="text-success mt-5 mb-5 text-center">How to Select Multiple values from database in PHP</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="category">Select Category</label>
                  <select type="select" name="category[]" class="form-control select2" id="category" multiple="multiple">
                     <?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>
               <div class="mb-3">
                  <label class="form-label" for="product">Product name</label>
                  <select type="select" name="product" class="form-control">
                     <option value="">Select Product</option>
                     <?php
                        $query = "SELECT * FROM products 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['product_name'] ?></option>
                              <?php
                           }
                        }
                     ?>
                  </select>
               </div>
               <input type="submit" name="submit" class="btn btn-primary mb-5" value="Submit">
            </form>
         </div>
      </div>
   </div>
</body>

<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.bundle.min.js"></script>
<script src="https://code.jquery.com/jquery-3.7.0.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/select2@4.1.0-rc.0/dist/js/select2.min.js"></script>
<script type="text/javascript">
   // In your Javascript (external .js resource or <script> tag)
   $(document).ready(function() {
      $('#category').select2();
   });
</script>
</html>

How to Select2 Multiple Select values from database in PHP

Save Multi Select2 Value in PHP

In this file, save category multi select value and product drop-down value in product category map table using PHP with mysqli

action.php

<?php
    
    // Include Configuration File
    include_once "config.php";

    if (isset($_POST['submit'])) {
        $categoryIds = $_POST['category'];
        $product = $_POST['product'];
        $error = false;

        foreach ($categoryIds as $categoryId) {
            $query = "INSERT INTO `product_category_map`(`product_id`, `category_id`) VALUES ('$product','$categoryId')";
            $con->query($query);
            $error = true;
        }
      
        if ($error) {
            $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."");
        }
    }
?>

Conclusion

In this article, I have explain the process of How to create Multi select2 Drop down list using PHP with mysqli. I have tried to very simple way to implement and display drop down list dynamically from database also save this value another table. You can easily extend the functionality according to your needs. To get all the necessary files, download the source code.

Download Source Code