Autocomplete Search in PHP Using jQuery MySQL

Autocomplete Search in PHP is the most useful & user-friendly features for searching something through Input Box. It suggests options related to user input text. It will quickly begin to display the related options while the user begins entering something into the text field. So, You can easily select the required options from the suggested dropdown options.

Why Need it – Suppose that the user has to enter some required data but he/she does not remember the exact name of the data, only able to enter some characters into the text field. but those characters may not be required or valid. In that situation, you have to use an autocomplete concept. So that the user can easily select exact valid data from the options list provided by the autocomplete search script. For Example – Google search also provides the options list while the user begins typing something in the google search box.

In this tutorial, I have shared the simple PHP script with MySQLi procedural MySQLi Object-oriented & PDO query. This script is very easy to understand & learn quickly. If you read the complete points of this tutorial, you will definitely implement it directly in your project.

autocomplete search in php

How to Create Autocomplete Search in PHP

Before starting coding, Create the project folder like the following structure. Otherwise, use the given script directly in your existing project folder.

autocomplete/
    |__autocomplete-search-form.php
    |__style.css
    |__database.php
    |__backend-script.php
    |__frontend-script.php

Read Also –

Dependent Dropdown Using Ajax & PHP

Change URL without Reloading Page Using Ajax

Load More Data on Infinite Scroll Using Ajax

 


1. Create MySQL Database and Table

Create a database ( codingstatus ) using the following query –

$query = "CREATE DATABASE codingstatus";

Create a table ( tutorials ) in MySQL database using the following query.

CREATE TABLE `tutorials` (
`id` int(10) UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
`tutorial_name` varchar(255) DEFAULT NULL,
`created_at` timestamp(6) DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

2. Connect PHP to MySQL Database

Use the following script to connect the autocomplete search to the MySQL database. Also, Replace the existing connection details with your own.

File Name – database.php

<?php 
// Database configuration 
$dbHost     = "localhost";  //  your hostname
$dbUsername = "root";       //  your table username
$dbPassword = "";          // your table password
$dbName     = "codingstatus";  // your database name
 
// Create database connection 
$conn = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName); 
 
// Check connection 
if ($conn->connect_error) { 
    die("Connection failed: " . $conn->connect_error); 
} 



?>

3. Create Search Input Box Using HTML

To create a search input box, You should configure the following points –

  • Include an external CSS file style.css. Don’t worry, code of this file is given in step – 4
  • Also, Include jQuery CDN jQuery Library CDNjQuery UI library
  • Include External js file frontend-script.js. Don’t worry, It will explain in step – 5
  • Write the HTML code to create an autocomplete search form.

File Name – autocomplete-search-form.php

<!DOCTYPE HTML>
<html lang="en">
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <title>Autocomplete search in PHP</title>
  <link rel="stylesheet" href="style.css">
  
</head>
<body>
<h2>AutoComplete Search In PHP MYSQL & jQuery</h2>
<br><br>

 <form method="post">
  <div class="autocomplete-container" style="width:300px;">
    <input  type="text" id="tutorial_name" name="tutorial_name" placeholder="tutorial name">
  </div>
  <input type="submit" name="submit">
</form>

  <link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
  <script src="https://code.jquery.com/jquery-1.12.4.js"></script>
  <script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
  <script type="text/javascript" src="frontend-script.js"></script>
 
</body>
</html>

 

4. Customize Search Input Box Using CSS

Use the following CSS code to design an autocomplete search form. Otherwise, write your own custom CSS code.

File Name – style.css

.autocomplete-container {
  /*the container must be positioned relative:*/
  position: relative;
  display: inline-block;
}
html,body{
  overflow-x: hidden;
}
input {
  border: 1px solid transparent;
  background-color: #f1f1f1;
  padding: 10px;
  font-size: 16px;
}
input[type=text] {
  background-color: #f1f1f1;
  width: 94%;
}
input[type=submit] {
  background-color: DodgerBlue;
  color: #fff;
}
.autocomplete-items {
  position: absolute;
  border: 1px solid #d4d4d4;
  border-bottom: none;
  border-top: none;
  z-index: 99;
  /*position the autocomplete items to be the same width as the container:*/
  top: 100%;
  left: 0;
  right: 0;
}
.autocomplete-items div {
  padding: 10px;
  cursor: pointer;
  background-color: #fff;
  border-bottom: 1px solid #d4d4d4;
}
.autocomplete-items div:hover {
  /*when hovering an item:*/
  background-color: #e9e9e9;
}
.autocomplete-active {
  /*when navigating through the items using the arrow keys:*/
  background-color: DodgerBlue !important;
  color: #ffffff;
}
.autocomplete-list{
  position: absolute;
    box-shadow: 0px 20px 15px #1310100d;
    width: 100%;
    padding: 10px;
    line-height: 29px;
}

 

