article

Saturday, July 16, 2022

PHP Mysqli Populate Dropdown using JavaScript AJAX

PHP Mysqli Populate Dropdown using JavaScript AJAX

Bootstrap 5
https://getbootstrap.com/docs/5.0/getting-started/introduction/
https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css

CREATE TABLE `countries` (
  `id` int(6) NOT NULL,
  `name` varchar(250) NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `countries` (`id`, `name`) VALUES
(1, 'Afghanistan'),
(171, 'Philippines'),
(227, 'United States of America');

CREATE TABLE `states` (
  `id` int(11) NOT NULL,
  `name` varchar(80) NOT NULL,
  `country_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `states` (`id`, `name`, `country_id`) VALUES
(1, 'Manila', 171),
(2, 'Zambales', 171),
(3, 'Pampanga', 171),
(4, 'Bulacan', 171),
(9, 'Colorado', 227),
(10, 'California', 227),
(11, 'Arizona', 227),
(12, 'Alaska', 227),
(13, 'Alabama', 227);

CREATE TABLE `cities` (
  `id` int(11) NOT NULL,
  `name` varchar(80) NOT NULL,
  `state_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `cities` (`id`, `name`, `state_id`) VALUES
(1, 'Manila', 1),
(2, 'Pasay', 1),
(3, 'Quezon City', 1),
(4, 'Olongapo City', 2),
(5, 'Angeles City', 3),
(6, 'Malolos', 4),
(7, 'Denver', 9),
(8, 'Los Angeles', 10),
(9, 'Phoenix', 11),
(10, 'Anchorage', 12),
(11, 'Huntsville', 13);

index.php
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
//index.php
<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>PHP Mysqli Populate Dropdown using JavaScript AJAX</title>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css">
</head>
<body>
<div class="container"><br/><br/>
    <div class="row">
        <div class="col-lg-10">
            <h2>PHP Mysqli Populate Dropdown using JavaScript AJAX</h2>
        </div>
    </div>
    <div class="row">
    <?php
    include "config.php";
    $sql = "SELECT * from countries order by name";
    $stmt = $con->prepare($sql);
    $stmt->execute();
    $result = $stmt->get_result();
 
    ?>
    <div class="row mb-3">
        <label class="col-sm-2 col-form-label">Country:</label>
        <div class="col-sm-10">
            <select id="country" onchange="getStates(this.value);" class="form-select">
                <option value="0" >– Select Country –</option>
                <?php
                while ($row = $result->fetch_assoc() ){
 
                   $id = $row['id'];
                   $name = $row['name'];
 
                   echo "<option value='".$id."' >".$name."</option>";
                }
                ?>
            </select>
        </div>
    </div>
         
    <div class="row mb-3">
        <label class="col-sm-2 col-form-label">State:</label>
        <div class="col-sm-10">
             <select id="state" onchange="getCities(this.value);" class="form-select">
                <option value="0" >– Select State –</option>
             </select>
        </div>
    </div>
 
    <div class="row mb-3">
        <label class="col-sm-2 col-form-label">City:</label>
        <div class="col-sm-10">
             <select id="city" class="form-select">
                <option value="0" >– Select City –</option>
             </select>
        </div>
    </div>
     
    </div>
</div>   
<script type="text/javascript">
        function getStates(country_id){
             
            // Empty the dropdown
            var stateel = document.getElementById('state');
            var cityel = document.getElementById('city');
             
            stateel.innerHTML = "";
            cityel.innerHTML = "";
 
            var stateopt = document.createElement('option');
            stateopt.value = 0;
            stateopt.innerHTML = '-- Select State --';
            stateel.appendChild(stateopt);
 
            var cityopt = document.createElement('option');
            cityopt.value = 0;
            cityopt.innerHTML = '-- Select City --';
            cityel.appendChild(cityopt);
 
            // AJAX request
            var xhttp = new XMLHttpRequest();
            xhttp.open("POST", "ajaxfile.php", true);
            xhttp.setRequestHeader("Content-Type", "application/json");
            xhttp.onreadystatechange = function() {
                if (this.readyState == 4 && this.status == 200) {
                    // Response
                    var response = JSON.parse(this.responseText);
                     
                    var len = 0;
                    if(response != null){
                       len = response.length;
                    }
                    
                    if(len > 0){
                        // Read data and create <option >
                        for(var i=0; i<len; i++){
 
                            var id = response[i].id;
                            var name = response[i].name;
 
                            // Add option to state dropdown
                            var opt = document.createElement('option');
                            opt.value = id;
                            opt.innerHTML = name;
                            stateel.appendChild(opt);
 
                        }
                    }
                }
            };
            var data = {request:'getStates',country_id: country_id};
            xhttp.send(JSON.stringify(data));
             
        }
 
        function getCities(state_id){
 
            // Empty the dropdown
            var cityel = document.getElementById('city');
             
            cityel.innerHTML = "";
 
            var cityopt = document.createElement('option');
            cityopt.value = 0;
            cityopt.innerHTML = '-- Select City --';
            cityel.appendChild(cityopt);
 
            // AJAX request
            var xhttp = new XMLHttpRequest();
            xhttp.open("POST", "ajaxfile.php", true);
            xhttp.setRequestHeader("Content-Type", "application/json");
            xhttp.onreadystatechange = function() {
                if (this.readyState == 4 && this.status == 200) {
                    // Response
                    var response = JSON.parse(this.responseText);
                     
                    var len = 0;
                    if(response != null){
                       len = response.length;
                    }
                    
                    if(len > 0){
                        // Read data and create <option >
                        for(var i=0; i<len; i++){
 
                            var id = response[i].id;
                            var name = response[i].name;
 
                            // Add option to city dropdown
                            var opt = document.createElement('option');
                            opt.value = id;
                            opt.innerHTML = name;
                            cityel.appendChild(opt);
 
                        }
                    }
                }
            };
            var data = {request:'getCities',state_id: state_id};
            xhttp.send(JSON.stringify(data));
        }
</script>
</body>
</html>
config.php
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
//config.php
<?php
 
$host = "localhost";
$user = "root";
$password = "";
$dbname = "projectdb";
 
// Create connection
$con = new mysqli($host, $user, $password, $dbname);
 
// Check connection
if ($con->connect_error) {
  die("Connection failed: " . $con->connect_error);
}
ajaxfile.php
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
//ajaxfile.php
<?php
include 'config.php';
 
// Read POST data
$postData = json_decode(file_get_contents("php://input"));
$request = "";
if(isset($postData->request)){
   $request = $postData->request;
}
 
// Get states
if($request == 'getStates'){
   $country_id = 0;
   $result = array();$data = array();
 
   if(isset($postData->country_id)){
      $country_id = $postData->country_id;
 
      $sql = "SELECT * from states WHERE country_id=?";
      $stmt = $con->prepare($sql);
      $stmt->bind_param("i", $country_id);
      $stmt->execute();
      $result = $stmt->get_result();
 
      while ($row = $result->fetch_assoc()){
 
         $id = $row['id'];
         $name = $row['name'];
 
         $data[] = array(
            "id" => $id,
            "name" => $name
         );
 
      }
 
   }
 
   echo json_encode($data);
   die;
 
}
 
// Get cities
if($request == 'getCities'){
   $state_id = 0;
   $result = array();$data = array();
 
   if(isset($postData->state_id)){
      $state_id = $postData->state_id;
 
      $sql = "SELECT * from cities WHERE state_id=?";
      $stmt = $con->prepare($sql);
      $stmt->bind_param("i", $state_id);
      $stmt->execute();
      $result = $stmt->get_result();
 
      while ($row = $result->fetch_assoc()){
 
         $id = $row['id'];
         $name = $row['name'];
 
         $data[] = array(
            "id" => $id,
            "name" => $name
         );
 
      }
   }
 
   echo json_encode($data);
   die;
}
Posted by ednalan at 12:11 AM . Under .

Related Post