Simple Autocomplete in PHP/MySQLi & jQuery UI Tutorial

Submitted by razormist on Tuesday, August 3, 2021 - 11:10.

In this tutorial we will create a Simple Autocomplete With MySQLi & jQuery using PHP. This code can be used for searching some data from database and some system that uses a large data. By using PHP, you can let your user directly interact with the script and easily to learned its syntax. It is mostly used by a newly coders for its user-friendly environment. jQuery is a cross-platform JavaScript library designed to simplify the client-side scripting of HTML.

So Let's do the coding.

Before we started:

First you have to download & install XAMPP or any local server that run PHP scripts. Here's the link for XAMPP server https://www.apachefriends.org/index.html. After the Installation, open the XAMPP's Control Panel and start Apache and MySQL.

And this is the link for the jquery that i used in this tutorial https://jquery.com/

Lastly, this is the link for the jquery ui that i used for the autocomplete function https://jqueryui.com/

Creating Database

Open your database web server then create a database naming db_text. Then go to the SQL tab and copy/paste the script below and click the GO button below.

CREATE TABLE `product` (
  `product_id` int(11) NOT NULL,
  `product` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
INSERT INTO `product` (`product_id`, `product`) VALUES
(1, 'Shampoo'),
(2, 'Soap'),
(3, 'Milk'),
(4, 'Wheat'),
(5, 'Rice'),
(6, 'Apple'),
(7, 'Pork'),
(8, 'Chicken');
 
ALTER TABLE `product`
ADD PRIMARY KEY (`product_id`);
 
ALTER TABLE `product`
MODIFY `product_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

Or, you can also import the provided SQL File provided along with the source code zip file inside the database folder. Click the Import Tab Menu in PHPMyAdmin and choose the sql file then click ok.

Creating the database connection

Open your any kind of text editor(notepadd++, etc..). Then just copy/paste the code below then name it conn.php.

<?php
        $conn = new mysqli('localhost', 'root', '', 'db_text');
        
        if(!$conn){
                die("Error: Can't connect to database");
        }
?>

Creating The Interface

This is where we will create a simple form for our application. To create the forms simply copy and write it into you text editor, then save it as shown below

. index.php.

<!DOCTYPE html>
<html lang="en">
        <head>
                <link rel="stylesheet" type="text/css" href="css/bootstrap.css"/>
                <link rel="stylesheet" type="text/css" href="css/jquery-ui.css"/>
                <meta charset="UTF-8" name="viewport" content="width=device-width, initial-scale=1"/>
        </head>
<body>
        <nav class="navbar navbar-default">
                <div class="container-fluid">
                        <a class="navbar-brand" href="https://sourcecodester.com">Sourcecodester</a>
                </div>
        </nav>
        <div class="col-md-3"></div>
        <div class="col-md-6 well">
                <h3 class="text-primary">PHP - Simple Autocomplete With MySQLi & jQuery</h3>
                <hr style="border-top:1px dotted #ccc;"/>
                <div class="col-md-2"></div>
                <div class="col-md-8">
                        <div class="form-group">
                                <form action="" method="POST">
                                        <label>Search Product here...</label>
                                        <div class="auto-widget">
                                                <input class="form-control" id="search" type="text" name="product"/>
                                        </div>
                                </form>
                        </div>
                </div>
        </div>
</body>
<script src="js/jquery-3.2.1.min.js"></script>
<script src="js/jquery-ui.js"></script>
<script type="text/javascript">
        $(document).ready(function(){
                $("#search").autocomplete({
                        source: 'search.php',
                        minLength: 0,
                });
        });
</script>
</html>

Creating the Main Function

This code contains the specific script for the autocomplete. This code will send request to the database then return it as a javascript array to display in the textbox input. To do that write these block of codes inside the Text editor and call it as search.php.

<?php
        require_once 'conn.php';
        
        $search = $_GET['term'];
        
        $query = $conn->query("SELECT * FROM `product` WHERE `product` LIKE '%$search%' ORDER BY `product` ASC") or die(mysqli_connect_errno());
        
        $list = array();
        $rows = $query->num_rows;
        
        if($rows > 0){
                while($fetch = $query->fetch_assoc()){
                        $data['value'] = $fetch['product']; 
                        array_push($list, $data);
                }
        }
        
        echo json_encode($list);
?>

DEMO

There you have it we successfully created a Simple Autocomplete With MySQLi & jQuery using PHP. I hope that this simple tutorial helps you to what you are looking for. For more updates and tutorials just kindly visit this site.

Enjoy Coding!!!

Add new comment

5705 views