How to load records using Select option with jQuery AJAX in PHP MySQL

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

How to load records using Select option with jQuery AJAX in PHP MySQL

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.😊

Leave a Reply

Your email address will not be published. Required fields are marked *