How to call MySQL source data for jQuery autocomplete?

Question

I am using jQuery autocomplete, and want to have a separate PHP file to house the data, instead of creating a variable (i.e. var availableTags = ["Red", "Orange", "Yellow", "Green, "Blue"]).

However, when doing this, the autocomplete will continually display all of the available options, and not filter out what the user types in.

Any ideas?

Javascript:

$(document).ready(function() {

    $( ".autocomplete" ).autocomplete({
            source: "../../db/autocomplete_list.php"
    });

});

autocomplete_list.php

require_once('../includes/connect.php');
$dbh = connect(DB_HOST,DB_USER,DB_PASSWORD,CM_DATABASE);

$colors = '[';

$sth = $dbh->query ("SELECT * FROM constants 
                     WHERE category='Colors' 
                     ORDER BY display");

while ($row = $sth->fetch ()) { 

    $colors.= '"'.$row[value].'", '; 

}

$colors = rtrim($colors, ', ');

$colors .= ']';

print $colors;

execution of autocomplete_list.php

["Red", "Orange", "Yellow", "Green, "Blue"]

EDIT:

Have updated my autocomplete_list.php file to the following. However, now the autocomplete isn't displaying my values. If I check the response in FireBug, it is showing the results correctly, it just appears that they aren't being passed back to the autocomplete.

require_once('../includes/connect.php');
$dbh = connect(DB_HOST,DB_USER,DB_PASSWORD,CM_DATABASE);

if(isset($_GET['term'])) {
    $mysearchString = $_GET['term'];
}

$sth = $dbh->query("SELECT category, value, display FROM constants 
                    WHERE category = 'COLORS' AND value LIKE '$mysearchString%'
                    ORDER BY display");

while ($row = $sth->fetch ()) { 
    print $row['display']."\n";         
}

No errors. Have FireBug open, and nothing. "GET dev.sample.com/db/autocomplete_list.php?term=l" with response of "Yellow", "Blue". — Michael
– Michael, Commented Nov 20, 2011 at 23:44
It should look like an array in the console. Is that the case? — Andrew Whitaker
– Andrew Whitaker, Commented Nov 21, 2011 at 0:44

Inna · Accepted Answer · 2011-11-20 17:05:31Z

1

the best way is to reduce the possible options on server side:

$sth = $dbh->query ("SELECT * FROM constants 
                     WHERE category='Colors' and value like '.".$_GET['term']."%'
                     ORDER BY display");

(with $_GET['term'] replaced by a string checked against sql injections..)

see: http://docs.jquery.com/UI/Autocomplete

answered Nov 20, 2011 at 17:05

Inna

3673 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Michael Over a year ago

Made the changes, but now the autocomplete is not displaying on my live form. When I check the console on Firebug, it is calling everything correctly. Any ideas?

Michael · Accepted Answer · 2011-11-21 00:46:45Z

1

Found a solution here:

http://www.jensbits.com/2010/03/29/jquery-ui-autocomplete-widget-with-php-and-mysql/

require_once('../includes/connect.php');
$dbh = connect(DB_HOST,DB_USER,DB_PASSWORD,CM_DATABASE);

$return_arr = array();

if(isset($_GET['term'])) {
    $mysearchString = $_GET['term'];
}

$sth = $dbh->query("SELECT category, value, display FROM constants 
                    WHERE category = 'COLORS' AND value LIKE '$mysearchString%'
                    ORDER BY display");

while ($row = $sth->fetch PDO::FETCH_ASSOC()) { 

     $row_array = $row['display'];

     array_push($return_arr,$row_array);

}

echo json_encode($return_arr);

answered Nov 21, 2011 at 0:46

Michael

2,29416 gold badges49 silver badges80 bronze badges

Comments

no_name_here · Accepted Answer · 2011-11-20 16:54:09Z

0

source propery expect an array of values, not a string. Try to echo the content of "autocomplete_list.php" directly onto the script, something like this:

source: <?php include "../../db/autocomplete_list.php"; ?>

That will should work.

answered Nov 20, 2011 at 16:54

no_name_here

1362 bronze badges

1 Comment

Michael Over a year ago

Thought about that, but you can't do a PHP include in Javascript.

Collectives™ on Stack Overflow

How to call MySQL source data for jQuery autocomplete?

EDIT:

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

EDIT:

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related