My goal was to make an autocomplete on a site that passed also another value as the selected to the new page.
I have a MySQL database with a table called Customer with several fields but the important ones are :
For the HTML I used a simple inputfield (which can be in a form) :
I have a MySQL database with a table called Customer with several fields but the important ones are :
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
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
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.
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 :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
|
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();
|
No comments:
Post a Comment