Ajax Live Data Search Using jQuery PHP MySQL
Last Updated : Mar 11, 2024
IN - PHP MySQL jQuery Ajax | Written & Updated By - Amruta
In this tutorial we will show you the solution of ajax live data search using jQuery PHP MySQL, here we needs to use some external open source library support files like ‘ajax,jquery,bootstrap’ for achieve live data search from mysql data.
The sql query of ‘LIKE’ help us to retrieve data for each input on search bar whatever user types then bootstrap will helps for show data’s properly on html table with responsively.
Step By Step Guide On Ajax Live Data Search Using jQuery PHP MySQL :-
Here first we need to define and execute database connection using mysqli_connect() method then that file saved as ‘config.php’.
In index.php file we included config php file once at top and we imported above seen all external libraries then in script block we declared load_data() method and we defined that method with query parameter.
We calling POST method defined in search.php file then accessing data from search file if data state success then we printing retrieved result on id ‘res’ html page using ajax.
If user searched anything on search bar the keyup event will loads that filtered data otherwise it loads all data in database table.
Using bootstrap we aligned all data on webpage responsively and properly.
In search.php file we checking whether query is set or not then we connecting with database.
We executing query using LIKE with ‘%’ symbol as we know that will represents any value like input will returns so we used that with all column values to find all type values in database table and its stored on variable ‘$query’.
Otherwise we selecting whole table data then returned. Al finally found result appended with html table and returned to index php file for display on webpage.
Config.php
<?php
$db_host = 'localhost';
$db_username = 'root';
$db_password = '';
$db_name = 'test';
$conn = mysqli_connect($db_host, $db_username, $db_password, $db_name);
if (mysqli_connect_errno()) {
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}?>
Index.php
<?php
include("config.php");
?>
<html>
<head>
<title>Live data search</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css" rel="stylesheet">
<script>
$(document).ready(function(){
load_data();
function load_data(query)
{
$.ajax({
url:"search.php",
method:"POST",
data:{query:query},
success:function(data)
{
$('#res').html(data);
}
});
}
$('#srch').keyup(function(){
var search = $(this).val();
if(search != '')
{
load_data(search);
}
else
{
load_data();
}
});
});
</script>
</head>
<body>
<div class="container-fluid">
<div class="content-wrapper">
<div class="container">
<h1>Ajax live data search using jquery php mysql</h1>
<div class="row">
<div class="col-xs-12">
<input type="text" name="search" id="srch" placeholder="Search" class="form-control" />
<div id="res"></div>
</div>
</div>
</div>
</div>
</div>
</body>
</html>
Search.php
<?php
require ('config.php');
$return = '';
if(isset($_POST["query"]))
{
$search = mysqli_real_escape_string($conn, $_POST["query"]);
$query = "SELECT * FROM employee
WHERE id LIKE '%".$search."%'
OR emp_name LIKE '%".$search."%'
OR email LIKE '%".$search."%'
OR phone LIKE '%".$search."%'
";}
else
{
$query = "SELECT * FROM employee";
}
$result = mysqli_query($conn, $query);
if(mysqli_num_rows($result) > 0)
{
$return .='
<div class="table-responsive">
<table class="table table bordered">
<tr>
<th>ID</th>
<th>Emp Name</th>
<th>Email</th>
<th>Phone</th>
<th>Created Date</th>
</tr>';
while($row1 = mysqli_fetch_array($result))
{
$return .= '
<tr>
<td>'.$row1["id"].'</td>
<td>'.$row1["emp_name"].'</td>
<td>'.$row1["email"].'</td>
<td>'.$row1["phone"].'</td>
<td>'.$row1["created_date"].'</td>
</tr>';
}
echo $return;
}
else
{ echo 'No results found.'; }?>- A php script can be placed anywhere in the document. A php script starts with <?php and end with ?>.
- The default file extension for php files is “.php” and php statements end with ‘;’ semicolon.
- Here we defined ‘host name- $db_host, user name- $db_username, password- $db_password, database name- $db_name’ with respective values ‘localhost,root,’’,test’ for make connection with server database.
- Using mysqli_connect() method we executed database connection with those values and connection referred by variable ‘$conn’ and this file named as config.php.
- In index.php file at first included config.php file for access database values. Here we imported some external files as we seen earlier then in script block we defined post method for retrieved result display on webpage by ajax.
- This ajax will help us for interact with search.php file then returned success data will appended on html page id ‘res’. There we defined search box input element if user type anything on search bar load_data method will displays searched related row in table otherwise it displays whole table with contents.
- Using bootstrap we aligned our page properly and responsively. In search.php file we specified config.php file for interact with database ‘test’ table ‘employee’.
- Using isset we checking whether query is set or not then within that we executing database connection with query values and it will stored on variable ‘$search’.
- The $query variable used for store sql query with LIKE, % specified represents select data whereas user entered word like whether with those ‘id, emp_name,email,phone’ columns values in table ‘employee’.
- Then collect that row values stored on variable ‘$query’ otherwise it executes query to select all table data. Using ‘mysqli_query()’ method we executing this with database ‘test’ for retrieve data it will stored on variable ‘$result’.
- If data present in database table then it will definitely had more than ‘0’ data’s so we printed using while loop in html table with respective column names otherwise we just printing message ‘No results found.’.
Conclusion :-
In conclusion now we are able to know how to do live data search using Ajax PHP MySQL.
When work with php we need to create and process php files at server location and then we need to start the server before execute the program.
When we executing this program on browser it will displays program heading ‘Ajax live data search using jQuery php MySQL’ and initially below that heading search bar and table ‘employee’ with whole data’s displayed when user types that like row data only filtered and shown if user entered unrelated data or letter then it displays ‘No results found’ message.
I hope this tutorial on ajax live data search using jQuery PHP MySQL helps you and the steps and method mentioned above are easy to follow and implement.
About Amruta
