Autocomplete using PHP/MySQL and jQuery

Autocomplete using PHP/MySQL and jQuery

Hi, a new tutorial explain how to impliment an autocomplete using PHP/MySQL and jQuery is available. In this tutorial w’ll focus on the autocomplete functionality using a single table in the database and a single input text in the web page. When we type a letter, a list of proposition containning the keyword will be displayed with the keyword in bold.

Download source code

1. Demonstration

2. Structure

/css : contain the CSS files
/js : contain the JavaScript files
/images : contain all images

3. Database

Create a new database named “autocomplet”, and import the SQL script in main folder, or copy/paste the script bellow:

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

--
-- Database: `autocomplet`
--

-- --------------------------------------------------------

--
-- Table structure for table `country`
--

CREATE TABLE IF NOT EXISTS `country` (
  `country_id` int(11) NOT NULL AUTO_INCREMENT,
  `country_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`country_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=45 ;

--
-- Dumping data for table `country`
--

INSERT INTO `country` (`country_id`, `country_name`) VALUES
(1, 'china'),
(2, 'united states'),
(3, 'india'),
(4, 'japan'),
(5, 'brazil'),
(6, 'russia'),
(7, 'germany'),
(8, 'nigeria'),
(9, 'united kingdom'),
(10, 'france'),
(11, 'mexico'),
(12, 'south korea'),
(13, 'indonesia'),
(14, 'philippines'),
(15, 'egypt'),
(16, 'vietnam'),
(17, 'turkey'),
(18, 'italy'),
(19, 'spain'),
(20, 'canada'),
(21, 'poland'),
(22, 'argentina'),
(23, 'colombia'),
(24, 'iran'),
(25, 'south africa'),
(26, 'malaysia'),
(27, 'pakistan'),
(28, 'australia'),
(29, 'thailand'),
(30, 'morocco'),
(31, 'taiwan'),
(32, 'netherlands'),
(33, 'ukraine'),
(34, 'saudi arabia'),
(35, 'kenya'),
(36, 'venezuela'),
(37, 'peru'),
(38, 'romania'),
(39, 'chile'),
(40, 'uzbekistan'),
(41, 'bangladesh'),
(42, 'kazakhstan'),
(43, 'belgium'),
(44, 'sweden');

4. Files

index.php

This is the main file, it will be displayed on the first view:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Autocomplete using PHP/MySQL and jQuery</title>
<link rel="stylesheet" href="css/style.css" />
<script type="text/javascript" src="js/jquery.min.js"></script>
<script type="text/javascript" src="js/script.js"></script>
</head>

<body>
    <div class="container">
        <div class="header">
            <img src="images/BeWebDeveloper.png" />
        </div><!-- header -->
        <h1 class="main_title">Autocomplet using PHP/MySQL and jQuery</h1>
        <div class="content">
            <form>
                <div class="label_div">Type a keyword : </div>
                <div class="input_container">
                    <input type="text" id="country_id" onkeyup="autocomplet()">
                    <ul id="country_list_id"></ul>
                </div>
            </form>
        </div><!-- content -->    
        <div class="footer">
            Powered by <a href="#">bewebdeveloper.com</a>
        </div><!-- footer -->
    </div><!-- container -->
</body>
</html>

ajax_refresh.php

This file will be executed using ajax with jQuery everytime we type a letter, it connect to the database and select the statements containning the keyword.

<?php
// PDO connect *********
function connect() {
    return new PDO('mysql:host=localhost;dbname=autocomplet', 'root', '', array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
}

$pdo = connect();
$keyword = '%'.$_POST['keyword'].'%';
$sql = "SELECT * FROM country WHERE country_name LIKE (:keyword) ORDER BY country_id ASC LIMIT 0, 10";
$query = $pdo->prepare($sql);
$query->bindParam(':keyword', $keyword, PDO::PARAM_STR);
$query->execute();
$list = $query->fetchAll();
foreach ($list as $rs) {
	// put in bold the written text
	$country_name = str_replace($_POST['keyword'], '<b>'.$_POST['keyword'].'</b>', $rs['country_name']);
	// add new option
    echo '<li onclick="set_item(\''.str_replace("'", "\'", $rs['country_name']).'\')">'.$country_name.'</li>';
}
?>

script.js

This is the JavaScript file

// autocomplet : this function will be executed every time we change the text
function autocomplet() {
	var min_length = 0; // min caracters to display the autocomplete
	var keyword = $('#country_id').val();
	if (keyword.length >= min_length) {
		$.ajax({
			url: 'ajax_refresh.php',
			type: 'POST',
			data: {keyword:keyword},
			success:function(data){
				$('#country_list_id').show();
				$('#country_list_id').html(data);
			}
		});
	} else {
		$('#country_list_id').hide();
	}
}

// set_item : this function will be executed when we select an item
function set_item(item) {
	// change input value
	$('#country_id').val(item);
	// hide proposition list
	$('#country_list_id').hide();
}

style.css

This is the CSS file, it contain the design styles

* {
	margin: 0;
	padding: 0;
}
body {
	padding: 10px;
	background: #eaeaea;
	text-align: center;
	font-family: arial;
	font-size: 12px;
	color: #333333;
}
.container {
	width: 1000px;
	height: auto;
	background: #ffffff;
	border: 1px solid #cccccc;
	border-radius: 10px;
	margin: auto;
	text-align: left;
}
.header {
	padding: 10px;
}
.main_title {
	background: #cccccc;
	color: #ffffff;
	padding: 10px;
	font-size: 20px;
	line-height: 20px;
}
.content {
	padding: 10px;
	min-height: 100px;
}
.footer {
	padding: 10px;
	text-align: right;
}
.footer a {
	color: #999999;
	text-decoration: none;
}
.footer a:hover {
	text-decoration: underline;
}
.label_div {
	width: 120px;
	float: left;
	line-height: 28px;
}
.input_container {
	height: 30px;
	float: left;
}
.input_container input {
	height: 20px;
	width: 200px;
	padding: 3px;
	border: 1px solid #cccccc;
	border-radius: 0;
}
.input_container ul {
	width: 206px;
	border: 1px solid #eaeaea;
	position: absolute;
	z-index: 9;
	background: #f3f3f3;
	list-style: none;
}
.input_container ul li {
	padding: 2px;
}
.input_container ul li:hover {
	background: #eaeaea;
}
#country_list_id {
	display: none;
}
Ajax, Autocomplete, CSS, JavaScript, MySQL, PHP
comments powered by Disqus

Social Profiles

bewebdeveloper on facebook bewebdeveloper on twitter bewebdeveloper on google plus bewebdeveloper on vk bewebdeveloper on youtube bewebdeveloper on tumblr bewebdeveloper rss

Subscribe to our Newsletter

Facebook

Twitter

Google+

HTML Tutorials
CSS Tutorials

Advertising