Posted by j_d3
Jay hails from small city Chatham, Ontario where he's grown up and spent his entire life, short of university. Since returning home from school, he has spent the last several years in the radio broadcast industry as an IT professional and Interactive Media Manager.
I found a nifty little autocomplete that immitates the one in facebook which I wanted to use for a project I’m working on allowing users to quickly search and select audio and video files in a database. You can download the code from web2ajax and read along to see how I implemented it into my project using a MySQL tables as the backend.
The first thing you’ll want to do is extract the zip file and move it onto your webserver. I placed the image is /img into my /images folder, everything in /lib into my server /lib folder and for organizing purposes I put /AjaxSearch into /lib as well. index.php I renamed to sample.php for testing before I implemented it into my own work.
The first things I did to sample.php was to relink all the paths I changed such as img to images as well as /AjaxSearch to /lib/AjaxSearch. I then ran sample.php to see if everything uploaded properly and it worked. Much to my dismay it did not. Turns out there is a problem in PHP4 with html_entity_decode() which is discussed in a bug report here but has no fix for PHP4.3.x only PHP5. Since I do not control the PHP version on my server I simply disabled the lines that did the transcoding in /lib/AjaxSearch/_doAjaxSearch.action.php on lines 67, 68, 71, and 72. On lines 67 and 28 I just commented the lines out and on 71 and 72 I removed the function call only. This seemed to allow it work anyways and the comments aboved stated that they were not necessary if the results are coming from mysql.
Next I wrote the plugin.name.php for my database so the search results weren’t the default country/capital data. Below is my file for searching my audio table. Notice I use adodb for this site so adjust yours to match whatever you are using. The two parts to this was to replace the sample string data with an sql call. and then to adjust the final section from creating an array to just using the one we get from adodb. This should be pretty straight forward to anyone who has done php/mysql programming before.
*/
#@ Detect if loaded by search_engineif ( ! SEARCHENGINE_LOADED ) return false;
#@ Category Name
$searchCat_name = ‘Audio’ ;
#@ Do search
global $db, $prefix;
$sql = ‘SELECT * FROM ‘.$prefix.‘_audio WHERE location LIKE “%’.$search_engine[‘options’][‘input’].‘%”
OR title LIKE “%’.$search_engine[‘options’][‘input’].‘%”
LIMIT 0, ‘.$search_engine[‘options’][’search_limit’] ;
//_dumpLog(”Search : query =>\n $sql \n”, 0) ;
$result = $db->getArray($sql) ;
#@ Transform txt in array
//$country_list = str_replace(”\”", ”, $country_list) ;
//$country_list = explode(”\n”, $country_list) ;
foreach ( $result as $item ) {
// Explode each lines
//$elements = explode(’,’, $country_list[$i]) ;
// Get elements to search and display
$id = “audio_”.trim($item[0]) ;
$location = trim($item[1]) ;
$title = trim($item[2]) ;
$stamp = trim($item[3]) ;
// Search on elements
if ( preg_match(‘/’.$search_engine[‘options’][‘input’].‘/i’, $title)
OR preg_match(‘/’.$search_engine[‘options’][‘input’].‘/i’, $location) ) {
// To display
$results[‘value’] = utf8_encode($title) ;
$results[‘info’] = utf8_encode($location) ;
// Store result
$search_engine[‘results’][$searchCat_name][$id] = $results ;
}
}
?>
Once we had that finished all you have to do is adjust the plugins default list in /lib/AjaxSearch/_doAjaxSearch.action.php to reflect your plugin files like so.
Now run your sample.php file and see if you can now search your database. If not your problem most likely lies in the plugin.name.php file you created. I had to debug and test mine a couple times to get it working right. If you have anymore problems feel free to comment below and I’ll try and help you.
Now that we are finished with hooking it up to our database it’s time to implement it into our project. This is very easy as we just need to copy over a few fields and divs from our sample.php file.
First you will want to copy over all the javascript from the header into the header for each page it will be on or in my case I have a global header file that is attached to all files and then adjust the following section to your needs. As you see I only changed the script location as well as the ghosted text, and then adjusted the result to submit the data to the form rather than display it.
$(‘.home_searchEngine’).bind(’submit’, function() {return false;} ) ;
// Set autosuggest options with all plugins activated
var options = {
script:“lib/AjaxSearch/_doAjaxSearch.action.php?json=true&limit=8&”,
varname:“input”,
json:true, // Returned response type
shownoresults:true, // If disable, display nothing if no results
noresults:“No Results”, // String displayed when no results
maxresults:8, // Max num results displayed
cache:false, // To enable cache
minchars:2, // Start AJAX request with at leat 2 chars
timeout:100000, // AutoHide in XX ms
callback: function (obj) { // Callback after click or selection
// For example use :
// Build HTML
//var html = “ID : ” + obj.id + “
Main Text : ” + obj.value + “
Info : ” + obj.info;
//$(’#input_search_all_response’).html(html).show() ;
// => TO submit form (general use)
$(’#asset’).val(obj.id);
//$(’#form_search_country’).submit();
}
};
// Init autosuggest
var as_json = new bsn.AutoSuggest(’input_search_all’, options);
// Display a little watermak
$(”#input_search_all”).Watermark(”ex : Walkerville”);
Finally copy over the input tags for the specific search field and the search_response div set below it and you should be ready to go!
Update
As per requested here is a zipped example of it working all together. I removed my ADOdb stuff and just used the native mysql statements to make it simpler but I prefer using ADOdb or mysqli in actual setups. facebook_autocomplete_db
posted on 13.02.2009
dan
Thanks for the demo, but I was hoping to get a little more info. (new to php). I am having trouble getting this functioning when pulling from my database. If I could get a little assistance I would be greatful.
posted on 13.02.2009
j_d3
Sure thing, can you post what you are trying to do and I’ll help where I can.
posted on 5.03.2009
Bjarne
Great turorial, I got autocomplete to work on my site, but without db support. And my search result is somehow in a html form “or something like that” on the same page. I want the result to be on its own php page.
Can you help me?
Thanx.
posted on 5.03.2009
Bjarne
Great tutorial, I got my webpage to work correctly, but without DB support, and the result is shown in “I think” html form on the same page. But I would rather have the result on it’s own page.
Can you help me?
Thanx
posted on 5.03.2009
bjarnet3
Great tutorial, I got my site on going with autoComplete, but without DB support and the result is shown in ((html form)).
Can you help me with DB support and show me how I can get the result to be shown in its own window.
Thanx
posted on 14.03.2009
Witja
Hey, very very nice idea to publish an tut like this.
could you please help me out? would be very nice^^
but i have some trouble writing the php for mysql db.
maybe i’m to stupid, or maybe it’s too hard
Heres my code:
\n $sql \n”, 0) ;
$result = mysql_query($sql) or die (”Konnte Abfrage nicht ausführen”) ;
#@ Transform txt in array
//$country_list = str_replace(”\””, ”, $country_list) ;
//$country_list = explode(”\n”, $country_list) ;
foreach ( $result as $item ) {
// Explode each lines
//$elements = explode(’,’, $country_list[$i]) ;
// Get elements to search and display
$id = “audio_”.trim($item[0]) ;
$plz = trim($item[1]) ;
$ort = trim($item[2]) ;
$vorwahl = trim($item[3]) ;
// Search on elements
if ( preg_match(‘/’.$search_engine[‘options’][‘input’].‘/i’, $title)
OR preg_match(‘/’.$search_engine[‘options’][‘input’].‘/i’, $location) ) {
// To display
$results[‘value’] = utf8_encode($title) ;
$results[‘info’] = utf8_encode($location) ;
// Store result
$search_engine[‘results’][$searchCat_name][$id] = $results ;
}
}
?>
I’ve just changed some options from your script but can’t get the result i want. plus i’m a newbie in php and mysql. would you mind fitting this script pls?^^
THX
posted on 27.03.2009
Henrik
I’m trying to have this to work on a MySQL database.
I feel i miss something about your ex.
global $db, $prefix; <—– where are these initialised in your code ?
Who sets SEARCHENGINE_LOADED to TRUE`?
posted on 27.03.2009
Henrik
Can you please zip your files so I can download your whole setup
Best regards
Henrik
posted on 27.03.2009
j_d3
Sorry guys I didn’t explain the sql really at all. The global $db, $prefix line is just that I declare those variables when the site starts and reuse them in each place I need. The $prefix is just a variable to hold different table prefix’s for me to run this on multiple databases. The $db is an ADOdb class which I use to handle all my database work. You could just drop these and use the php mysql statements or mysqli.SEARCHENGINE_LOADED is set by the search engine code from web2ajax. Give me some time to write this up again in as a demo and I’ll do a repost.
posted on 27.03.2009
j_d3
Posted the example but you’ll need to create a database called testbed and make a table autocomplete with an id key, name (VARCHAR 255), email (VARCHAR 255), and rdate (TIMESTAMP).