5. Create Autocomplete Search Script Using jQuery UI

Remember that the following script will not work until you include the jQuery UI library. This library was included in step -3. Now configure the following points –

  • Declared autocomplete method with search input id  #tutorial_name
  • Assign backend-script.php file  to the object property source

When the user begins typing something in the search input #tutorial_name, This script will access the backend data from backend-script.php. Don’t worry, backend script will be explained in the next step

File Name – frontend-script.js

 $( function() {
    $( "#tutorial_name" ).autocomplete({
    source: 'backend-script.php'  
    });
});

 

6. Fetch Autocomplete search Text Using PHP & MySQL

To fetch autocomplete search text, You have to configure the following points –

  • Include database connection file database.php
  • Get the search input value using $_GET['term'] and assign it to a variable $searchTerm. Here the term is a parameter that will be generated by jquery UI script.
  • Write the MySQLi query with the LIKE operator.
  • Run the MySQLi query
  • If Search data is greater than zero in the database. fetch id & tutorial name and store it in array variable $tutorialData.
  • Convert value of $tutorialData into json formate using json_encode() and print it.

File Name – backend-script.php

include('database.php'); 

$searchTerm = $_GET['term'];
$sql = "SELECT * FROM tutorials WHERE tutorial_name LIKE '%".$searchTerm."%'"; 
$result = $conn->query($sql); 
if ($result->num_rows > 0) {
  $tutorialData = array(); 
  while($row = $result->fetch_assoc()) {

   $data['id']    = $row['id']; 
   $data['value'] = $row['tutorial_name'];
   array_push($tutorialData, $data);
} 
}
 echo json_encode($tutorialData);

 

Backend script in MySQLi procedural –

If you want to create an autocomplete search with the MySQLi procedural query, use the following code.

$searchTerm = $_GET['term']; 
$sql = "SELECT * FROM tutorials WHERE tutorial_name LIKE '%".$searchTerm."%'"; 
$result = mysqli_query($conn, $sql); 
$tutorialData = array(); 
if (mysqli_num_rows($result) > 0) {
 while($row = mysqli_fetch_assoc($result)) {
  $data['id'] = $row['id']; 
 $data['value'] = $row['tutorial_name'];
 array_push($tutorialData, $data);
 } }
 // Return results as json encoded array
 echo json_encode($tutorialData);

Backend script in PDO

If you want to create an autocomplete search with a PDO query, use the following code. But remember that the connection query of databse.php file will not work with PDO. You will have to create a PDO database connection query. Don’t worry, I have also shared this script with the PDO database connection.

$host = 'localhost';
$db   = 'codingstatus';
$user = 'root';
$pass = '';

$options = [
    \PDO::ATTR_ERRMODE            => \PDO::ERRMODE_EXCEPTION,
    \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
    \PDO::ATTR_EMULATE_PREPARES   => false,
 ];
 $dsn = "mysql:host=$host;dbname=$db";
 try {
      $pdo = new \PDO($dsn, $user, $pass, $options);
 } catch (\PDOException $e) {
      throw new \PDOException($e->getMessage(), (int)$e->getCode());
 }

 $searchTerm = $_GET['term'];
 $sql = "SELECT * FROM tutorials WHERE tutorial_name LIKE '%".$searchTerm."%'";
 $stmt = $pdo->query($sql);
 $tutorialData=array(); 
 while ($row = $stmt->fetch()) {
    $data['id']    = $row['id']; 
     $data['value'] = $row['tutorial_name'];
    array_push($tutorialData, $data);
 }
 echo json_encode($tutorialData);

 

Suggestions:

I have shared the above script in a smart way according to my working experience. Now you can easily implement an autocomplete search using PHP in your project.

If you have any doubts or suggestions related to this tutorial, you can ask me through the below comment box. I will reply as soon as possible.

Thanks For giving time on this tutorial…

Categories PHP

Leave a Comment