Wednesday, December 21, 2016
Database Ajax Instant Search and Show Suggestions JQuery, PHP, MySQL Tutorial
database connectivity
,
database query like
,
instant search suggestions
,
javascript
,
jquery code
,
mysql database tutorial
,
mysqli
,
php
,
request and submit data php without using form
What is it about?
This is part of my sample database project about social networking for researchers. Most web sites like Google, Quora, Facebook show some instant suggestion using some complex algorithm some requested data from their database.The main focus of this tutorial is to show some data upon search key entered on the search box. The objective is to get the data upon request and show it to user without reloading the page. I hope to make the all the project codes, diagram and documents available once it has reached some maturity. Many parts of the project are example snippet published by others. For now I will just show bits that are needed for this post.
View of the project:
Code Snippet:
HTML:
This below is the code for the search bar using bootstrap grid system 12 columns.
<div class="col-xs-12">
<div class="input-group stylish-input-group">
<input id="searchBar" type="text" class="form-control" placeholder="Search for...">
<span class="input-group-addon">
<button type="submit">
<span class="glyphicon glyphicon-search"></span>
</button>
</span>
<div class="col-xs-12" id="result" class="searchBarResults"></div>
</div><
</div>
JQuery:
When pressing any key on search bar it will instantly get that value. Next performs an ajax request to the database using backend-search.php. Upon success of failure it gets the data returned adds to the html dom.The next code sets the value of the search bar as a clicked result.
$(document).ready(function(){
$('#searchBar').on("keyup input", function(){
/* Get input value on change */
var term = $(this).val();
var resultDropdown = $("#result");
if(term.length){
$.get("backend-search.php", {query: term}).done(function(data){
// Display the returned data in browser
resultDropdown.html(data);
});
} else{
resultDropdown.empty();
}
});
// Set search input value on click of result item
$("#result").on("click", "p", function(){
$("#searchBar").val($(this).text());
$("#result").empty();
});
});
PHP:
The code below establishes connection with mysql database. Here, root is login name for mysql and dbtest is the name of the database. Do not forget to put limit on the query, I did not do it since I did have much data when I started."SELECT * FROM users WHERE userName LIKE '" . $query . "%'"
The code above is most important. It quries a table named user, there it gets all the column( not needed here ), for which the query starts with input pattern. Next it gets all the rows and sends the results.
<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$link = mysqli_connect("localhost", "root", "", "dbtest");
// Check connection
if($link === false){
die("ERROR: Could not connect. " . mysqli_connect_error());
}
// Escape user inputs for security
$query = mysqli_real_escape_string($link, $_REQUEST['query']);
if(isset($query)){
// Attempt select query execution
$sql = "SELECT * FROM users WHERE userName LIKE '" . $query . "%'";
if($result = mysqli_query($link, $sql)){
if(mysqli_num_rows($result) > 0){
while($row = mysqli_fetch_array($result)){
echo "
" . $row['userName'] . "
"; } // Close result set mysqli_free_result($result); } else{ echo "No matches found for $query
"; } } else{ echo "ERROR: Could not able to execute $sql. " . mysqli_error($link); } } // close connection mysqli_close($link); ?>
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment