PDA

View Full Version : Autosuggest/Autocomplete from database



newphpcoder
10-27-2011, 02:54 AM
Good day!

I need an autosuggest/autocomplete script for my field Name..
I search code for that but when I tried it in my webpage it did not work. I used smarty template, php and mysql-adodb.

Is there any syntax that you can suggest for a simple autosuggest the data coming from database?

Thank you...

newphpcoder
10-27-2011, 04:48 AM
I found sample autosuggest:
http://www.planetsourcecode.com/vb/scripts/ShowCode.asp?lngWId=8&txtCodeId=2492

I tried to use this in my code, but it did not work..

Here is my code where i want to use it.




<?php
include("config.php");


$smarty->display('loanForm.tpl');

?>




and here is the loanForm.tpl



<div class="loanformmain">
<fieldset>
<legend>Loan Enrollment Form</legend>
<p>
<input type="button" name="sssbtn" value="SSS" onclick="loanFrm()">
<input type="button" name="hdmfbtn" value="HDMF" onClick="hdmfloanFrm()">
<input type="button" name="UTbtn" value="Union Dues/Trust Fund" onclick="utloanFrm()">
</p>
<div class="loanform">
<fieldset>
<legend>SSS Loan</legend>
<p>
<label class="LoanLabel">SSS ID:</label>
<input type="text" name="SSSID" value="" class="LoanField" size="7">
<label class="LoanEmpNoLabel">&nbsp;Employee No:</label>
<input type="text" name="EMP_NO" value="" class="LoanField" size="7">
<label class="Loan1Label">&nbsp;Loan:</label>
<input type="text" name="EMP_NO" value="" class="LoanField" size="5">
<label class="Loan1Label">&nbsp;Amor:</label>
<input type="text" name="EMP_NO" value="" class="LoanField" size="5">
<label class="LoanNameLabel">&nbsp;Name:</label>
<input type="text" name="NAME" value="" class="LoanField" size="30">

<input type="button" name="add" value="add" class="add">
<input type="button" name="edit" value="edit" class="edit">

</p>
</fieldset>
</fieldset>
</div>
</div>


in my Name field I wanto have the autosuggest..

Thank you...

newphpcoder
10-27-2011, 10:04 AM
I tried to add the code from planetsourcecode, and my problem is automatically the data was display at the top without searching.

here is my revise code:



<?php
include("config.php");

$keyword = $_POST['data'];
$sql = "select LNAME from personal where LNAME like '".$keyword."%' limit 0,20";
//$sql = "select name from ".$db_table."";
$result = mysql_query($sql) or die(mysql_error());
if(mysql_num_rows($result))
{
echo '<ul class="list">';
while($row = mysql_fetch_array($result))
{
$str = strtolower($row['LNAME']);
//$str = $row['LNAME'];
$start = strpos($str,$keyword);
$end = similar_text($str,$keyword);
$last = substr($str,$end,strlen($str));
$first = substr($str,$start,$end);

$final = '<span class="bold">'.$first.'</span>'.$last;

echo '<li><a href=\'javascript:void(0);\'>'.$final.'</a></li>';
}
echo "</ul>";
}
else
echo 0;



$smarty->display('loanForm.tpl');

?>

the LNAME data display automatically in my loanForm.tpl

here is my loanForm.tpl


<link href="style.css" rel="stylesheet" type="text/css">
<SCRIPT LANGUAGE="JavaScript" src="jquery.js"></SCRIPT>
<SCRIPT LANGUAGE="JavaScript" src="script.js"></SCRIPT>


<div class="loanformmain">
<fieldset>
<legend>Loan Enrollment Form</legend>
<p>
<input type="button" name="sssbtn" value="SSS" onclick="loanFrm()">
<input type="button" name="hdmfbtn" value="HDMF" onClick="hdmfloanFrm()">
<input type="button" name="UTbtn" value="Union Dues/Trust Fund" onclick="utloanFrm()">
</p>
<div class="loanform">
<fieldset>
<legend>SSS Loan</legend>
<p>
<label class="LoanLabel">SSS ID:</label>
<input type="text" name="SSSID" value="" class="LoanField" size="7">
<label class="LoanEmpNoLabel">&nbsp;Employee No:</label>
<input type="text" name="EMP_NO" value="" class="LoanField" size="7">

