Different Ways of Populating HTML Select Options from MySQL Database using PHP, JS, and jQuery Tutorial

Submitted by oretnom23 on Tuesday, February 21, 2023 - 13:23.

In this tutorial, you will learn how to Populate the HTML's Select box with options from the MySQL Database. The main purpose of this tutorial is to provide the students and beginners with a reference for learning some techniques for building an application using PHP, MySQL DB, JS, and jQuery Library. Here, snippets and scripts are provided to give you a better understanding of the possible ways to achieve the said goal of this tutorial. A sample source code zip file is also provided and is free to download.

What is HTML Select?

The HTML Select (<select></select>) is an element used for creating a drop-down list. It is used for form fields that have multiple options or possible values. It is commonly used in forms to collect user input.

How to Populate HTML Select with Option From MySQL Database?

There are a lot of ways to populate the HTML Select box with options listed from the MySQL Database. Using The PHP code, we can simply retrieve the data from the Database and use the retrieved data as the select field option.

Here are the following possible and effective ways to Populate Select with data from MySQL DB:

Straightforward Coding using HTML and PHP
Using JavaScript
Using JavaScript Fetch API
Using jQuery
Using jQuery Ajax Request

The list above is all effective and can be achieved easily achieve in just a short line of code. Options 3 and 5 are commonly used when the data of the form page are being retrieved using API requests.

Here are the following snippets or scripts of how you can achieve our for this tutorial using the listed possible ways.

Examples

Assuming that we have the following Database Schema which contains some list of countries. The data on the countries table is the data that we will use as the options of the HTML Select element.

