Use a Multi-Select Dropdown in a PHP Form and Insert into MySQL

In this tutorial, you will learn how to use a multi-select dropdown in a PHP form and save the selected values into a MySQL database. Using a multi-select dropdown allows users to select multiple options, making your PHP forms more dynamic and user-friendly. By the end of this tutorial, you'll have a complete working example of multi-select form handling in PHP and MySQL.

For a multi-select dropdown, you have to handle more than one value from the list. So, you need to use an array of values and insert them into the database accordingly.

Select Dropdown


<select name="skill" id="skill">

Multi-Select Dropdown


<select name="skill[]" id="skill" multiple>

For multi-select dropdown, field name is an array - skill[] and "multiple" attribute is used.

Watch YouTube Video

In this tutorial, we will use a multiselect dropdown in a PHP form for the users to register their skills. The screenshot of the form is given below:

Form for multi-select dropdown in PHP

We'll walk through how to:

  1. Create a multi-select dropdown using HTML
  2. Handle multiple selected values in PHP
  3. Insert those values into a MySQL database

PHP Multi-Select Dropdown folder structure

We will use a folder called 'multiselect' under 'xampp/htdocs'. index.php is the form with a multi-select dropdown for skill registration and process_skills.php is to process the submitted form.

Create the Database Tables in MySQL

We will create three tables:

  1. skills
  2. candidates
  3. candidate_skills

Table structures are given below:

Table 1 - skills

This table is used to store all skills with id and name. This is a master table to show the list of available skills.

This table has 2 columns.

  1. id - Primary key
  2. skill_name - Skill name

The create table script along with some dummy data is given below: 


CREATE TABLE `skills` (
  `id` int(11) NOT NULL,
  `skill_name` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `skills` (`id`, `skill_name`) VALUES
(1, 'Java'),
(2, 'PHP'),
(3, 'C++'),
(4, 'Python'),
(5, 'Html and CSS'),
(6, 'MySQL'),
(7, 'Oracle'),
(8, 'JavaScript'),
(9, 'jQuery'),
(10, 'Wordpress'),
(11, 'Android');

ALTER TABLE `skills`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `skills`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12;

Table 2 - candidates

This table is used to store candidate details

This table has 4 columns.

  1. id - primary key and auto incremented
  2. name - name of the candidate
  3. email_id - email id of the candidate
  4. registration_dt - Date on which candidate was registered 


CREATE TABLE `candidates` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `email_id` varchar(200) NOT NULL,
  `registration_dt` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `candidates`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `email_id` (`email_id`);

ALTER TABLE `candidates`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

Table 3 - candidate_skills

This table is used to store skills for each candidate. When the user registers with their skills, all skills for the candidate are stored in this table.

This table has 3 columns.

  1. id - it is the primary key and auto incremented
  2. candidate_id - candidate id from the candidate table.
  3. skill_id - skill id of the skill for a candidate


CREATE TABLE `candidate_skills` (
  `id` int(11) NOT NULL,
  `candidate_id` int(11) NOT NULL,
  `skill_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `candidate_skills`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `candidate_skills`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

ALTER TABLE `candidate_skills`
  ADD CONSTRAINT `candidate_skills_ibfk_1` FOREIGN KEY (`candidate_id`) REFERENCES `candidates` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `candidate_skills_ibfk_2` FOREIGN KEY (`skill_id`) REFERENCES `skills` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

Note the foreign keys created.

Create a form for skill entry (multi-select) (index.php)

This is the skill registration form. The form has a multi-select input field along with name and email. The screenshot of the form is given below:

php form multiple select html form

We will first create a simple registration form with name and email. We will use Bootstrap 5 and a custom stylesheet. 


<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Skill Registration</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.7/dist/css/bootstrap.min.css" rel="stylesheet">
    <link rel="stylesheet" href="css/style.css">
</head>

<body>
  
    <div class="container">
        <h1>Register Your Skills</h1>
        <form class="form1" action="" method="post">
          
            <div class="mb-3">
                <label for="name" class="form-label">Name</label>
                <input
                    type="text"
                    class="form-control"
                    name="name"
                    id="name"
                    placeholder="Enter Your Name" />
                <div class="text-danger"></div>
            </div>

            <div class="mb-3">
                <label for="email" class="form-label">Email</label>
                <input
                    type="text"
                    class="form-control"
                    name="email"
                    id="email"
                    placeholder="Enter Your Email" />
                <div class="text-danger"></div>
            </div>
            
            <button
                type="submit"
                class="btn btn-primary"
                name="submit">
                Submit
            </button>

        </form>
    </div>
</body>

</html>

This is a simple registration form without the multi-select dropdown. Note that we have kept a place to display the validation error message after each input.

Below is our stylesheet:

css/style.css


*{
  box-sizing: border-box;
}
h1{
  text-align: center;
}
.form1{
  width:40%;
  margin:auto;
}

Add a Bootstrap Multi-Select Dropdown

To add a multi-select dropdown, we will use the HTML code below, after the email field.


<div class="mb-3">
    <label for="" class="form-label">Select one or more skill(s)<small> (Press and hold Ctrl key to select
            multiple)</small></label>
    <select multiple class="form-select" name="skill[]" id="skill">
        <option value="">Select skills</option>
        
    </select>
    <div class="text-danger"></div>
</div>

Skill is a multiple select dropdown, the user can choose one or more skills. Note the use of "multiple" attribute and the name of the field with square brackets - "[ ]", denoting an array to hold multiple values.

Populate the multi-select dropdown from the database

We will select the data from the "skills" table and populate the dropdown using PHP code. We will connect to the MySQL database. Using a prepared statement we will get the data and populate the dropdown. See the code below:


  <?php
  $conn = new mysqli("localhost", "root", "", "test");
  if ($conn->connect_error) {
    die("Database connection failed");
  }
  $sql = "select * from skills order by skill_name";
  $stmt = $conn->prepare($sql);
  $stmt->execute();
  $result = $stmt->get_result();
  ?>

We will add the above code in index.php.

Now, update the code for multi-select dropdown in index.php to populate the skills. 


<div class="mb-3">
    <label for="" class="form-label">Select one or more skill(s)<small> (Press and hold Ctrl key to select
            multiple)</small></label>
    <select multiple class="form-select" name="skill[]" id="skill">
        <?php foreach ($result as $row) { ?>
        <option value="<?= $row['id'] ?>">
            <?= $row['skill_name'] ?>
        </option>
        <?php } ?>
    </select>
    <div class="text-danger"></div>
</div>

Using a foreach loop, we are populating all the skills in the dropdown.

Handle the Form Submission in PHP (process_skills.php)

After the form is submitted, PHP will receive the selected values as an array. You can then loop through that array and insert each value into the database.

Validate input data

We will validate required fields, email format and also check duplicate emails before storing the data in the database. See the code below in process_skills.php:


<?php
  $name = $email = "";
  $name_err = $email_err = $skill_err = "";
  $error = false;
  $skill = array();

  if (isset($_POST['submit'])){
    $name = trim($_POST['name']);
    $email = trim($_POST['email']);
    if (isset($_POST['skill']))
      $skill = $_POST['skill']; // array of skills

    if ($name == ""){
      $name_err = "Name is Required";
      $error = true;
    }

    // validate email 
    if ($email == "") {
      $email_err = "Email is Required";
      $error = true;
    }
    elseif(!filter_var($email, FILTER_VALIDATE_EMAIL)){
      $email_err = "Not a valid email address";
      $error = true;
    }
    else{   // check if email already exists
      $sql = "select * from candidates where email_id = ?";
      $stmt = $conn->prepare($sql);
      $stmt->bind_param("s",$email);
      $stmt->execute();
      $result = $stmt->get_result();
      if ($result->num_rows > 0) {
        $email_err = "Email Id already exists";
        $error = true;
      }
    }
    if (count($skill) == 0){
      $skill_err = "At least one skill is required";
      $error = true;
    }
}

After validating the name and email, we are checking if skills are selected. If no skills are selected, $skill will be empty, so in line 38, we are using the count() function on the array and validating the multi-select dropdown data. All these validation error messages will be displayed in the form. Note that we are using an error flag to check if any validation error occurs.

In case of validation error for any of the input fields, we want the values in the dropdown remain selected so that users do not have to select them again. So, add the "value" attribute for the name and email fields. Update the multi-select dropdown code in index.php as below: 


<div class="mb-3">
    <label for="" class="form-label">Select one or more skill(s)<small> (Press and hold Ctrl key to select
            multiple)</small></label>
    <select multiple class="form-select" name="skill[]" id="skill">
        <?php foreach ($result as $row) { ?>
        <option value="<?= $row['id'] ?>" <?php if (in_array($row['id'], $skill)) echo "selected" ?>>
            <?= $row['skill_name'] ?>
        </option>
        <?php } ?>
    </select>
    <div class="text-danger"> <?= $skill_err ?> </div>
</div>

See the use of in_array() function to check if the value fetched from the database matches the selected value. If matches, we are adding 'selected' attribute.

Insert Multi-Select input data into the database

When all validations are successful, we will insert the data into two tables. First, we will insert a row in the candidates table, we will get the candidate_id and then in a loop we will insert the skills in the 'candidate_skills' table for that candidate_id. 


 if (!$error) { 
      // all vaidations are successful
      $conn->begin_transaction();
      try{
        $curr_dt = date('Y-m-d');
        $sql = "insert into candidates (name,email_id,registration_dt) values (?,?,?)";
        $stmt = $conn->prepare($sql);
        $stmt->bind_param("sss", $name, $email, $curr_dt);
        $stmt->execute();
        $candidate_id = $conn->insert_id;

        foreach ($skill as $value) {
          $skill_id = $value;

          $sql = "insert into candidate_skills (candidate_id,skill_id) values (?,?)";
          $stmt = $conn->prepare($sql);
          $stmt->bind_param("ii", $candidate_id, $skill_id);
          $stmt->execute();
        }   
        $conn->commit();
        $succ_msg = "Your Skill Registration Successful";
       
      } catch (Exception $e) {
        $conn->rollback();
        $err_msg = $e->getMessage();
      }
    }

Note the use of begin_transaction() with commit and rollback as multiple insert statements are used. Either we commit all the data or rollback the entire transaction in case of error. As the skills are in an array, we are using a loop to insert the skills. Success and error messages will be displayed in the form as below:


    <?php if (!empty($succ_msg)) { ?>
                <div class="alert alert-success"><?= $succ_msg ?></div>
            <?php  }
          if (!empty($err_msg)) { ?>
                <div class="alert alert-danger"><?= $err_msg ?></div>
            <?php  } ?>

Create Multi Select Dropdown in PHPTest the Application

Make sure in your XAMPP control panel, Apache and MySQL services are running. Run localhost/multiselect in the browser. Validate and test if multiple skills are inserted for a candidate by checking the database.

Get HTML Multi-Select Values in PHP download source codeDownload Source Code

Download source code from GitHub by clicking the download button below:

Insert Multiple Values Using Dropdown List Box in PHPConclusion

Using a multi-select dropdown in PHP forms is very useful when you want users to choose multiple items. In this PHP tutorial, I have demonstrated the use of a multi-select dropdown in PHP and also showed how to insert the values in the database using PHP with MySQL. I hope this multiselect dropdown examples will be useful to you. You can modify as per your requirements and apply the code in your project.

Please write your comments/questions in the Comments section below. Your questions, doubts and suggestions are always welcome.

Related Topics

Post a Comment

Save my Name and Email id for future comments