Autocomplete Search Using PHP, PDO - MySQL & Ajax

Share this post:

In this post, I'm going to show you how to make Auto Complete Search Using Bootstrap 4, PHP, PDO - MySQL and Ajax. I'm using Bootstrap 4 to design the form, using jQuery - Ajax to send the HTTP request to the server and using PHP as the server-side language with MySQL database. In this project, I'll not only show you how to make autocomplete search but also when we click on the searched result then first, We'll set the searched result in the search input field and when We'll click on the search button then We'll redirect to other page containing some information about the searched term.

Here I've taken an example of country name search, so when I enter any country name in the search field then according to the entered characters, the list of matching countries will be displayed in the dropdown. And when I click on any country name then it sets in the input field and after when I click on the search button then it goes to another page containing some information about the country.

Okay So, I'll show you how to make this project step by step. If you follow all the steps properly then you will easily understand the concept of Autocomplete Search.

Step 1:

In this step, we'll set up our database and table for this project. So first We'll create a database using PHPMyAdmin and name it autocomplete_db and then for the table I'm providing a SQL file that contains records of countries, you can download it from here. Now next We've to import this SQL file into the database that We've just created. So, for this just click on your database name i.e., autocomplete_db and then click on the Import tab then click on browse file, then select the downloaded file and then click on go. When your SQL file runs successfully then a new table has been created with name countries. Okay, so our database is now ready for the project.

Step 2:

Now in this step, we'll create our project folder inside the htdocs or www folder and name it auto complete, once the project folder is created just open the folder inside your favourite code editor.

Step 3:

In this step, we'll create a new file index.php inside the root of the project directory. Now in this file, we'll design our form using Bootstrap 4. So, for this, you don't have to type the codes just copy the below codes and paste into your index.php file and save them.

<!DOCTYPE html>
<html lang="en">

<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <meta http-equiv="X-UA-Compatible" content="ie=edge">
  <title>AutoComplete Search Using Bootstrap 4, PHP, PDO - MySQL & Ajax</title>
  <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.1/css/bootstrap.min.css">

</head>

<body class="bg-info">
  <div class="container">
    <div class="row mt-4">
      <div class="col-md-8 mx-auto bg-light rounded p-4">
        <h5 class="text-center font-weight-bold">AutoComplete Search Using Bootstrap 4, PHP, PDO - MySQL & Ajax</h5>
        <hr class="my-1">
        <h5 class="text-center text-secondary">Write any country name in the search box</h5>
        <form action="details.php" method="post" class="p-3">
          <div class="input-group">
            <input type="text" name="search" id="search" class="form-control form-control-lg rounded-0 border-info" placeholder="Search..." autocomplete="off" required>
            <div class="input-group-append">
              <input type="submit" name="submit" value="Search" class="btn btn-info btn-lg rounded-0">
            </div>
          </div>
        </form>
      </div>
      <div class="col-md-5" style="position: relative;margin-top: -38px;margin-left: 215px;">
        <div class="list-group" id="show-list">
          <!-- Here autocomplete list will be display -->
        </div>
      </div>
    </div>
  </div>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
  <script src="script.js"></script>
</body>

</html>

In the codes above you can see I've included Bootstrap 4 CDN CSS link, jQuery CDN link and I've also linked a script.js. In the script.js file, we'll write our Ajax code.

Now let's check the design of the form so for this just go to your browser and type localhost/auto-comp. If everything is fine, then you will see a form like in the below image:

Step 4:

Now in this step, we'll create another file in the root of the project directory i.e., config.php file. In this file, we'll write a PHP script for connection to the database using MySQL PDO (PHP Data Output). Just copy the below codes and paste them into your config.php file.

<?php
  const DBHOST = 'localhost';        // Database Hostname
  const DBUSER = 'root';             // MySQL Username
  const DBPASS = '';                 // MySQL Password
  const DBNAME = 'autocomplete_db';  // MySQL Database name

  // Data Source Network
  $dsn = 'mysql:host=' . DBHOST . ';dbname=' . DBNAME . '';

  // Connection Variable
  $conn = null;

  // Connect Using PDO (PHP Data Output)
  try {
    $conn = new PDO($dsn, DBUSER, DBPASS);
    $conn->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
  } catch (PDOException $e) {
    die('Error : ' . $e->getMessage());
  }