CREATE TABLE `countries` (
    `id` int(11) NOT NULL,
    `name` varchar(250) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `countries` (`id`, `name`) VALUES
    (1, 'Singapore'),
    (2, 'Peru'),
    (3, 'Belgium'),
    (4, 'United States'),
    (5, 'United Kingdom'),
    (6, 'Indonesia'),
    (7, 'Ireland'),
    (8, 'India'),
    (9, 'Netherlands'),
    (10, 'Nigeria'),
    (11, 'Pakistan'),
    (12, 'Sweden'),
    (13, 'Canada'),
    (14, 'Germany'),
    (15, 'Mexico'),
    (16, 'New Zealand'),
    (17, 'Australia'),
    (18, 'Colombia'),
    (19, 'Chile'),
    (20, 'Italy'),
    (21, 'China'),
    (22, 'Poland'),
    (23, 'South Africa');
ALTER TABLE `countries`
    ADD PRIMARY KEY (`id`);
ALTER TABLE `countries`
    MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=24;

Database Connection

Here are the PHP scripts that handle the Database Connection between the application and MySQL Database.

db-connect.php

<?php
$host = "localhost";
$username = "root";
$password = "";
$dbname = "sample_db";
 
$conn = new MySQLi($host, $username, $password, $dbname);
 
if(!$conn){
    die("Database Connection failed. Error: ". $conn->error);
}

The example script below that I provided is using the Bootstrap 5.3 Framework Library for the design.

Example 1: Straightforward Coding using HTML and PHP

Here's an example script that demonstrates the straightforward technique to list the DB data on the HTML Select element. The script is written in combined HTML and PHP scripts.

<?php ;
require_once('db-connect.php'); 
$countries_qry = $conn->query("SELECT * FROM `countries` order by `name` asc");
$countries = $countries_qry->fetch_all(MYSQLI_ASSOC);
?>
<div class="card rounded-0 shadow mb-3">
    <div class="card-header rounded-0">
        <div class="card-title"><b>Using HTML5</b></div>
    </div>
    <div class="card-body rounded-0">
        <div class="container-fluid">
            <div class="mb-3">
                <label class="fw-light text-body">Populating Select Options: Example #1</label>
                <select class="form-select rounded-0" name="select1" id="select1">
                    <?php 
                    $default = mt_rand(1, count($countries));
                    $countries = $conn->query("SELECT * FROM `countries` order by `name` asc");
                    while($row = $countries->fetch_assoc()):
                        if($default == $row['id'])
                            echo "<option value='{$row['id']}'>{$row['name']}</option>";
                        else
                            echo "<option value='{$row['id']}'>{$row['name']}</option>";
                    endwhile;
                    ?>
                </select>
            </div>
        </div>
    </div>
</div>
<?php $conn->close(); ?>

The script above will result in something like the following image:

Populating SELECT Element with Data from MySQL Database

Example 2: Using JavaScript

We can also populate the list option data of the select element using JavaScript due to any instance that we need to meet our requirements. We can simply set the data into the JS script as a JSON encoded string and parse it using the JS JSON.parse and create an option element and append it into the select element. See the following example script.

<?php ;
require_once('db-connect.php'); 
$countries_qry = $conn->query("SELECT * FROM `countries` order by `name` asc");
$countries = $countries_qry->fetch_all(MYSQLI_ASSOC);
$countries_json = json_encode($countries);
?>
 
<div class="col-lg-6 col-md-6 col-sm-12 col-xs-12 mx-auto">
    <div class="card rounded-0 shadow mb-3">
        <div class="card-header rounded-0">
            <div class="card-title"><b>Using JavaScript</b></div>
        </div>
        <div class="card-body rounded-0">
            <div class="container-fluid">
                <div class="mb-3">
                    <label class="fw-light text-body">Populating Select Options: Example #2</label>
                    <select class="form-select rounded-0" name="select2" id="select2"></select>
                </div>
            </div>
        </div>
    </div>
</div>
 
<script>
var countries = '<?= $countries_json ?>';
var pjs_countries = JSON.parse(countries);
 
const select2 = document.getElementById('select2')
/**
 * Populating Select Field : Example #2 (PURE JS)
 */
Object.keys(pjs_countries).map(function(k){
    var option = document.createElement('option')
    option.setAttribute('value', pjs_countries[k].id)
    option.innerText = pjs_countries[k].name
    select2.appendChild(option)
})
// set default Selected
select2.value = Math.floor(Math.random() * (pjs_countries.length - 1 ) + 1)
</script>
<?php $conn->close(); ?>

Here's the output of the script.

Populating SELECT Element with Data from MySQL Database

Example 3: Using jQuery

This option or way is like Example #2 but only written in JS script using the jQuery Library methods and functions.

<?php ;
require_once('db-connect.php'); 
$countries_qry = $conn->query("SELECT * FROM `countries` order by `name` asc");
$countries = $countries_qry->fetch_all(MYSQLI_ASSOC);
$countries_json = json_encode($countries);
?>
<div class="card rounded-0 shadow mb-3">
    <div class="card-header rounded-0">
        <div class="card-title"><b>Using jQuery</b></div>
    </div>
    <div class="card-body rounded-0">
        <div class="container-fluid">
            <div class="mb-3">
                <label class="fw-light text-body">Populating Select Options: Example #3</label>
                <select class="form-select rounded-0" name="select3" id="select3"></select>
            </div>
        </div>
    </div>
</div>
<script>
var countries = '<?= $countries_json ?>';
/**
 * Using jQuery: Example #3
 */
    var jq_Countries = $.parseJSON(countries)
const select3 = $('#select3')
jq_Countries.forEach(data => {
    var option = $('<option>')
    option.attr('value', data.id)
    option.text(data.name)
    select3.append(option)
})
 
// set default Selected
select3.val(Math.floor(Math.random() * (pjs_countries.length - 1 ) + 1))
</script>
<?php $conn->close(); ?>

Here's the output of the script above.

Populating SELECT Element with Data from MySQL Database

Example 4: Using JavaScript Fetch API

Using JS API is also one of the possible ways to populate the select element using the data from the database. This way can be used if you wish to retrieve the database data using HTTP Request and JavaScript without any JS library.

<div class="card rounded-0 shadow mb-3">
    <div class="card-header rounded-0">
        <div class="card-title"><b>Using JS Fetch API</b></div>
    </div>
    <div class="card-body rounded-0">
        <div class="container-fluid">
            <div class="mb-3">
                <label class="fw-light text-body">Populating Select Options: Example #4</label>
                <select class="form-select rounded-0" name="select4" id="select4"></select>
            </div>
        </div>
    </div>
</div>
<script>
/**
 * Using JS Fetch API: Example 4
 */
    const select4 = document.getElementById('select4')
fetch('api.php')
.then(response => { return response.json()})
.then(data => {
    data.forEach(country => {
        var option = document.createElement('option')
            option.setAttribute('value', country.id)
            option.innerText = country.name
        select4.appendChild(option)
    })
 
    // set default Selected
    select4.value = (Math.floor(Math.random() * (data.length - 1 ) + 1))
})
</script>

api.php

<?php 
require_once('db-connect.php');
$countries_qry = $conn->query("SELECT * FROM `countries` order by `name` asc");
$countries = $countries_qry->fetch_all(MYSQLI_ASSOC);
echo json_encode($countries);
$conn->close();

Populating SELECT Element with Data from MySQL Database

Example 5: Using jQuery's Ajax Request

Here's another example of populating the select element with MySQL Database data using Ajax Request.

<div class="card rounded-0 shadow mb-3">
    <div class="card-header rounded-0">
        <div class="card-title"><b>Using jQuery's Ajax</b></div>
    </div>
    <div class="card-body rounded-0">
        <div class="container-fluid">
            <div class="mb-3">
                <label class="fw-light text-body">Populating Select Options: Example #5</label>
                <select class="form-select rounded-0" name="select5" id="select5"></select>
            </div>
        </div>
    </div>
</div>
<script>
/**
 * Using jQuery's Ajax Request: Example 5
 */
    const select5 = $('#select5')
$.ajax({
    url:'api.php',
    dataType:'json',
    error: err=>{
        console.error(err)
    },
    success: function(resp){
        resp.forEach(data => {
            var option = $('<option>')
            option.attr('value', data.id)
            option.text(data.name)
            select5.append(option)
        })
        select5.val(Math.floor(Math.random() * (resp.length - 1 ) + 1))
    }
})
</script>

api.php

<?php 
require_once('db-connect.php');
$countries_qry = $conn->query("SELECT * FROM `countries` order by `name` asc");
$countries = $countries_qry->fetch_all(MYSQLI_ASSOC);
echo json_encode($countries);
$conn->close();

Populating SELECT Element with Data from MySQL Database

There you go. It's up to you which way or technique you prefer to use or suits your current project. I have also provided the source code zip file that I created for this tutorial. You can download it for free on this site. The download button is located below this tutorial's content.

That's it! I hope this Different Ways of Populating HTML Select Options from MySQL Database using PHP, JS, and jQuery Tutorial will help you with what you are looking for and will be useful for your current and future PHP Projects.

Explore more on this website for more Tutorials and Free Source Codes.

Happy Coding =)

Add new comment

1742 views

Different Ways of Populating HTML Select Options from MySQL Database using PHP, JS, and jQuery Tutorial

What is HTML Select?

How to Populate HTML Select with Option From MySQL Database?

Examples

Database Connection

Example 1: Straightforward Coding using HTML and PHP

Example 2: Using JavaScript

Example 3: Using jQuery

Example 4: Using JavaScript Fetch API

Example 5: Using jQuery's Ajax Request

Happy Coding =)

Tags

Add new comment