Use a Multi-Select Dropdown in Your Form in PHP and MySQL

A multiple select dropdown is a type of input field with the option to select one or more values from a list of values. When a user submits a form with multiple selected values, you have to store the values in the database. We will show how to handle form submission with multiple selected values in a form in PHP and MySQL.

For a usual select dropdown, the user can select only one value from the list. The selected value is taken in a variable and insert/update takes place in the database. For multiple select dropdown, you have to handle more than one value from the list. So, you have to use an array of values and update/insert database accordingly.

Select Dropdown


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

Multi-Select Dropdown


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

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

In this topic, we will develop an application in PHP and MySQL, where users can enter their name, email and select one or more skills and submit a form. So, the skill is our multi-select dropdown. The screenshot of the form is given below:

Form for multi-select dropdown in PHP

select multiple options in dropdown in PHPFolders and Files

show multiple selected values in dropdown in PHP

We have a folder called 'multi_select' under 'xampp/htdocs'.

Folder 'cfg' - We have kept dbconnect.php in this folder to connect to the MySQL database.
Folder 'css' - This is for the custom stylesheet.
Folder 'db' - In this folder we have kept our create table scripts.

We have two php programs, one is index.php which is the form to take input data from the user and the other is process_skills.php which is used to process the form submitted by the user. We have included process_skills.php within index.php.

Create MySQL tables

We will create three tables - skills, candidate and 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. Below is the structure of the table:

Multiple Select Dropdown and Get Values in PHP

This table has 2 columns.

  1. skill_id - primary key
  2. skill_name - name of the skill

The create table script for this table is given below, you can use this code to create the table. You can also download all scripts from download section later in this topic.

db/skills.sql


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