?>

Step 5:

In this step, we'll create another new file in the root of the project directory i.e., script.js file. In this file, we'll write the jQuery - Ajax codes to send entered search term from the input field to the server where We'll process and send back the response of the matching list to the client from the database. Just copy the below codes and paste them into the script.js file and save.

$(document).ready(function () {
  // Send Search Text to the server
  $("#search").keyup(function () {
    let searchText = $(this).val();
    if (searchText != "") {
      $.ajax({
        url: "action.php",
        method: "post",
        data: {
          query: searchText,
        },
        success: function (response) {
          $("#show-list").html(response);
        },
      });
    } else {
      $("#show-list").html("");
    }
  });
  // Set searched text in input field on click of search button
  $(document).on("click", "a", function () {
    $("#search").val($(this).text());
    $("#show-list").html("");
  });
});

In the above codes, you can see We're sending the request to the action.php file. So, we have to create another file and there We've to handle this ajax request. So, let's go to the next step.

Step 6:

In this step, we'll create another new file in the root of the project directory i.e., the action.php file. In this file, we'll write the logic to get data from the database according to the searched term. So just see the below codes, you can copy the below codes and paste them into the action.php file.

<?php
  require_once 'config.php';

  if (isset($_POST['query'])) {
    $inpText = $_POST['query'];
    $sql = 'SELECT country_name FROM countries WHERE country_name LIKE :country';
    $stmt = $conn->prepare($sql);
    $stmt->execute(['country' => '%' . $inpText . '%']);
    $result = $stmt->fetchAll();

    if ($result) {
      foreach ($result as $row) {
        echo '<a href="#" class="list-group-item list-group-item-action border-1">' . $row['country_name'] . '</a>';
      }
    } else {
      echo '<p class="list-group-item border-1">No Record</p>';
    }
  }
?>

In the above coding you can see first We're including the config.php file then using a condition we're getting the searched term that is sent via the ajax. Here We're using a prepared statement. After this step our Autocomplete, Search is almost ready you can test it now. Below is a screenshot of how it looks when we enter any characters in the input field.

Step 7:

In this step, we'll create another new file in the root of the project directory i.e., details.php file. In this file, we'll create a page where We'll display some information regarding searched country. Okay so just copy the below codes and paste them into the details.php file and save.

<?php
  require_once 'config.php';

  if (isset($_POST['submit'])) {
    $countryName = $_POST['search'];

    $sql = 'SELECT * FROM countries WHERE country_name = :country_name';
    $stmt = $conn->prepare($sql);
    $stmt->execute(['country_name' => $countryName]);
    $row = $stmt->fetch();
  } else {
    header('location: .');
    exit();
  }
?>
<!DOCTYPE html>
<html lang="en">

<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>Details</title>
  <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.1/css/bootstrap.min.css">
</head>

<body>
  <div class="container">
    <div class="row mt-5">
      <div class="col-5 mx-auto">
        <div class="card shadow text-center">
          <div class="card-header">
            <h1><?= $row['country_name'] ?></h1>
          </div>
          <div class="card-body">
            <h4><b>Country Code :</b> <?= $row['country_code'] ?></h4>
            <h4><b>City :</b> <?= $row['city'] ?></h4>
            <h4><b>Country ID :</b> <?= $row['id'] ?></h4>
          </div>
        </div>
      </div>
    </div>
  </div>
</body>

</html>

Here in the above image, you can see some details of the country when we click on the search button after setting the country in the input field.

Okay, So the project is finished now. If you have any issues regarding this project, then comment down below. And if you liked this post then share this post with your friends. If you want to learn more advanced concepts in Web Development, then you can just go to my YouTube channel and watch my videos. Here is the link to my YouTube channel.

Share this post: