In this tutorial we are going to see autocomplete using jQuery, Ajax,PHP and MySQL.

 

Follow each steps carefully to create this autocomplete function by yourself.

Step 1:

create this below table using the below query.

CREATE TABLE IF NOT EXISTS `auto` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `data` varchar(75) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

Find the SQL dump from download file.

Step 2:

Create index.php file and add the following html codes to create autocomplete text box.

<h1>AutoComplete Using jQuery, Ajax, PHP and MySQL</h1>
<div class="ui-widget">
<label for="tags">Type Something: </label>
<input id="tags" />
</div>

Step 3:

Add the following css to add styles to our html we just created.

  * {
    background-color: #16a085;
    margin: 0;
    padding: 0;
    color: white;
}
@font-face{
    font-family: Lobster;
    src: url('Lobster.otf');
}
h1{
    font-family: Lobster;
    text-align: center;
    color: white;
    margin-top: 50px;
    font-size: 3em;
}

.ui-autocomplete-input{
    color: white;
}

.ui-widget {
    color: white;
    font-family: 'Lobster';
    font-size: 2em;
    text-align: center;
	margin-top: 130px;
}
#tags{

    background: none repeat scroll 0 0 white;
    height: 35px;
    width: 300px;
	color: blue;
	text-align: center;
	border: 2px solid white;
	border-radius: 5px;

}
#link{
    text-align: center;
    text-decoration: underline;
    margin-top: 70px;
    color: blue ;
}
#link p{
    color: #fe3400;
}
.underline{text-decoration: underline;}

Step 4:

Add following jQuery library files in the index.php file.

<script src="http://code.jquery.com/jquery-1.9.1.js"></script>
<script src="http://code.jquery.com/ui/1.10.2/jquery-ui.js"></script>

 

Step 5:

Here I am attaching autocomplete to the tags textbox. Which makes ajax request to the php file that’s going to provide autocompletion values.

$(function() {
			$("#tags").autocomplete({
				source: function(data, cb){
					$.ajax({
						type:'POST',
						url:'ajax.php',
						data: {
							data : data.term
						},
						dataType: "json",
						success: function(response){
							var result;
							result = [
								{
									label: 'There is matching record found for '+data.term,
									value: ''
								}
							];

							if (response.data.length) {
								result = $.map(response.data, function(d){
									console.log(d);
									
									return {
										label: d.data,
										value: d.data,
										obj: d
									};
								});
							}

							cb(result);
						}
					});
				},
				minLength: 2,
				select: function( event, selectedItem ) {
					console.log( "Selected: " , selectedItem );
				}
			});
		});

 

Step 6:
Create config.php and ajax.php file.

<?php
const DB_HOST = 'localhost';
const DB_USER = 'xyz';
const DB_PASSWORD = 'mysql';
const DB_NAME = 'smarttut_developersguide';


$mysqli = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

if ($mysqli->connect_errno) {
  echo "Failed to connect to MySQL: " . $mysqli -> connect_error;
  exit();
}

Where ajax.php file that gets the ajax request. It process the request data and provides auto completion values.

<?php
require_once 'config.php';
try {
    $data = $_POST['data'];
    if (empty($data)) {
        echo json_encode([]);exit;
    }
    $stmt = $mysqli->prepare("SELECT * FROM auto where data LIKE ?");
    if (!($stmt)) {
        throw new Exception("Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error);
    }
    $p = '%'.$data.'%';
    $stmt->bind_param("s", $p);

    if (!$stmt->execute()) {
        throw new Exception("Execute failed: (" . $stmt->errno . ") " . $stmt->error);
    }
    
    if (!($res = $stmt->get_result())) {
        throw new Exception("Getting result set failed: (" . $stmt->errno . ") " . $stmt->error);
    }
    $results = $res->fetch_all(MYSQLI_ASSOC);
    sendResponse(1, "Filtered Reults", $results);
} catch (Exception $e) {
    sendResponse(0, $e->getMessage(), []);
}



function sendResponse($status, $message, $data)
{
    header('Content-Type: application/json');
    if ($status) {
        http_response_code(200);
        $response = [
            "code" => 200,
            "message" => $message,
            "data" => $data
        ];
        echo json_encode($response); 
    } else {
        http_response_code(500);
        $response = [
            "code" => 500,
            "message" => $message,
            "data" => $data
        ];
        echo json_encode($response);
    }
    exit;
}
exit;

It gets post data, and using that post data we are going query the database. Finally the result is converted into json values and send it back to index.php file.