Tutorial: Ajax search with MySQL, PHP and JQuery.

In this tutorial, I will show you how to create an Ajax search form using JQuery, PHP and MySQL. For the purposes of this tutorial, I will be creating an Ajax form that allows the user to search a database of employee names.

Our MySQL database.

We will start off by creating our employees table in MySQL:

The above SQL statement will create a simple table called employees. There are only two columns. The id column, which is a simple auto_increment primary key column and name, which is a VARCHAR column that will contain the employee’s name.

Let’s insert some test data so that we can search our MySQL database:

Feel free to add more employee names to the table if you wish.

Our Ajax HTML form.

Here is a simple Ajax search form that was built using HTML and JavaScript. Be sure to read the comments if you want to fully understand what is going on:

Here is how the form above works:

  1. The user enters an employee name into the text input field and clicks on the search button.
  2. Once the search button is clicked, our JQuery event handler carries out an Ajax request to search.php, which is where our PHP code will be located. The name that was entered into the text input field is sent to our PHP script via a GET parameter called “name”.
  3. Our PHP script searches our MySQL database and returns any matching results.
  4. As soon as the Ajax request has been completed successfully, we loop through the results and append them to a div.
  5. If no employees match that name, we display a message to the user.

Obviously, you can change things around and spruce up the appearance of the form. However, for the purpose of this tutorial, I am trying to keep things extremely simple.

Our PHP search script.

In this PHP script, we connect to MySQL using PDO and return any results that match the user’s search query. Be sure to pay attention to the comments.

In the code above, we:

  1. Connect to our MySQL database using PDO.
  2. We execute a prepared statement that selects rows from our employees table. In this particular example, we are using a LIKE clause with two wildcard operators. This is so that search queries such as “Jones” will return results such as “Sandra Jones”.
  3. Any rows that match the user’s search query are returned as an associative array.
  4. We convert our associative array into a JSON format and print it out.
  5. If no matches are found, $results will be an empty array.

Hopefully, you found this tutorial to be simple and straight-forward. If there are parts of this tutorial that you do not understand, then I suggest that you read the following articles, as they may help to fill any gaps in your knowledge (you may be trying to run before you can walk):

Comments

comments