Jquery Autocomplete remote datasource

Question

Im trying to use Jquery UI's autocomplete feature to query usernames on my database. So the user enters a username similar to one on my db and the autocomplete is suppossed to guess what they are looking for in a drop down. Unfortunately, I can't get the backend script to return suggestions.

<?php

sleep( 3 );
// no term passed - just exit early with no response
if (empty($_GET['term'])) exit ;
$q = strtolower($_GET["term"]);
// remove slashes if they were magically added
if (get_magic_quotes_gpc()) $q = stripslashes($q);

$sql = "SELECT * FROM users";   
$r = mysql_query($sql);
$items = array();
if ( $r !== false && mysql_num_rows($r) > 0 ) { 
    while ( $a = mysql_fetch_assoc($r) ) {  
        $username = $a['username'];
        array_push($items, $username);              
        }
}

$result = array();
foreach ($items as $key=>$value) {
    if (strpos(strtolower($key), $q) !== false) {
        array_push($result, array("id"=>$k, "label"=>$key, "value" => strip_tags($key)));
    }
    if (count($result) > 11)
        break;
}

// json_encode is available in PHP 5.2 and above, or you can install a PECL module in earlier versions
echo json_encode($result);
/* echo $items; */

?>

The script simply returns an empty array, even when it should return a result. I have no idea what is wrong here..

score 1 · Accepted Answer · edited May 23 '17 at 12:01

1

First let me say, querying the database and returning the entire table to sift through for your results is a poor method. The SQL queries will execute faster if they are filtering the data from the database. You have to call up the data anyways, why not filter it and return only the relevant results?

You need to send the query a Like parameter as in the following:

$sql = "SELECT * FROM users where username like :term";

(I'm using parameterized queries in this case which you should use to protect against SQL Injection attacks.)

You can also use the more precarious method as follows:

$sql = "SELECT * FROM users WHERE username = ". $term;

Reference for Parameterized Queries: How can I prevent SQL injection in PHP?

edited May 23 '17 at 12:01

Community

1
1

answered Jul 29 '12 at 23:54

Desirea Herrera

421
4
9

Hmm, but do you have any idea why the script is returning an empty array? – Thomas Jul 30 '12 at 15:35
it looks like your offset value $q is assigned a string $q = strtolower($_GET["term"]); $q is your offset and should be a numerical value of where to start searching in strpos per the documentation: offset If specified, search will start this number of characters counted from the beginning of the string. – Desirea Herrera Jul 31 '12 at 14:43

Jquery Autocomplete remote datasource

1 Answers1