As we see in many of the forms in different websited that we just type a letter then the form automatic start guessing the the test or word we want to write in the feild. In this tutorial we will learn how to do autocomplete functionality using a singledatabase table in the database with single input text in the web page. When we start typing a letter , it wil automatic show the value we want to enter in that feild.
We can create a database first. In this tutorial we are taking an example of few countries.
We have to create a database first with any of the name .
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');
After creating a database , we can call it in any of the file. Like we have created an index.php file to display the form feild
<!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/abcd.png" />
</div><!-- header -->
<h1 class="main_title">Autocomplet form feild</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">
</div><!-- footer -->
</div><!-- container -->
</body>
</html>
After creating an index.php we have to create ajax_refresh.php. This file will be executed using ajax with jQuery everytime we type a letter.
<?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) {
$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 file is must for doing the autocomplete form .
// 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();
}
You can also copy the css if needed to clear the design of the autocomplete form feild.
* {
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;
}
1 Comment(s)