HTML File, gw.html
<!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>
<title>Help Tool 2.0</title>
<link rel="stylesheet" href="http://code.jquery.com/ui/1.10.3/themes/smoothness/jquery-ui.css" />
<script src="http://code.jquery.com/jquery-1.9.1.js"></script>
<script src="http://code.jquery.com/ui/1.10.3/jquery-ui.js"></script>
</head>
<body>
<script type="text/javascript">
$(function() {
url = 'gsearch.php?q=' + $('#search').val();
$.getJSON(url, function(data) {
$('#search').autocomplete( {
source: data
}).keypress(function(e) {
if (e.keyCode == 13 ){
alert("choosed");
}
});
});
});
</script>
<form>
Office Name: <input id="search" name="q" type="text" />
</form>
</body>
</html>
gsearch.php
<?php
$username="test";
$password="password";
$database="test";
$q=$_GET['q'];
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query = "select office_name,postcode from office where office_name like '%$q%' or postcode like '%$q%'";
$result = mysql_query($query);
$num=mysql_numrows($result);
mysql_close();
$response = array();
if($result){
while($row = mysql_fetch_array($result,MYSQLI_ASSOC)){
array_push($response,array('label'=>$row['office_name'],'value'=>$row['office_name']));
}
echo(json_encode($response));
}else {
echo 'No Results for :"'.$_POST['q'].'"';
}
?>
$username="test";
$password="password";
$database="test";
$q=$_GET['q'];
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query = "select office_name,postcode from office where office_name like '%$q%' or postcode like '%$q%'";
$result = mysql_query($query);
$num=mysql_numrows($result);
mysql_close();
$response = array();
if($result){
while($row = mysql_fetch_array($result,MYSQLI_ASSOC)){
array_push($response,array('label'=>$row['office_name'],'value'=>$row['office_name']));
}
echo(json_encode($response));
}else {
echo 'No Results for :"'.$_POST['q'].'"';
}
?>
MySQL SQL Script
office.sql
CREATE TABLE IF NOT EXISTS `office` (
`office_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Event ID',
`office_name` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Event Name',
`country` varchar(50) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Province',
`province` varchar(50) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Province',
`city` varchar(30) COLLATE utf8_unicode_ci NOT NULL COMMENT 'City',
`address` varchar(88) COLLATE utf8_unicode_ci NOT NULL COMMENT 'address',
`postcode` char(10) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Postcode',
`gpslatitude` char(15) COLLATE utf8_unicode_ci NOT NULL COMMENT 'GPS Latitude',
`gpslongitude` char(15) COLLATE utf8_unicode_ci NOT NULL COMMENT 'GPS Longitude',
`catagory` varchar(16) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Catagory ID',
`start_date` date DEFAULT NULL COMMENT 'Starting Date',
`close_date` date DEFAULT NULL COMMENT 'Ending Date',
`Schedule` text COLLATE utf8_unicode_ci NOT NULL COMMENT 'Schedule',
`admission` char(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT 'Admission',
`Contact` char(50) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Contact',
`Phone` varchar(20) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Phone',
`Fax` char(20) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Fax',
`Email` varchar(50) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Email',
`Website` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'website',
`image1` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Image1',
`Image2` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Image2',
`Description` text COLLATE utf8_unicode_ci NOT NULL COMMENT 'Description',
`tags` text COLLATE utf8_unicode_ci NOT NULL COMMENT 'tags',
`Searchable` tinyint NOT NULL DEFAULT '1' COMMENT 'Searchable',
`Submitter` varchar(80) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Submitter',
PRIMARY KEY (`office_id`),
KEY `Event_Name` (`office_name`),
FULLTEXT KEY `Description` (`Description`)
);
`office_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Event ID',
`office_name` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Event Name',
`country` varchar(50) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Province',
`province` varchar(50) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Province',
`city` varchar(30) COLLATE utf8_unicode_ci NOT NULL COMMENT 'City',
`address` varchar(88) COLLATE utf8_unicode_ci NOT NULL COMMENT 'address',
`postcode` char(10) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Postcode',
`gpslatitude` char(15) COLLATE utf8_unicode_ci NOT NULL COMMENT 'GPS Latitude',
`gpslongitude` char(15) COLLATE utf8_unicode_ci NOT NULL COMMENT 'GPS Longitude',
`catagory` varchar(16) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Catagory ID',
`start_date` date DEFAULT NULL COMMENT 'Starting Date',
`close_date` date DEFAULT NULL COMMENT 'Ending Date',
`Schedule` text COLLATE utf8_unicode_ci NOT NULL COMMENT 'Schedule',
`admission` char(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT 'Admission',
`Contact` char(50) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Contact',
`Phone` varchar(20) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Phone',
`Fax` char(20) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Fax',
`Email` varchar(50) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Email',
`Website` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'website',
`image1` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Image1',
`Image2` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Image2',
`Description` text COLLATE utf8_unicode_ci NOT NULL COMMENT 'Description',
`tags` text COLLATE utf8_unicode_ci NOT NULL COMMENT 'tags',
`Searchable` tinyint NOT NULL DEFAULT '1' COMMENT 'Searchable',
`Submitter` varchar(80) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Submitter',
PRIMARY KEY (`office_id`),
KEY `Event_Name` (`office_name`),
FULLTEXT KEY `Description` (`Description`)
);
Data file
To load the data file into the table, the file must be have 755 permission, and its parent folder must have 755 permission too. So I would suggest to put it in /tmp if you are using Unix/Linux.
Mysql> load data infile '/tmp/office.txt' into table office fields terminated by ',';
office.txt
, Downtown Toronto, Canada, Ontario, Toronto, 5 John st., M5V 5C6,,,Head Quarter,,,6am-6pm,N/A,Phy Cris, 416-333-8888,,aaa@bloger.ca,www.bloger.ca,,,Internet Marketing,IM,1,
, Downtown New York, USA, NY, New York, 8 Wall st., 10888,,,NA HQ,,,6am-6pm,N/A,Sam US, 646-333-8888,,bbb@bloger.ca,www.bloger.ca,,,Internet Marketing,IM,1,
, Downtown New York, USA, NY, New York, 8 Wall st., 10888,,,NA HQ,,,6am-6pm,N/A,Sam US, 646-333-8888,,bbb@bloger.ca,www.bloger.ca,,,Internet Marketing,IM,1,
