Ajax Search With MySQL, PHP And JQuery. ~ Tech Blog

Thursday, 9 August 2018

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:

1

2

3

4

5

CREATE TABLE IF NOT EXISTS `employees` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3 ;

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:

1

2

3

INSERT INTO `employees` (`id`, `name`) VALUES

(1, 'Michael Doyle'),

(2, 'Sandra Jones');

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:

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

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

<!DOCTYPE html>

<html lang="en">

<head>

    <meta charset="UTF-8">

    <title>Ajax MySQL search with PHP and JQuery</title>

</head>

<body>

    <!-- Text input to enter the employee's name -->

    <input type="text" id="employee_name"><br>

    <!-- Our search button -->

    <input type="button" id="search_button" value="Search">

    <!-- This div will contain a list of all employee names that match our search term -->

    <div id="search_results" style="padding:5px;"></div>

<!-- JQuery library -->

<script src="https://code.jquery.com/jquery-1.12.4.min.js"></script>

<script>

    //Add a JQuery click listener to our search button.

    $('#search_button').click(function(){

        //If the search button is clicked,

        //get the employee name that is being search for

        //from the search_box.

        var employee_name = $('#employee_name').val().trim();

        //Carry out a GET Ajax request using JQuery

        $.ajax({

            //The URL of the PHP file that searches MySQL.

            url: 'search.php',

            data: {

                name: employee_name

            },

            success: function(returnData){

                //Set the inner HTML of our search_results div to blank to

                //remove any previous search results.

                $('#search_results').html('');

                //Parse the JSON that we got back from search.php

                var results = JSON.parse(returnData);

                //Loop through our employee array and append their

                //names to our search results div.

                $.each(results, function(key, value){

                    //The name of the employee will be present

                    //in the "name" property.

                    $('#search_results').append(value.name + '<br>');

                });

                //If no employees match the name that was searched for, display a

                //message saying that no results were found.

                if(results.length == 0){

                    $('#search_results').html('No employees with that name were found!');

                }

            }

        });

    });

</script>

</body>

</html>

Here is how the form above works:

The user enters an employee name into the text input field and clicks on the search button.
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”.
Our PHP script searches our MySQL database and returns any matching results.
As soon as the Ajax request has been completed successfully, we loop through the results and append them to a div.
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.

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

39

40

41

<?php

//MySQL username.

$dbUser = 'root';

//MySQL password.

$dbPassword = '';

//MySQL host / server.

$dbServer = 'localhost';

//The MySQL database your table is located in.

$dbName = 'test';

//Connect to MySQL database using PDO.

$pdo = new PDO("mysql:host=$dbServer;dbname=$dbName", $dbUser, $dbPassword);

//Get the name that is being searched for.

$name = isset($_GET['name']) ? trim($_GET['name']) : '';

//The simple SQL query that we will be running.

$sql = "SELECT `id`, `name` FROM `employees` WHERE `name` LIKE :name";

//Add % for wildcard search!

$name = "%$name%";

//Prepare our SELECT statement.

$statement = $pdo->prepare($sql);

//Bind the $name variable to our :name parameter.

$statement->bindValue(':name', $name);

//Execute the SQL statement.

$statement->execute();

//Fetch our result as an associative array.

$results = $statement->fetchAll(PDO::FETCH_ASSOC);

//Echo the $results array in a JSON format so that we can

//easily handle the results with JavaScript / JQuery

echo json_encode($results);

In the code above, we:

Connect to our MySQL database using PDO.
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”.
Any rows that match the user’s search query are returned as an associative array.
We convert our associative array into a JSON format and print it out.
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):

Ajax requests with JQuery: This will help you to better understand how Ajax requests are carried out.
Connecting to MySQL with PDO: Shows you how to connect to a MySQL database using the PDO object.
Selecting rows with PDO: A guide that shows you how to select rows from a MySQL table using the PDO object.
Simple Ajax / PHP search: An even simpler tutorial that doesn’t involve any databases.

Tech Blog

Thursday, 9 August 2018