Skyline Groningen
Toegevoegd op

jQuery autocomplete tutorial with PHP and MySQL

My goal was to make an autocomplete on a site that passed also another value as the selected to the new page. I started with the documentation of jQuery but there was not much on the topic, so I started to Google to find out that it was hard to find the solution but a lot of people were looking for it. Thanks to combining the blogs of Christos Pontikis and AF Design I came to my solution. So, I wanted to create a autcomplete on customername and when a name is selected it should give the customercode to the next page and not the name. I have a MySQL database with a table called Customer with several fields but the important ones are : `id` int(11) NOT NULL AUTO_INCREMENT, `firstName` varchar(50) NOT NULL DEFAULT '', `lastName` varchar(50) NOT NULL DEFAULT '', `customerCode` varchar(6) NOT NULL DEFAULT '' For the HTML I used a simple inputfield (which can be in a form) : Using jQuery autocomplete one combines the autocomplete to the inputfield so at the page is a piece of Javascript. For this I inserted the jQuery scripts that you can find here. Time to add a small piece of Javascript to the page $(document).ready(function($){ $('#customerAutocomplte').autocomplete({ source:'suggest_name.php', minLength:2 }); }); What it does is adding the autocomplete functionality to the field customerAutocomplete. After typing at least 2 characters the script will send the entered value to the source, in this case suggest_name.php For this jQuery uses automatically the variable term, leading to a url like suggest_name.php?term=AB Knowing this I wrote the PHP page suggest_name.php <?php $mysqli = new MySQLi($server,$user,$password,$database); /* Connect to database and set charset to UTF-8 */ if($mysqli->connect_error) { echo 'Database connection failed...' . 'Error: ' . $mysqli->connect_errno . ' ' . $mysqli->connect_error; exit; } else { $mysqli->set_charset('utf8'); } /* retrieve the search term that autocomplete sends */ $term = trim(strip_tags($_GET['term'])); $a_json = array(); $a_json_row = array(); if ($data = $mysqli->query("SELECT * FROM Customer WHERE firstname LIKE '%$term%' OR lastname LIKE '%$term%' ORDER BY firstname , lastname")) { while($row = mysqli_fetch_array($data)) { $firstname = htmlentities(stripslashes($row['firstname '])); $lastname = htmlentities(stripslashes($row['lastname'])); $code = htmlentities(stripslashes($row['customercode'])); $a_json_row["id"] = $code; $a_json_row["value"] = $firstname.' '.$lastname; $a_json_row["label"] = $firstname.' '.$lastname; array_push($a_json, $a_json_row); } } // jQuery wants JSON data echo json_encode($a_json); flush(); $mysqli->close(); ?> What it does is reading the table Customer at line 14 looking for the string term in the first- or lastname. The results are added to a multidimensional array. When using a single array it would only return the value to have a autocomplete. The label is now used to send for instance extra html like Chris did in his example. I just kept it equal. The code is returned as well as you can see at line 19. Now that I have the values I need, I need to expand my script a little. I have to add the behaviour when one selects a name which is now done at line 5 and I redirect the user to the invoice page with the correct customercode. $(document).ready(function($){ $('#zoekNaam').autocomplete({ source:'suggest_name.php', minLength:2, select: function(event,ui){ var code = ui.item.id; if(code != '') { location.href = '/invoice.php?customercode=' + code; } }, // optional html: true, // optional (if other layers overlap the autocomplete list) open: function(event, ui) { $(".ui-autocomplete").css("z-index", 1000); } }); }); This should do the trick. (and ofcourse you can send more parameters when needed) I know the PHP is doing what it should but making it a little more safe is a wise thing to do. Therefor my suggest_name.php looks like : <?php require 'conf.inc.php'; /* prevent direct access to this page */ $isAjax = isset($_SERVER['HTTP_X_REQUESTED_WITH']) AND strtolower($_SERVER['HTTP_X_REQUESTED_WITH']) === 'xmlhttprequest'; if(!$isAjax) { $user_error = 'Access denied - direct call is not allowed...'; trigger_error($user_error, E_USER_ERROR); } ini_set('display_errors',1); /* if the 'term' variable is not sent with the request, exit */ if ( !isset($_REQUEST['term']) ) { exit; } $mysqli = new MySQLi($server,$user,$password,$database); /* Connect to database and set charset to UTF-8 */ if($mysqli->connect_error) { echo 'Database connection failed...' . 'Error: ' . $mysqli->connect_errno . ' ' . $mysqli->connect_error; exit; } else { $mysqli->set_charset('utf8'); } /* retrieve the search term that autocomplete sends */ $term = trim(strip_tags($_GET['term'])); /* replace multiple spaces with one */ $term = preg_replace('/\s+/', ' ', $term); $a_json = array(); $a_json_row = array(); $a_json_invalid = array(array("id" => "#", "value" => $term, "label" => "Only letters and digits are permitted...")); $json_invalid = json_encode($a_json_invalid); /* SECURITY HOLE *************************************************************** */ /* allow space, any unicode letter and digit, underscore and dash */ if(preg_match("/[^\040\pL\pN_-]/u", $term)) { print $json_invalid; exit; } /* ***************************************************************************** */ if ($data = $mysqli->query("SELECT * FROM Customer WHERE firstname LIKE '%$term%' OR lastname LIKE '%$term%' ORDER BY firstname , lastname")) { while($row = mysqli_fetch_array($data)) { $firstname = htmlentities(stripslashes($row['firstname'])); $lastname = htmlentities(stripslashes($row['lastname'])); $customercode= htmlentities(stripslashes($row['customercode'])); $a_json_row["id"] = $customercode; $a_json_row["value"] = $firstname.' '.$lastname; $a_json_row["label"] = $firstname.' '.$lastname; array_push($a_json, $a_json_row); } } /* jQuery wants JSON data */ echo json_encode($a_json); flush(); $mysqli->close();