I have created a few post about how to Search, Update and Delete data from a MySQL Database with PHP and PDO. Those tutorials are quite simple (but not dynamically enough, they don’t use Ajax), but they are very important to catch the flow of this tutorial if you have never worked with this technologies. The tutorials are in Spanish so if you really need them in English I will be glad to translate them.
Something that I would like to remark is that you can get the scripts at the end of the tutorial.
In order to create a nice look and feel to the page I have implemented the framework Boostrap, but if you don’t want to focus on the look and feel you only have to focus on the Form, the event to Fire the Ajax request and the server to process your request.
The next flow chart will show the path that we are going to cover to complete our Ajax search:
The HTML form
So the first thing that we must have is our form. Our form will contain a Label, Input, and a Button. We are not going to create a submit button because in order to add a nice style we need to use the button tag instead. The events Submit of the form and Click will be handle by jQuery.
| 1 2 3 4 5 | <form method="get"> <label for="name">Name</label> <input id="name" name="name" placeholder="Type the name" /> <button class="btnSearch">Search</button> </form> |
So this code will bring us the following form:
You will see that form in the picture looks nice, this is achieve by implementing the classes from Bootstrap, but in order to keep the a clean code in the tutorial I will avoid putting all the styles in the code, you can have access to all this in the source files that I will place down.
Adding jQuery Library
I will put the jQuery library at the bottom of the page. You can put it on the head tag since this is not a page with loaded content. Usually you use it at the bottom of the page when you want the page to load smoothly.
The Actions
We are going to handle the events with two jQuery functions (Submit and Click), this events are trigger with the search button and when the form is submitted (Since we don’t have a submit button the form will be submitted when the user press the key Enter)
| 1 2 3 4 5 6 7 | $('.btnSearch').click(function(){ // code goes here ! }); $('form').submit(function(e){ // code goes here ! }); |
The Ajax Request
Since we are going to make the Ajax request either on the Submit or Click event we have to reduce our code by creating a function. This function will handle the request for both events.
| 1 2 3 4 5 6 7 8 9 10 | function makeAjaxRequest() { $.ajax({ url: 'php/search.php', type: 'get', data: {name: $('input#name').val()}, success: function(response) { $('table#resultTable tbody').html(response); } }); } |
It is important to notice that from the server we get a response which will be use to show the result in a Table. Initialy we will have a table like this:
The structure of our table is:
| 1 2 3 4 5 6 7 8 9 10 11 | <table id="resultTable"> <thead> <tr> <th>Id</th> <th>Name</th> <th>Email</th> <th>Telephone</th> </tr> </thead> <tbody></tbody> </table> |
As you can see I left the tag <tbody> empty, this tag will be filled with jQuery, so to review the jQuery Ajax properties this is a simplyfied explanation:
url: Is the path where we are sending the data.
type: How are we sending the data, post/get?
data: The data to be send in a query string format or JSON which is our case.
success: A callback function to be executed is the request was succesful. A ‘response’ variable will be returned.
Because I left the tag <tbody> with no data it will be fill with ‘response’. To achieve this we us jQuery html() method to add HTML code to this matched selector. The body of <tbody> will be a table structure created by the server. After we fill the table it will look like this:
Creation of Our Table (employee)
With this script we create and insert data into our table in MySQL:
| 1 2 3 4 5 6 7 8 9 10 11 12 | CREATE TABLE IF NOT EXISTS `employee` ( `employee_id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(85) NOT NULL, `email` varchar(45) NOT NULL, `telephone` varchar(20) NOT NULL, PRIMARY KEY (`employee_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ; INSERT INTO `employee` (`employee_id`, `name`, `email`, `telephone`) VALUES (1, 'Dennis Ritchie', 'dritchie@bell.com', '555-154-8745'), (2, 'Ken Thompson', 'kthompson@bell.com', '555-154-1234'), (3, 'Steve Jobs', 'sjobs@apple.com', '751-121-8124'); |
Server Side ‘search.php’ (PHP)
Now in our server side we have to do a few steps to get our Information:
- Create a Database Connection to MySQL with PDO.
- Verify that the information is coming from a GET request.
- Create the Query.
- Prepare the query with the parameters.
- Bind and Execute the Query.
- Fetch all the records.
- Validate results.
- If there are results create the structure of a HTML table to fill the <tbody> in the client side.
So the code that execute all the steps is:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | require_once 'Connection.simple.php'; $conn = dbConnect(); if (isset($_GET['name'])) { $data = "%".$_GET['name']."%"; $sql = 'SELECT * FROM employee WHERE name like ?'; $stmt = $conn->prepare($sql); $results = $stmt->execute(array($data)); $rows = $stmt->fetchAll(); } if(empty($rows)) { echo "<tr>"; echo "<td colspan='4'>There were not records</td>"; echo "</tr>"; } else { foreach ($rows as $row) { echo "<tr>"; echo "<td>".$row['employee_id']."</td>"; echo "<td>".$row['name']."</td>"; echo "<td>".$row['email']."</td>"; echo "<td>".$row['telephone']."</td>"; echo "</tr>"; } } |
With all these steps we can get our nice jQuery Ajax Search Application, at the end we add a nice touch to our application with Bootstrap to make it look more professional.
