Autocomplete Textbox using PHP, Ajax and MySQL

Introduction

This example shows how to create an Autocomplete Textbox using PHP, Ajax and Mysql Database. When the user begin typing in the textbox "Product Name" , an automatic ajax request will be made with the database to extract and fetch the list of products.
Then a list of the products that include the typed caracters will appear in order to select one of them to be displayed in the textbox.

Content

Screen Shots
1- Database Connection.
2- Database content.
3- HTML Code.
4- CSS Style.
5- JS Script ( Ajax Request )
6- PHP Code ( Retreiving data )

Screenshots

Autocomplete Textbox using PHP, Ajax and Mysql

1- Database Connection

To handle the connection to our database. Create a file and name it db_connect.php. Then add the following code :

<?php
//Database Connection file. Update with your Database information .
    define ("DB_HOST", "localhost"); //Databse Host.
    define ("DB_USER", "root"); //Databse User.
    define ("DB_PASS", ""); //database password.
    define ("DB_NAME", "tutos"); //database Name.
$db = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
//
//$db->set_charset('utf8');
mysqli_set_charset($db,"utf8");
if($db->connect_errno > 0){
    die('Unable to connect to database ['.$db->connect_error.']');
}

2- Database Content

Create a table named products in your database and add the following SQL code :

--
-- Table structure for table `products`
--

CREATE TABLE `products` (
  `productId` int(11) NOT NULL,
  `productBarcode` varchar(20) NOT NULL,
  `productName` varchar(100) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `products`
--

INSERT INTO `products` (`productId`, `productBarcode`, `productName`) VALUES
(1, '1254785496325', 'Fruit Juice Tropical CAN 250 ml'),
(2, '1254236552148', 'Fruit Juice Mojito CAN 250 ml'),
(3, '1254785665923', 'Carbonated Drink Cola 300 ml'),
(4, '1254785696523', 'Carbonated Juice 250 ml'),
(5, '1236521545879', 'Fried Chicken Frozen 500 g'),
(6, '1254785696325', 'Fresh Fish 1 Kg');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `products`
--
ALTER TABLE `products`
  ADD PRIMARY KEY (`productId`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `products`
--
ALTER TABLE `products`
  MODIFY `productId` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;COMMIT;

This code will create the table and insert test data.

3- HTML Code

After creating our database and mainting the connection file, we move now to the HTML code. Create a page named index.php in the root of your server then add the following code :

 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html lang="en">
<head>
  <title>MESDEV.COM || Autocomplete Textbox using PHP, Ajax and Mysql</title>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
  <link rel="stylesheet" type="text/css" href="css/style.css" media="all" />
  <link rel="stylesheet" type="text/css" href="css/select2.css" media="all" />
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.16.0/umd/popper.min.js"></script>
  </head>
<body>
    <div class="container">    
        <div class="row content">
            <div class="well">
                <div class="row">
                    <div class="form-group col-sm-2"></div>
                    <div class="form-group col-sm-8">
                        <!-- the textbox -->
                        <label>Product Name</label>
                            <input type="text" name="product" id="product"  class="form-control">
                        <!-- the container that will receive the list of products from after the ajax request -->
                        <div id="productlist"></div>
                    </div>
                    <div class="form-group col-sm-2"></div>
                    <!-- this container will display the name of the selected product -->
                    <div class="col-sm-12" id ="result"></div>
                </div>
            </div>
        </div>
    </div>
</body>
</html>

4- CSS Style

To keep a nice view of the displayed list of products, we added this style (you can add it to the head section or in an independant css file and call it in the head tag) :

ul{
      margin-top: 0px;
      background: gray;
      color: #000;
      }
    li{
      padding: 12px;
      cursor: pointer;
      color: black;
      border: 1px
      }
    li:hover{
      background: #f0f0f0;
      }

5- JS Script ( Ajax Request )

This section is the heart of the work we want to do as it handles an ajax request to retreive the data (product list ) from the database and fetch it to the listing container to be displayed. For that add the following JS script :

$(document).ready(function(){
			  $("#product").on("keyup", function(){
				var product = $(this).val();
				//if something if typed
				if (product !=="") {
				  $.ajax({
					// the url of the page that will handle the extraction of data from database
					url:"products.php",
					type:"POST",
					cache:false,
					data:{product:product},
					success:function(data){
					  // data will be displayed in the 'productlist' container
					  $("#productlist").html(data);
					  $("#productlist").fadeIn();
					}  
				  });
				}else{
				  // if no data found , the container 'productlist' will be cleared and if there was a displayed list , it will be hided.
				  $("#productlist").html("");  
				  $("#productlist").fadeOut();
				}
			  });

			  // when selecting a product from the list, the textbox will get its value
			  $(document).on("click","li", function(){
				$('#product').val($(this).text());
				// the value of the selected product will be also shown in a another container down the textbox
				$('#result').html(' Selected Product : '+$(this).text()+'');
				//then, we hide the list of products
				$('#productlist').fadeOut("fast");
			  });
		  });

6- PHP Code ( Retreiving data )

As you see in the JS script, we added the url of the php page that will handle the data retreiving but we still not implemented this page.

For that, create a page named products.php and add the following code to it :

<?php
include("db_connect.php");
if (isset($_POST['product'])) {


          $output = "";
        //string text retreived from the textbox
          $product = $_POST['product'];
        /* ------- sql query that will look for the string in the 'productName' column of the table products.  */
          $query = "SELECT * FROM products WHERE productName LIKE '%$product%'";
          $result = $db->query($query);


          $output = '<ul class="list-unstyled">';        


          if ($result->num_rows > 0) {
              while ($row = $result->fetch_array()) {
                  $output .= '<li>'.ucwords($row['productName']).' ('.$row['productBarcode'].')</li>';
              }
          }else{
                $output .= '<li> No product found</li>';
          }
          
          $output .= '</ul>';
          echo $output;
    }

14 items available

Scripts & Templates