Sometimes it requires auto-populate data on the element based on the selection of another element e.g. City names based on a state.
You can do this with only PHP but it required you to submit every time on selection.
This solves the problem but it is a little frustrating because it submits every time even if the selection is right or wrong.
For making it better you can use AJAX with jQuery which loads new data and removes the old one on each selection.
In the demonstration, I am creating a Department drop-down list, and based on the option selection show all existing users of that department on another Dropdown.
Contents
1. Table structure
I am using 2 tables in the example –
department Table –
CREATE TABLE `department` ( `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, `depart_name` varchar(80) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
users Table –
CREATE TABLE `users` ( `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, `username` varchar(80) NOT NULL, `name` varchar(80) NOT NULL, `email` varchar(80) NOT NULL, `department` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
2. Configuration
Create a config.php
to define database connection.
Completed Code
<?php $host = "localhost"; /* Host name */ $user = "root"; /* User */ $password = ""; /* Password */ $dbname = "tutorial"; /* Database name */ $con = mysqli_connect($host, $user, $password,$dbname); // Check connection if (!$con) { die("Connection failed: " . mysqli_connect_error()); }
3. HTML
Creating two Dropdown elements one –
- Fetch records from
department
table and use to add<option>
in<select id='sel_depart'>
and - Another dropdown shows the users names which are filled with jQuery based on the department name selection from the first dropdown element.
Completed Code
<?php include "config.php"; ?> <div>Departments </div> <select id="sel_depart"> <option value="0">- Select -</option> <?php // Fetch Department $sql_department = "SELECT * FROM department"; $department_data = mysqli_query($con,$sql_department); while($row = mysqli_fetch_assoc($department_data) ){ $departid = $row['id']; $depart_name = $row['depart_name']; // Option echo "<option value='".$departid."' >".$depart_name."</option>"; } ?> </select> <div class="clear"></div> <div>Users </div> <select id="sel_user"> <option value="0">- Select -</option> </select>
4. PHP
- Create
getUsers.php
file to handle AJAX requests. - Fetching users based on department selection from the
users
table. - Initialize
$users_arr
Array with userid and name. - Return
$users_arr
Array in JSON format.
Completed Code
<?php include "config.php"; $departid = 0; if(isset($_POST['depart'])){ $departid = mysqli_real_escape_string($con,$_POST['depart']); // department id } $users_arr = array(); if($departid > 0){ $sql = "SELECT id,name FROM users WHERE department=".$departid; $result = mysqli_query($con,$sql); while( $row = mysqli_fetch_array($result) ){ $userid = $row['id']; $name = $row['name']; $users_arr[] = array("id" => $userid, "name" => $name); } } // encoding array to json format echo json_encode($users_arr);
5. jQuery
Sending AJAX request when an option selected from the first drop-down. Pass the selected option value as data
and on successful callback fill <select id='sel_user'>
with response.
Completed Code
$(document).ready(function(){ $("#sel_depart").change(function(){ var deptid = $(this).val(); $.ajax({ url: 'getUsers.php', type: 'post', data: {depart:deptid}, dataType: 'json', success:function(response){ var len = response.length; $("#sel_user").empty(); for( var i = 0; i<len; i++){ var id = response[i]['id']; var name = response[i]['name']; $("#sel_user").append("<option value='"+id+"'>"+name+"</option>"); } } }); }); });
6. Demo
7. Conclusion
In the example, I used dropdown element for autofill but you can do this with any other element like TextBox, Textarea, etc. that you can fill with information according to input on another element.
You can view this tutorial to know to auto-populate dropdown with PDO and PHP.
If you found this tutorial helpful then don't forget to share.