TalkPHP - Auto Suggest Using PHP/MySQL & Ajax

I have come up with this code that gives you a nice google style suggest.

You can view a demo Here

Downloads

script.aculo.us 1.7.0 - This file is needed for this tutorial. Once you have downloaded the file unzip it and you should be presented with couple folders. We only need the files that are stored in src and lib folder. Create a folder under your website directory or where ever you are storing your website and call it javascript or something along them lines and put everything from the src folder and the one file thats in the lib folder

Ready to start

Once you have done that we can now start coding. Firstly we need to connect to them files we just put in a new folder, as they store the classes we need for the autosuggest to work.

PHP Code:


		
			
<script src="javascript/prototype.js" type="text/javascript"></script>

<script src="javascript/scriptaculous.js" type="text/javascript"></script>

The above code will save you time and code space because the scriptaculous.js will include all the needed files in that folder for what we want to accomplish.

Now we have included the files we needed. We can start constructing the page. We need to use DIV tags and input field to set up things.

PHP Code:


		
			
<label for="bands_from_the_70s"></label>

 <form name="form1" method="post" action="">

  <label for="bands_from_the_70s"></label>

  <input name="text" type="text" id="bands_from_the_70s" value="" size="15" autocomplete="off" />

  </p>



  <label></label>

  <input name="Go" type="submit" id="Go" value="Go">

</form>

<div class="page_name_auto_complete" id="band_list"></div>

The suggestions will appear where you see the div using the ID "band_list"

PHP & MySQL

Now we are going to be using PHP & MySQL to access a database and use the information found as the autosuggest information. Firstly of course we need to connect to the actual MySQL server and database. I would suggest creating a new file called config.php and put the following information in.

PHP Code:


		
			
<?php

########SERVER DETAILS##########

$host = "localhost";

$dbname = "dbname";

$dbuser = "dbusername";

$dbpass = "dbpassword";

$l = mysql_connect  ( "$host" , "$dbuser" , "$dbpass" ) or die("Error connecting:<BR><BR>".mysql_error());

mysql_select_db( "$dbname" ) or die("Error getting db:<BR><BR>".mysql_error());

################################

?>

Or use your own method of connecting to the database.

Now we can construct the code to place the information from the database into arrays. The following code may seem long winded or confusing, but i found it worked fine.

PHP Code:


		
			
 <?php



        //echo a javascript using the Autocomplete function in 

        //scriptaculous, firstly we define the field we want to

        //use for the autosuggest to use, then we use the div where

        //we want to display the results (local(field,div, array of suggestions)

    echo "<script type=\"text/javascript\">

    new Autocompleter.Local('bands_from_the_70s', 'band_list',["; // use PHP to loop all the results into the array

    ?>

  <?php

    $get = mysql_query("SELECT * FROM bands")or die(mysql_error());

    while($r = mysql_fetch_array($get)){

    echo "'$r[bandname]',";}

    echo "'No band'],{}"; //end the javascript code

    echo "); \n</script>";

?>

What we have done is basically used the following code, but just added a PHP loop to put all the records from the database into an array. Without it the code would look like the following

PHP Code:


		
			
<script type="text/javascript">

    new Autocompleter.Local('bands_from_the_70s', 'band_list',[  'History of violence','the hard easy','a t l','James Brown','Downfall','Walk the line','Kill Bill','Kill Bill 2','Kung Fu Hustle','Lord Of The Rings 1','Lord Of The Rings 2','Slipknot','The Matrix 1','Lord Of The Rings 3','Paparoach','Audio Slave','American Pie 1','bonanza','beverley hillbillies','nightmare on elm street','money kings','hellboy','sin city','jasonx','chronicles of riddick','donnie darko','edward scissorhands','from dusk till dawn','underworld','x men the phoenix saga','lord of the rings the fellowship of the ring','lord of the rings the two towers'],{}); 

</script>

IMPORTANT: When using this script, the script won't correctly work if any of the records from the database contain ' Example: Jack's

Now thats pretty much it. From here it should work once you put it all together. I have supplied the full code below.

PHP Code:


		
			
<?php

include("config.php");

?>  



<script src="javascript/prototype.js" type="text/javascript"></script>

 <script src="javascript/scriptaculous.js" type="text/javascript"></script>



<style type="text/css">

<!--

body,td,th {

    font-family: Arial, Helvetica, sans-serif;

    font-size: 12px;

}



.page_name_auto_complete li{

border: solid thin; 

padding:5px; 

list-style:none;

}



.page_name_auto_complete li:hover{

border: solid thin; 

padding:5px;

background-color:#333333;

color:#FFFFFF;

list-style:none;

}

-->

</style>

  



<label for="bands_from_the_70s"></label>

 <form name="form1" method="post" action="">

  <label for="bands_from_the_70s"></label>

  <input name="text" type="text" id="bands_from_the_70s" value="" size="15" autocomplete="off" />

  </p>



  <label></label>

  <input name="Go" type="submit" id="Go" value="Go">

</form>

<div class="page_name_auto_complete" id="band_list"></div>



  <?php



    echo "<script type=\"text/javascript\">

    new Autocompleter.Local('bands_from_the_70s', 'band_list',[";

    ?>

  <?php

    $get = mysql_query("SELECT * FROM band")or die(mysql_error());

    while($r = mysql_fetch_array($get)){

    echo "'$r[band]',";}

    echo "'No DVD'],{}";

    echo "); \n</script>";

?>

You can off course do the arrays in a better way i am sure. At the time i couldn't think of one lol.