Autocomplete Search box in PHP and MySQL

by

AlsoAutocomplete Search in PHP is the most useful & user-friendly feature for searching for something through the 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 an option list when 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 straightforward to understand & learn quickly. If you read the complete points of this tutorial, you will implement it directly in your project.

autocomplete search in php

Autocomplete Search in PHP Using jQuery MySQL

You should also learn the following examples –

1. Create a Folder Structure

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

autocomplete/
    |__form.php
    |__style.css
    |__database.php
    |__script.php
    |__custom.js

2. Create MySQL Database and Table

Create a database ( codingstatus ) using the following query –

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

3. Set up Database Connection

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 a Search Input Box

This HTML document creates a webpage with an autocomplete search form in PHP, using MySQL and jQuery, including necessary resources and styling.

File Name – 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="custom.js"></script>
 
</body>
</html>

Explanation –

  • This HTML document sets up a webpage with a form for an autocomplete search in PHP using MySQL and jQuery.
  • It includes a meta tag for character set and viewport configuration.
  • The page title is set to “Autocomplete search in PHP.”
  • A stylesheet link is provided for a CSS file named “style.css.”
  • The form contains an input field with the id “tutorial_name” and a submit button.
  • External resources include jQuery and jQuery UI for autocomplete functionality, and a custom JavaScript file named “frontend-script.js” is included.
  • The jQuery UI CSS theme is linked for styling.
  • The script tags include jQuery and jQuery UI script sources.
  • The document has a heading “AutoComplete Search In PHP MYSQL & jQuery.”
  • The form captures user input for a tutorial name and has a submit button.
  • The input field has an associated div with the class “autocomplete-container” to facilitate autocomplete functionality.
  • The form submits data using the post method.
  • The JavaScript file “frontend-script.js” likely contains code for handling autocomplete functionality.

4. jQuery Autocomplete Search Script

This jQuery code initializes an autocomplete feature on the input field with the id “tutorial_name,” sourcing suggestions from a PHP script named “backend-script.php.”

File Name – custom.js

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

5. PHP Search Script

This PHP script retrieves a search term from the GET parameter, queries a MySQL database for tutorials matching the term, and returns the results as a JSON-encoded array with id and tutorial_name.

File Name – 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);
?>

Explanation –

  • This PHP script includes a database connection from “database.php.”
  • It retrieves the search term from the GET parameter “term.”
  • The script constructs a SQL query to select records from the “tutorials” table where the “tutorial_name” column contains the search term.
  • The query is executed using the database connection, and the result is stored in the variable $result.
  • If there are rows in the result, an empty array named $tutorialData is initialized.
  • A while loop iterates through the result set, creating an associative array for each row with ‘id’ and ‘value’ keys.
  • Each array is added to the $tutorialData array.
  • Finally, the script echoes the JSON-encoded $tutorialData array containing the id and tutorial_name values for matching records.

 

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

Comments are closed.