jQuery autocomplete field not autocompleting when getting data from remote database

Question

I'm trying to get a jQuery autocomplete field to work with data from a remote DB, and I'm having no luck. Here's what I've attempted so far:

1.I have all of the necessary links to the source code in my header.

2.I have the following jQuery script written:

<script>
 $(function() {
   function log( message ) {

      $( "<div>" ).text( message ).prependTo( "#log" );
      $( "#log" ).scrollTop( 0 );
    }

    $( "#projNo0" ).autocomplete({ //projNo0 is the name of the autocomplete field
      source: "projects.php",
      minLength: 2,
      select: function( event, ui ) {
        log( ui.item ?
         "Selected: " + ui.item.value + " aka " + ui.item.id :
         "Nothing selected, input was " + this.value );
      }
    });
  });
</script>

3.This is the PHP backend that's supposed to populate the autocomplete field projNo0 (the DB connection is handled elsewhere in the same file):

$return_arr = array();
if ($con2) //If the DB connection is successful
{
    //Get the user's input from projNo0
    $ac_term = "%".$_GET['term']."%";
    $query = $con2->prepare("SELECT CodeID FROM CodeTable WHERE 
        CodeID LIKE :term");
    $data = array('term'=>$ac_term);
    $query->execute($data);

    while ($row = $query->fetch(PDO::FETCH_ASSOC)) 
    {
      $row_array['CodeID'] = $row['CodeID'];
      array_push($return_arr,$row_array);
      //echo $row['CodeID']; //line used for testing
      //echo '<br />'; //line used for testing
    } 
}

4.And here is the HTML for my autocomplete field. It's part of an array:

<p class="ui-widget">
<input type="text" name="projNo[]" id="projNo0" value="<? php echo $projNo[0]; ?>" />
</p>

I can confirm that there's no problem connecting to the remote DB. I'm able to echo out the array of values the SELECT statement returns. The jQuery script only works, however, when I hardcode an array of values into it. (Replace source "projects.php" with ["Bob","Carol","Ted","Alice"], for example.) When I right-click on the field and inspect it, the network activity shows that projects.php is being called and that it's accepting my input, appending it as a GET variable. The status of that activity is "OK". But I get no drop-down list of autofill suggestions.

Where is the break between my application and the database? I'm assuming that's where the problem is, since the script works with hardcoded values.

What does your script return? Pure json? Did you include the jquery-ui css files? — Tobias Golbs
– Tobias Golbs, Commented Jan 16, 2014 at 17:06
Yes, the ui css files are there. the jQuery script works fine when I use a hardcoded array of values within it, but not when I try to get the values from my database. — Chris
– Chris, Commented Jan 16, 2014 at 17:21
And you checked that your script is returning something? Show some example output of your script. Did you checked client-side what you are receiving? — Tobias Golbs
– Tobias Golbs, Commented Jan 16, 2014 at 17:25
When I run this from within projects.php: echo json_encode(array_values($query->fetchAll(PDO::FETCH_COLUMN, 0))); using my own name as :term, I get ["Michael Benton"]. My name is in the database, so it's finding it okay. It's just not giving it to the JQuery script. — Chris
– Chris, Commented Jan 16, 2014 at 18:27

Eternal1 · Accepted Answer · 2014-01-16 17:25:22Z

1

Replace this

while ($row = $query->fetch(PDO::FETCH_ASSOC)) 
{
  $row_array['CodeID'] = $row['CodeID'];
  array_push($return_arr,$row_array);
  //echo $row['CodeID']; //line used for testing
  //echo '<br />'; //line used for testing
}

With this

echo json_encode(array_values($query->fetchAll(PDO::FETCH_COLUMN, 0)));

The point is - autocomplete expexts JSON-encoded string as an answer, and you return nothing from your script. Using array_reduce to get the resulting array is just my preference.

edited Jan 16, 2014 at 17:25

answered Jan 16, 2014 at 17:09

Eternal1

5,6253 gold badges34 silver badges48 bronze badges

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

12 Comments

Chris Over a year ago

I'm getting a syntax error when I copy/paste that. Is there a closing } or ) missing? Having trouble tracking it down.

Tobias Golbs Over a year ago

Which php version are you using?

Eternal1 Over a year ago

Semicolon at the end perhaps. And i'm an idiot, usually i use this when i need to pass value and label pairs to autocomplete.

Chris Over a year ago

We're on 5.3, so no problems there. Syntax error has been corrected, but the code you suggested didn't change anything, alas.

Eternal1 Over a year ago

Another try. Also, can you watch a response on your projects.php and tell what's in there?

|

Collectives™ on Stack Overflow

jQuery autocomplete field not autocompleting when getting data from remote database

1 Answer 1

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related