In this tutorial we'll be using jQuery to perform a search on a mysql table without waiting for a new page to load. First of all we're going to build the php document which will be used by JQuery to perform the search.
Click here to view a working demo
The way it will work is the keywords will be posted to the php document and any thing that is outputted on the php document is what data will be returned, So we just need to make a simple search script with php.
$link = mysql_connect("localhost","user","password");
mysql_select_db("database", $link);
To start with we make the connection to the database, Now we want to grab the data which has been posted to this document, escape it for security reasons and then run a query to see if there are any matches.
$link = mysql_connect("localhost","root","");
mysql_select_db("td_search", $link);
$keywords = mysql_real_escape_string( $_POST["keywords"] );
$query = mysql_query("SELECT * FROM wp_posts WHERE post_content LIKE '%". $keywords ."%' && post_status = 'publish'");
This script is now running a query on the wordpress posts table on my localhost for any posts with what ever the value of $keywords is. Finally all we need to do with this script is loop through the results adding each of them to an array and then outputting the array json encoded.
$link = mysql_connect("localhost","root","");
mysql_select_db("td_search", $link);
$keywords = mysql_real_escape_string( $_POST["keywords"] );
$query = mysql_query("SELECT * FROM wp_posts WHERE post_content LIKE '%". $keywords ."%' && post_status = 'publish'");
$arr = array();
while( $row = mysql_fetch_array ( $query ) )
{
$arr[] = array( "id" => $row["ID"], "title" => $row["post_title"], "content" => $row["post_content"] );
}
echo json_encode( $arr );
So this is pretty straight forward, if you have worked with MySQL at a basic level and 3 dimensional arrays you should understand whats going on here. So save this document as search.php and create a new blank file.
Here we have all our basic html tags to structure the page and we've loaded the jquery library, You can download the latest version of Jquery from http://jquery.com/. In the body of the page we need an input field and a submit field as well as an empty div.
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>jQuery Search Demonstration</title>
<script type="text/javascript" src="jquery.js"></script>
</head>
<body>
<input type="text" name="search" class="keywords">
<input type="submit" name="submit" class="search">
<div id="content">
</div>
</body>
</html>
Notice that I've given the input field and submit field classes called keywords and search, This is very important as this is how we will identify them later on I have also given the div the ID content. If you've never worked with Jquery before don't worry as its an extremely easy to use and powerful javascript library.
$(document).ready(function(){
});
We will need to have all of our code within this function.
$(document).ready(function(){
$(".search").click(function(){
});
});
Now when ever the object with the class search is clicked it will run this function so within this function we want to have our ajax code which will interact with search.php.
$.post( url, data, callback, type );
The jQuery function we are going to use is .post you can see what the parameters are above so the url is going to be search.php, the data will be what ever the value of our input box is and for the call back we are going to use a function to get it to write the data to the div we created with the id content.
$(document).ready(function(){
$(".search").click(function(){
$.post("search.php", { keywords: $(".keywords").val() }, function(data){
$("div#content").empty()
$.each(data, function(){
$("div#content").append("- <a href='post.php?id=" + this.id + "'>" + this.title + "</a>");
});
}, "json");
});
});
As you will see the first parameter has been set to search.php so this is where our data will be posted to, the second parameter we have created a javascript array containing one entity with a key keywords and the value will be the value or our input field with the class keywords. The third parameter is a function which the data from search.php will be returned to, In this function we have emptied the content div encase there was any content in it and then looped through the data we received and added each of them to the content div and finally the fourth parameter has been set to json which is the type of data we are receiving from search.php. Have a good look through the javascript because its not as complicated as it may look at first.
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>jQuery Search Demonstration</title>
<script type="text/javascript" src="jquery.js"></script>
<script type="text/javascript">
$(document).ready(function(){
$(".search").click(function(){
$.post("search.php", { keywords: $(".keywords").val() }, function(data){
$("div#content").empty()
$.each(data, function(){
$("div#content").append("- <a href='post.php?id=" + this.id + "'>" + this.title + "</a>");
});
}, "json");
});
});
</script>
</head>
<body>
<input type="text" name="search" class="keywords">
<input type="submit" name="submit" class="search">
<div id="content">
</div>
</body>
</html>
This is how our final document should look, So save this in the same directory as search.php and save it as index.php. Run the file on your local server and you should have a fully functional ajax search.
Click here to view the jQuery search demo
Here are a few links to jQuery documentation which will help you to understand the functions used in this tutorial better.