INSERT INTO `skills` (`skill_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 (`skill_id`);

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

Table 2 - candidate

This table is used to store candidate details like name, email id etc. Below is the structure of the table:

multiple selection in dropdown in php and mysql

This table has 4 columns.

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

db/candidate.sql


CREATE TABLE `candidate` (
  `candidate_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 `candidate`
  ADD PRIMARY KEY (`candidate_id`);

ALTER TABLE `candidate`
  MODIFY `candidate_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

This table is empty initially. When user submits the form, this table gets populated.

Table 3 - candidate_skills

candidate_skills table is used to store skills for each candidate. When the user registers with their skills, all skills entered for a user are stored in this table candidate-wise. Below is the structure of the table:

display multiple selected value of dropdownlist in php

This table has 3 columns.

  1. id - it is the primary key and auto incremented
  2. candidate_id - candidate_id from the candidate table. You can say it is the foreign key of candidate table, though we have not enforced any constraints here.
  3. skill_id - skill_id of the skill for a candidate

db/candidate_skills.sql


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;

This table is also empty initially. When the user submits the form, this table gets populated.

Connect to MySQL database (dbconnect.php)

PHP script to connect to MySQL database.

cfg/dbconnect.php


<?php
  $server="localhost";
  $userid="root";
  $pwd="";
  $dbname="test";
  $conn = mysqli_connect($server, $userid, $pwd, $dbname);
//Check connection
if (!$conn) 
  	die("Connection Error: " . mysqli_connect_error());

We are using mysqli_connect() function with the below parameters.

  1. server - localhost
  2. userid - we are using root user
  3. password - no password for the user root
  4. database name - test datadase.

We will include dbconnect.php in other php programs. You can also read the topic How to connect to MySQL database in PHP using MySQLi and PDO.

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

The form has a multi-select input field, where user can select one or more skills from a list. The screenshot is given below:

php multiple select option from database

Let's see the code for this.

index.php


<!DOCTYPE html>
<html lang="en">
<head>
  <title>Skill Registration</title>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
  <link  rel="stylesheet" href="css/style.css">
</head>
<body>
  <?php include "process_skills.php";
  $sql = "select * from skills order by skill_name";
  $rs = mysqli_query($conn,$sql);?>
  <div class="container">
    <h4>Register Your Skills</h4>
    <form id ="frm" action = "" method = "post" onsubmit="return validate_form()">
      <p><?php echo $msg;?></p>
        <div class="form-group col-md-12">
      	 	<label>Name</label>
      	 	<input type = "text" name="name" id = "name" class="form-control" maxlength="100" value="<?php echo $name;?>" placeholder="Enter Your Name" required>
    	 </div>
       <div class="form-group col-md-12">
          <label>Email</label>
          <input type = "test" name="email" id = "email" class="form-control" maxlength="200" value="<?php echo $email;?>" placeholder="Enter Your Email" required>
          <div class="error"><?php if ($email_err_msg !="") echo $email_err_msg;?></div>
       </div>
       <div class="form-group col-md-12">
          <label for="ps">Select one or more skill(s) <small>(Press and hold Ctrl key to select multiple)</small></label>
          <select style="height: 155px;padding:2px;" name="skill[]" id="skill" class="form-control" multiple required>
            <?php foreach ($rs as $value) { ?>
              <option value="<?php echo $value['skill_id']?>" <?php if (in_array($value['skill_id'], $skill)) { ?> selected <?php } ?>><?php echo $value['skill_name']?></option>
            <?php } ?>
          </select>
        </div>
         <div class="col-md-12 text-center">
          <input type ="submit" name="submit" class="btn btn-primary" value="Submit">
        </div>
    </form>
  </div>
</body>
</html>

It is a simple form where the user can enter their Name and email id and select Skills. Skill is a multiple select dropdown, the user can choose one or more skills. We are selecting all skills from the "skills" table to populate this multi-select list box. Remember that values selected in this multi-select list box will be in an array named skill[]. Once the form is submitted, process_skills.php will process the data.

Write PHP code to process form data (process_skills.php)

It validates the email and inserts a row in the candidate table. Also, it inserts rows in the candidate_skills table for selected skills for that candidate. It processes the skills as an array, using a for loop. See the code for process_skills.php below:

process_skills.php


<?php
  include "cfg/dbconnect.php";
  $name = $email = $skill_name = $skill_id = $email_err_msg = $msg = "";
  $skill = array();
  $email_error = false;  // to validate email
  if (isset($_POST['submit'])){
    $last_id = 0;
    $name = trim($_POST['name']);
    $email = trim($_POST['email']);
    $skill = $_POST['skill']; // array of skills
    // validate email
    if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
      $email_error = true;
      $email_err_msg = "Not a valid email address";
    }
    // check if email already exists
    $sql = "select * from candidate where email_id = '$email'";
    $result = mysqli_query($conn,$sql);
    if (mysqli_num_rows($result) > 0) {
       $email_error = true;
      $email_err_msg = "Email Id already exists";
    }
    if (!$email_error) { // email id is valid and new
      $curr_dt = date('Y-m-d');
      $sql = "insert into candidate (name,email_id,registration_dt) values ('$name','$email','$curr_dt')";
      $result = mysqli_query($conn,$sql);
      $last_id = mysqli_insert_id($conn);// take the last inserted id as candidate_id
      for ($i = 0;$i<count($skill);$i++){
          $skill_id = $skill[$i];
          $sql = "insert into candidate_skills (candidate_id,skill_id) values ('$last_id','$skill_id')";
          $result = mysqli_query($conn,$sql);
        }   // for loop ends
      $msg = 'Your Skills Added Successfully';
      // clear all values in the form
      $name = "";
      $email = "";
      $skill = array();
    }  
}

See in line 27, $last_id is the last inserted id of the candidate (candidate_id), using mysqli_insert_id($conn) you can get the id of the inserted row in a table.

Add CSS (style.css)

We have already added style.css in index.php.

css/style.css


h4{
  margin-top: 30px;
  margin-bottom: 30px;
  text-align: center;
}
#frm{
  width:40%;
  margin:auto;
}
label{
    font-weight: bold;
  }
p {
  text-align: center;
}
.error{
  color: red;
}
@media (max-width: 700px){
#frm {
    width: 100%;
  }
}

You can see simple styles are added here. Keep this style.css file in your css folder.

Create Multi Select Dropdown in PHPTest the Application

In your XAMPP control panel Apache and MySQL services should be running. Run localhost/multi_select in the browser. The form will be displayed.

Enter the Name, Email and select more than one skill and submit. You will see a successful message displayed. See below:

creating Multiple Selection Lists in PHP

Verify that the rows are inserted in the candidate and candidate_skills tables in the database.

Get multiple Selected Values in PHPImportant Note

  1. We used in_array() php function to check if a value is already in the selected array so that the user does not have to select skills again, in case it gives some error during submission.
  2. Since this application uses multiple database inserts in more than one table, it is always better to use database commit and rollback explicitly. Because if one insert fails, we need to rollback to ensure data consistency.

Get HTML Multi-Select Values in PHPDownload Source Code

Download the zip file by clicking the download button below:

Insert Multiple Values Using Dropdown List Box in PHPConclusion

Multi-Select is a common requirement, so you must know how to use it in a form and process the selected values. I have tried to explain it by developing this simple application. I hope it will be good learning for you.

Related Topics

Popular Topics

Post a Comment

Save my Name and Email id for future comments