<label class="Loan1Label">&nbsp;Loan:</label>
<input type="text" name="Loan" value="" class="LoanField" size="5">
<label class="Loan1Label">&nbsp;Amor:</label>
<input type="text" name="Amor" value="" class="LoanField" size="5">
<label class="LoanNameLabel">&nbsp;Name:</label>
<input type="text" name="NAME" id="keyword" value="" class="LoanField" size="30">
<div id="ajax_response"></div>
<input type="button" name="add" value="add" class="add">
<input type="button" name="edit" value="edit" class="edit">

</p>
</fieldset>
</fieldset>
</div>
</div>


here is the script.js


/*
Script name : Ajax Auto Suggest
File Name : script.js
Developed By : Amit Patil (India)
Email Id : amitpatil321@gmail.com
last Updated : 9 June 2009
This program is freeware.There is no any ****ing copyright and bla bla bla.
You can use it for your personal use.You can also make any changes to this script.
But before using this script i would appericiate your mail.That will encourage me a lot.
Any suggestions are always welcome.
Have a fun with programming.
*/
$(document).ready(function(){
$(document).click(function(){
$("#ajax_response").fadeOut('slow');
});
$("#keyword").focus();
var offset = $("#keyword").offset();
var width = $("#keyword").width()-2;
$("#ajax_response").css("left",offset.left);
$("#ajax_response").css("width",width);
$("#keyword").keyup(function(event){
//alert(event.keyCode);
var keyword = $("#keyword").val();
if(keyword.length)
{
if(event.keyCode != 40 && event.keyCode != 38 && event.keyCode != 13)
{
$("#loading").css("visibility","visible");
$.ajax({
type: "POST",
url: "LoanForm.php",
data: "data="+keyword,
success: function(msg){
if(msg != 0)
$("#ajax_response").fadeIn("slow").html(msg);
else
{
$("#ajax_response").fadeIn("slow");
$("#ajax_response").html('<div style="text-align:left;">No Matches Found</div>');
}
$("#loading").css("visibility","hidden");
}
});
}
else
{
switch (event.keyCode)
{
case 40:
{
found = 0;
$("li").each(function(){
if($(this).attr("class") == "selected")
found = 1;
});
if(found == 1)
{
var sel = $("li[class='selected']");
sel.next().addClass("selected");
sel.removeClass("selected");
}
else
$("li:first").addClass("selected");
}
break;
case 38:
{
found = 0;
$("li").each(function(){
if($(this).attr("class") == "selected")
found = 1;
});
if(found == 1)
{
var sel = $("li[class='selected']");
sel.prev().addClass("selected");
sel.removeClass("selected");
}
else
$("li:last").addClass("selected");
}
break;
case 13:
$("#ajax_response").fadeOut("slow");
$("#keyword").val($("li[class='selected'] a").text());
break;
}
}
}
else
$("#ajax_response").fadeOut("slow");
});
$("#ajax_response").mouseover(function(){
$(this).find("li a:first-child").mouseover(function () {
$(this).addClass("selected");
});
$(this).find("li a:first-child").mouseout(function () {
$(this).removeClass("selected");
});
$(this).find("li a:first-child").click(function () {
$("#keyword").val($(this).text());
$("#ajax_response").fadeOut("slow");
});
});
});


and jquery.js


