In this post you will create How to load records using Select option with jQuery AJAX in PHP Mysql.
For this I have use example of city name. When user select particular city name then at that time city relates to that user name and other details will be load with out page refresh. I have use a table, In table have user city name and users full details.
I have use simple ajax function call when user select city name from select box then it send request to php page and on the basis of selected city name it select user data from users table as per selected city name.
First of all you will fetch the common city name from users table use the SQL DISTINCT query and show in the select box. than city select box show related users city name in table.
Create a Database in MySQL
The first step in this tutorial is the creation of a MySQL database. you can create tables by running SQL queries. Create a table ‘users’ in database using the following SQL query.
-- -- Database: `testing` -- -- -------------------------------------------------------- -- -- Table structure for table `users` -- CREATE TABLE `users` ( `id` int(50) NOT NULL PRIMARY KEY AUTO_INCREAMENT, `name` varchar(50) NOT NULL, `email` varchar(50) NOT NULL, `age` varchar(50) NOT NULL, `city_name` varchar(50) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- -- Dumping data for table `users` -- INSERT INTO `users` (`id`, `name`, `email`, `age`, `city_name`) VALUES (1, 'sandeep', 'sandy32@gmail.com', '30', 'delhi'), (2, 'manish arora', 'manish07@gmail.com', '25', 'delhi'), (4, 'sahid', 'sahid12@gmail. com', '30', 'mumbai'), (5, 'anil', 'anil21@gmaill.com', '45', 'mumbai'), (6, 'devesh', 'devesh120@gmail.com', '30', 'kolkata'), (7, 'vijay', 'vijay21@gmail.com', '45', 'mumbai'), (8, 'akshay kumar', 'akshay53@gmail.com', '53', 'mumbai'), (9, 'anurag singh', 'anurag12@gmail.com', '25', 'delhi'), (10, 'teena ', 'teen53@gmail.com', '32', 'delhi'), (11, 'avanish singh', 'avanish32@gmail.com', '25', 'delhi'), (12, 'shiv prakash', 'shiv12@gmail.com', '24', 'kolkata'), (13, 'dileep', 'dileep231@gmail.com', '27', 'kolkata');
Create MySql Connection in PHP
For importing and exporting database in MySql will make a separate file ‘dbCongif.php’. Add the following code and replace the database credentials with yours. You can find your db credentials in Application Access details:
dbCongif.php
<?php
// Database configuration
$dbHost = "localhost";
$dbUsername = "root";
$dbPassword = "";
$dbName = "testing";
// Create database connection
$con = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);
// Check connection
if ($con->connect_error) {
die("Connection failed: " . $con->connect_error);
}
?>Creating HTML Page
In this page you will create select box and jquery AJAX script for the show users city related details.
index.php
<!Doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>How to load records using Select option with jQuery AJAX in PHP MySQL</title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.0/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.16.0/umd/popper.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.0/js/bootstrap.min.js"></script>
</head>
<body>
<div class="container" style="margin-top: 50px;">
<h2 class="text-center">Load records using Select option with jQuery AJAX in PHP </h2>
<div class="row">
<div class="col-md-4"></div>
<div class="col-md-4" style="margin-top:20px; margin-bottom:20px;">
<form id="submitForm">
<div class="form-group">
<select class="form-control city" name="city" id="city">
<option value="">Select City</option>
<?php
// include database connectity file
include_once "dbConfig.php";
$query = "SELECT DISTINCT city_name FROM users";
$result = $con->query($query);
if ($result->num_rows > 0 ) {
while ($row = $result->fetch_assoc()) {
$cityName = ucwords($row['city_name']);
echo "<option value='$cityName'>$cityName</option>";
}
}
?>
</select>
</div>
</form>
</div>
</div>
<div class="col-md-12">
<div id="show-city">
</div>
</div>
</div>
</body>
</html>jQuery AJAX Script
<!---jQuery ajax load rcords using select box --->
<script type="text/javascript">
$(document).ready(function(){
$(".city").on("change", function(){
var cityname = $(this).val();
if (cityname !== "") {
$.ajax({
url : "display.php",
type:"POST",
cache:false,
data:{cityname:cityname},
success:function(data){
$("#show-city").html(data);
}
});
}else{
$("#show-city").html(" ");
}
})
});
</script>Display data when change drop-down
display.php
<?php
// include database connection file
include_once "dbConfig.php";
// load records using select box jquery ajax in PHP
$city_name = $_POST['cityname'];
$query = "SELECT * FROM users WHERE city_name = '$city_name'";
$result = $con->query($query);
$output = "";
if ($result->num_rows > 0) {
$output .= "<table class='table table-hover table-border'>
<thead>
<tr>
<th>Name</th>
<th>Email</th>
<th>Age</th>
<th>City</th>
</tr>
</thead>";
while ($row = $result->fetch_assoc()) {
$output .= "<tbody>
<tr>
<td>{$row['name']}</td>
<td>{$row['email']}</td>
<td>{$row['age']}</td>
<td>{$row['city_name']}</td>
</tr>
</tbody>";
}
$output .= "</table>";
echo $output;
}else{
echo "No records found";
}
?>Output
You can always support by sharing on social media or recommending my blog to your friends and colleagues. If you have any suggestions / problems about this tutorial, please comment on the form below.😊
