Posted 6:11 pm by muni & filed under CSS, HTML5, JQUERY, MYSQL, PHP.

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

Demo

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.

Download

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 !important;
}
#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.