/*
Script name : Ajax Auto Suggest
File Name : script.js
Developed By : Amit Patil (India)
Email Id : amitpatil321@gmail.com
last Updated : 9 June 2009
This program is freeware.There is no any ****ing copyright and bla bla bla.
You can use it for your personal use.You can also make any changes to this script.
But before using this script i would appericiate your mail.That will encourage me a lot.
Any suggestions are always welcome.
Have a fun with programming.
*/
$(document).ready(function(){
$(document).click(function(){
$("#ajax_response").fadeOut('slow');
});
$("#keyword").focus();
var offset = $("#keyword").offset();
var width = $("#keyword").width()-2;
$("#ajax_response").css("left",offset.left);
$("#ajax_response").css("width",width);
$("#keyword").keyup(function(event){
//alert(event.keyCode);
var keyword = $("#keyword").val();
if(keyword.length)
{
if(event.keyCode != 40 && event.keyCode != 38 && event.keyCode != 13)
{
$("#loading").css("visibility","visible");
$.ajax({
type: "POST",
url: "LoanForm.php",
data: "data="+keyword,
success: function(msg){
if(msg != 0)
$("#ajax_response").fadeIn("slow").html(msg);
else
{
$("#ajax_response").fadeIn("slow");
$("#ajax_response").html('<div style="text-align:left;">No Matches Found</div>');
}
$("#loading").css("visibility","hidden");
}
});
}
else
{
switch (event.keyCode)
{
case 40:
{
found = 0;
$("li").each(function(){
if($(this).attr("class") == "selected")
found = 1;
});
if(found == 1)
{
var sel = $("li[class='selected']");
sel.next().addClass("selected");
sel.removeClass("selected");
}
else
$("li:first").addClass("selected");
}
break;
case 38:
{
found = 0;
$("li").each(function(){
if($(this).attr("class") == "selected")
found = 1;
});
if(found == 1)
{
var sel = $("li[class='selected']");
sel.prev().addClass("selected");
sel.removeClass("selected");
}
else
$("li:last").addClass("selected");
}
break;
case 13:
$("#ajax_response").fadeOut("slow");
$("#keyword").val($("li[class='selected'] a").text());
break;
}
}
}
else
$("#ajax_response").fadeOut("slow");
});
$("#ajax_response").mouseover(function(){
$(this).find("li a:first-child").mouseover(function () {
$(this).addClass("selected");
});
$(this).find("li a:first-child").mouseout(function () {
$(this).removeClass("selected");
});
$(this).find("li a:first-child").click(function () {
$("#keyword").val($(this).text());
$("#ajax_response").fadeOut("slow");
});
});
});


When I tried it separately not using .tpl it works, by the way I'm using mysql adodb for my database and smarty for my template.Thank you

newphpcoder
10-28-2011, 12:15 AM
I found an autocomplete by this link:
http://docs.jquery.com/Plugins/autocomplete

I want this because its simple, but the data is came from var data, but in my case, the data that I want to search is from the database, How can I do that.?

Thank you




<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<script src="http://code.jquery.com/jquery-latest.js"></script>
<link rel="stylesheet" href="http://dev.jquery.com/view/trunk/plugins/autocomplete/demo/main.css" type="text/css" />
<link rel="stylesheet" href="http://dev.jquery.com/view/trunk/plugins/autocomplete/jquery.autocomplete.css" type="text/css" />
<script type="text/javascript" src="http://dev.jquery.com/view/trunk/plugins/autocomplete/lib/jquery.bgiframe.min.js"></script>
<script type="text/javascript" src="http://dev.jquery.com/view/trunk/plugins/autocomplete/lib/jquery.dimensions.js"></script>
<script type="text/javascript" src="http://dev.jquery.com/view/trunk/plugins/autocomplete/jquery.autocomplete.js"></script>
<script>
$(document).ready(function(){
var data = "Core Selectors Attributes Traversing Manipulation CSS Events Effects Ajax Utilities".split(" ");
$("#example").autocomplete(data);
});
</script>

</head>
<body>
API Reference: <input id="example" /> (try "C" or "E")
</body>
</html>

newphpcoder
10-28-2011, 03:03 AM
I tried new autocomplete from http://papermashup.com/jquery-php-ajax-autosuggest/ and i revise the code:

I change the autosuggest.php
before the code is like this:


<?php
$db = new mysqli('DB_HOST', 'USERNAME' ,'PASSWORD', 'DATABASE_NAME');

if(!$db) {

echo 'Could not connect to the database.';
} else {

if(isset($_POST['queryString'])) {
$queryString = $db->real_escape_string($_POST['queryString']);

if(strlen($queryString) >0) {

$query = $db->query("SELECT country FROM countries WHERE country LIKE '$queryString%' LIMIT 10");
if($query) {
echo '<ul>';
while ($result = $query ->fetch_object()) {
echo '<li onClick="fill(\''.addslashes($result->country).'\');">'.$result->country.'</li>';
}
echo '</ul>';

} else {
echo 'OOPS we had a problem :(';
}
} else {
// do nothing
}
} else {
echo 'There should be no direct access to this script!';
}
}
?>

and I change it into:


<?php

include 'config.php';

$queryString = $_POST['queryString'];

