Live Search in PHP Mysqli using jQuery AJAX


Live Search in PHP Mysqli using jQuery AJAX

Hello Friends, In this post we are going to learn Live search in PHP Mysqli using jQuery AJAX.On most websites, we see a search bar on the website and we can search for content within it. If you use Facebook or Twitter, there is an outstanding direct search feature that you can use to find new friends or followers. Suppose we enter text on this site to find it. After that, we can get instant results from the site without refreshing the page. This function is performed by Ajax with Jquery. With the help of Jquery we can use the Ajax http dom function . Using this function, it searches data from the server and sends the results back to the front end of the website without refreshing the page. This function gives your website a stunning display. I have provided the source code with this post. I hope you learn these things.

Output
Live Search in PHP Mysqli using jQuery AJAX

Creating Database Table

--
-- Database: `php_ajax_testing`
--

-- --------------------------------------------------------

--
-- Table structure for table `students`
--

CREATE TABLE `students` (
  `id` int(50) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `first_name` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `password` varchar(100) NOT NULL,
  `city_name` varchar(100) NOT NULL,
  `otp` int(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `students`
--

INSERT INTO `students` (`id`, `first_name`, `last_name`, `email`, `password`, `city_name`, `otp`) VALUES
(36, 'manish', 'pushpakar', 'manishpushpakar007@gmail.com', '59c95189ac895fcc1c6e1c38d067e244', 'uttar pradesh', 161975),
(46, 'shiv ', 'shankar', 'shiv07@gmail.com', '59c95189ac895fcc1c6e1c38d067e244', 'uttar pradesh', 0),
(47, 'ramesh', 'kumar', 'ramesh007@gmail.com', '59c95189ac895fcc1c6e1c38d067e244', 'mumbai', 0),
(48, 'sanjay ', 'kumar', 'sanjay007@gmail.com', '59c95189ac895fcc1c6e1c38d067e244', 'delhi', 0),
(49, 'madan', 'kumar', 'madan007@gmail.com', 'fd68e8922a6705a916b19669fb356cce', 'delhi', 0),
(50, 'aman ', 'kumar', 'aman007@gmail.com', '59c95189ac895fcc1c6e1c38d067e244', 'mumbai', 0),
(51, 'manish', 'pushpakar', 'manis1319@gmail.com', '59c95189ac895fcc1c6e1c38d067e244', 'delhi', 0);

Database Connectivity

dbConfig.php
<?php
	// Database configuration 
	$dbHost     = "localhost"; 
	$dbUsername = "root"; 
	$dbPassword = ""; 
	$dbName     = "php_ajax_testing"; 
	 
	// Create database connection 
	$con = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName); 
	 
	// Check connection 
	if ($con->connect_error) { 
	    die("Connection failed: " . $con->connect_error); 
	}

    
?>

Search HTML Code and jQuery AJAX Code

index.php
<!doctype html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <title>Live Search in PHP Mysqli using jQuery AJAX</title>
  <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
  <link rel="stylesheet" href="directory/bootstrap.min.css">
  <script src="directory/bootstrap.min.js"></script>
  <script src="directory/jquery-3.4.1.js"></script>
</head>
  <body>
    <div class="container" style="margin-top: 50px;">
      <h2 style="text-align: center;"><b>Live Search in PHP Mysqli using jQuery AJAX</b></h2><br>
        <div class="row">
          <div class="col-md-2"></div>
            <div class="col-md-8 form-group">
              <input type="text" id="search" class="form-control" autocomplete="off" placeholder="Search here.."><br>
            </div>
          <div class="col-md-2"></div>
        </div>
      <div class="result">
        
      </div>
    </div>
  </body>
</html>

<!---jQuery ajax live search --->
<script type="text/javascript">
    $(document).ready(function(){
        // fetch data from table without reload/refresh page
        loadData();
        function loadData(query){
          $.ajax({
            url : "action.php",
            type: "POST",
            chache :false,
            data:{query:query},
            success:function(response){
              $(".result").html(response);
            }
          });  
        }

        // live search data from table without reload/refresh page
        $("#search").keyup(function(){
          var search = $(this).val();
          if (search !="") {
            loadData(search);
          }else{
            loadData();
          }
        });
    });
</script>

Search Page (action.php)

<?php
	// include database connection file

	include "dbConfig.php";

	// fetch data from student table..

	$output = "";
	if (isset($_POST['query'])) {
		$search = mysqli_real_escape_string($con, $_POST['query']);
		$sql = "SELECT * FROM students WHERE first_name LIKE '%$search%' || last_name LIKE '%$search%' || 
				email LIKE '%$search%' || city_name LIKE '%$search%'";
	}else{
		$sql = "SELECT * FROM students ORDER BY id DESC";
	}
	$query = mysqli_query($con, $sql);
	if (mysqli_num_rows($query) > 0) {
		$output .= "<table class='table table-hover table-striped'>
		<thead>
			<tr>
				<th>Id</th>
				<th>Firstname</th>
				<th>Lastname</th>
				<th>Email</th>
				<th>City Name</th>
			</tr>
		</thead>";
		while ($row = mysqli_fetch_assoc($query)) {
		$output .= "<tbody>
			<tr>
				<td>{$row['id']}</td>
				<td>{$row['first_name']}</td>
				<td>{$row['last_name']}</td>
				<td>{$row['email']}</td>
				<td>{$row['city_name']}</td>
			</tr>
			</tbody>";
		}
	$output .="</table>";
		echo $output;
	}else{
		echo "<h5>No record found</h5>";
	}
	
?>
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