In this post, we are going to learn Ajax Live Data Search with a help of PHP, MySQLi and Ajax. In real-time most of the website and web applications providing content search option to make the application as most user-friendly. You may see this live search option at the top of the page in most of the social networking sites like Facebook, Twitter, LinkedIn and more. It helps us to find out our search-related content quickly from their database or storage and it will help the user to get the relevant content as by what they are given key search.
In this post, we are going to get the relevant content from a database and display into HTML page with the help of search key entered in a text box. Here we user MySQL LIKE and REGEXP Methods to filter and get the data from a database and return a JSON response to the requested page. It will support find from a database then we can get an instant result from a site without refresh a page.
MySQL Like option help us to find the data value with entire search key is present in given MySQL table column data but it will only get the data if entire searched content is present in specified columns. But MySQL REGEXP option helps us to get the data with the random key value from given searched key. REGEXP will split the given searched key value into many keys with help of black space and it will replace with ‘|’ pipe symbol.
This method is prepared by Ajax with Jquery and we can use Ajax HTTP Post function. With the assistant of this method it finds data on the database and sends back a result to front-end web page without page refresh. This functionality will give an amazing look to your site.
Step 1: Database and Connect Details
Here we going to get contention with MySQL database using PHP with MySQLi contention property. Using this contention object we can process the Live data serach using search key values entered by user.
|
1 2 3 4 5 6 7 |
CREATE TABLE `li_ajax_post_load` ( `post_id` int(11) NOT NULL, `post_title` varchar(250) NOT NULL, `post_desc` text NOT NULL, `status` int(11) NOT NULL, PRIMARY KEY (`post_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; |
and connection object,
|
1 2 3 4 5 6 7 8 9 10 |
<?php //db details $db_host = 'localhost'; $db_user = 'root'; $db_pass = ''; $db_name = 'li_demo'; //connect and select db $con = mysqli_connect($db_host, $db_user, $db_pass, $db_name); ?> |
Step 2: Make HTML page
Here we are going to make a user view page with the search option and search key text box to make a live search option into it and also display default showing data while page loading.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
<div class="panel-body"> <div class="form-group"> <label>Search Key:</label> <br/> <div class="radio-inline"> <label><input type="radio" value="single" name="search_type" checked>Single Key</label> </div> <div class="radio-inline"> <label><input type="radio" value="many" name="search_type">Random Key</label> </div> </div> <div class="form-group"> <label>Search Key:</label> <input type="text" name="search-key" id="search-key" class="form-control" placeholder="Search Key like 'Laravel, 'PHP', 'Auth', 'auto load', 'CRUD' ect.,"> </div> <div class="post-data-list"> <?php //get rows query $query = mysqli_query($con, "SELECT * FROM li_ajax_post_load ORDER BY post_id DESC"); //number of rows $rowCount = mysqli_num_rows($query); if($rowCount > 0){ while($row = mysqli_fetch_assoc($query)){ ?> <div class="li-post-group"> <h4 class="li-post-title"><?php echo ucfirst($row["post_title"]); ?></h4> <p class="li-post-desc"><?php echo ucfirst($row["post_desc"]); ?></p> </div> <?php } } ?> </div> </div> |
Step 3: Defile Ajax Script
In this part, we need to define a jQuery function to perform data live data search with Ajax Post call with parameters. It will trigger when a user enters the key value into the text box with Keyup Event call and Radia button Change event hit a call to function to the server to get the relevant data with JSON format and display procedures.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
$(document).ready(function(e){ $(document).on('keyup', '#search-key', function(e){ getPostList(); }); $(document).on('change', 'input[name="search_type"]', function(e){ getPostList(); }); var jqxhr = {abort: function () {}}; function getPostList(){ $search_key = $('#search-key').val(); $search_type = $("input[name='search_type']:checked").val(); //$('.load-post').show(); $('.post-data-list').html(''); jqxhr.abort(); jqxhr = $.ajax({ type:'POST', dataType: "json", url:'ajax_search.php', data:{ 'action':'showPost', 'search_key':$search_key , 'search_type':$search_type}, success:function(data){ $('.load-post').hide(); $.each(data, function(key, post){ $('.post-data-list').append('<div class="li-post-group">\ <h4 class="li-post-title">'+post.post_title+'</h4>\ <p class="li-post-desc">'+post.post_desc+'</p>\ </div>'); }); } }); } }); |
Step 4: PHP script to filter data
Here we define a what procedures need to happen while user hit request to the server-side page. It contains both LIKE and REGEXP options and will perform based on user value search type and return as JSON data to the requested page.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
<?php require_once('./include/mysqli_connect.php'); $actionName = $_POST["action"]; if($actionName == "showPost"){ $resultData = []; $search_key = $_POST["search_key"]; $search_type = $_POST["search_type"]; $searchKey = ''; if(!empty($search_key)){ if($search_type == 'single'){ $searchKey .= " where post_title like '%".$search_key."%' "; $searchKey .= " or post_desc like '%".$search_key."%' "; }else if($search_type == 'many'){ $searchKey .= " where post_title REGEXP '". str_replace(" ", "|", $search_key) ."' "; $searchKey .= " or post_desc REGEXP '". str_replace(" ", "|", $search_key) ."' "; } } //get rows query $query = "SELECT * FROM li_ajax_post_load ".$searchKey." ORDER BY post_id DESC "; $result = mysqli_query($con, $query); //number of rows $rowCount = mysqli_num_rows($result); if($rowCount > 0){ while($row = mysqli_fetch_assoc($result)){ $resultData[] = $row; } } echo json_encode($resultData); } ?> |