$query = "SELECT country FROM countries WHERE country LIKE '$queryString%' LIMIT 10";
$result = mysql_query($query) or die(mysql_error());
if(mysql_num_rows($result))
{
echo '<ul class="list">';
while($row = mysql_fetch_array($result))
{
echo '<li onClick="fill(\''.addslashes($row->country).'\');">'.$row->country.'</li>';
}
echo "</ul>";
}
else {
echo 'OOPS we had a problem :(';
}
?>


the index.php code is:


<!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>jQuery PHP Ajax Autosuggest</title>


<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.js"></script>
<script>
function suggest(inputString){
if(inputString.length == 0) {
$('#suggestions').fadeOut();
} else {
$('#country').addClass('load');
$.post("autosuggest.php", {queryString: ""+inputString+""}, function(data){
if(data.length >0) {
$('#suggestions').fadeIn();
$('#suggestionsList').html(data);
$('#country').removeClass('load');
}
});
}
}

function fill(thisValue) {
$('#country').val(thisValue);
setTimeout("$('#suggestions').fadeOut();", 600);
}

</script>

<style>
#result {
height:20px;
font-size:16px;
font-family:Arial, Helvetica, sans-serif;
color:#333;
padding:5px;
margin-bottom:10px;
background-color:#FFFF99;
}
#country{
padding:3px;
border:1px #CCC solid;
font-size:17px;
}
.suggestionsBox {
position: absolute;
left: 0px;
top:40px;
margin: 26px 0px 0px 0px;
width: 200px;
padding:0px;
background-color: #000;
border-top: 3px solid #000;
color: #fff;
}
.suggestionList {
margin: 0px;
padding: 0px;
}
.suggestionList ul li {
list-style:none;
margin: 0px;
padding: 6px;
border-bottom:1px dotted #666;
cursor: pointer;
}
.suggestionList ul li:hover {
background-color: #FC3;
color:#000;
}
ul {
font-family:Arial, Helvetica, sans-serif;
font-size:11px;
color:#FFF;
padding:0;
margin:0;
}

.load{
background-image:url(loader.gif);
background-position:right;
background-repeat:no-repeat;
}

#suggest {
position:relative;
}

</style>
</head>

<body>




<form id="form" action="#">
<div id="suggest">Start to type a country: <br />
<input type="text" size="25" value="" id="country" onkeyup="suggest(this.value);" onblur="fill();" class="" />

<div class="suggestionsBox" id="suggestions" style="display: none;"> <img src="arrow.png" style="position: relative; top: -12px; left: 30px;" alt="upArrow" />
<div class="suggestionList" id="suggestionsList"> &nbsp; </div>
</div>
</div>
</form>



</body>
</html>


now I encountered problem in, when I type in textfield, no data display only colors.

here is the script.js:


function lookup(inputString) {
if(inputString.length == 0) {
// Hide the suggestion box.
$('#suggestions').hide();
} else {
// post data to our php processing page and if there is a return greater than zero
// show the suggestions box
$.post("string_search.php", {mysearchString: ""+inputString+""}, function(data){
if(data.length >0) {
$('#suggestions').show();
$('#autoSuggestionsList').html(data);
}
});
}
} //end

// if user clicks a suggestion, fill the text box.
function fill(thisValue) {
$('#inputString').val(thisValue);
setTimeout("$('#suggestions').hide();", 200);
}

and the style.css


body { font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 11px; color: #000; background-color: #eeeeee;}
h3 { padding: 0px; font-size: 23px; margin: 0px 0px 5px 0px; margin-top: 0px; color: #000000;}

#container {
width: 250px;
margin: 25px auto 0px auto;
padding: 20px;
-moz-border-radius: 10px;
-webkit-border-radius: 10px;
}

/* Styling for Suggestion Box Container */
.suggestionsBox {
position: absolute;
width: 240px;
background-color: #212427;
border: 2px solid #000;
color: #fff;
padding: 5px;
margin:10px 0px 0px 0px;
-moz-border-radius: 8px;
-webkit-border-radius: 8px;
}

#inputString { width: 240px; padding: 5px; font-size: 18px;}

.suggestionList { margin: 0px; padding: 0px; }

/* Individual Search Results */
.suggestionList li {
margin: 0px 0px 3px 0px;
padding: 7px;
cursor: pointer;
-moz-border-radius: 3px;
-webkit-border-radius: 3px;
list-style-type: none;
}

/* Hover effect */
.suggestionList li:hover { background-color: #009900; font-weight: bold;}


I only want is a simple autosuggest..Thank you..All the code that I found has a problem.