Autocomplete Search box in PHP and MySQL

by

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

Autocomplete Search in PHP Using jQuery MySQL

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

Load More Data on Infinite Scroll Using Ajax

1. Create MySQL Database and Table

Create a database ( codingstatus ) using the following query –

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

Table Name – tutorials

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,
)

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, the code of this file is given in step – 4
  • Also, Include jQuery CDN jQuery Library CDN & jQuery 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. 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'  
    });
});

5. 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 the 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 an array variable $tutorialData.
  • Convert value of $tutorialData into JSON format using json_encode() and print it.

File Name – backend-script.php

<?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);
?>

 

My Suggestion

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 to this tutorial…

3 thoughts on “Autocomplete Search box in PHP and MySQL”

  1. You really should not use a direct query where you append the variable. It makes sql injection trivial. Here is a better way using a prepared statement:

    $stmt = $pdo->prepare(“SELECT FROM tutorials WHERE tutorial_name LIKE %:term%”);
    $stmt->execute([‘term’ => $searchTerm]);
    $results = $stmt->fetchAll(PDO::FETCH_COLUMN, 0);
    json_encode($results);

    Reply

Leave a Comment