jQuery Autocomplete Pull from Mysql Database

Question

What I'm trying to solve: User, inputs a zipcode like 08212, or any US zipcode for that matter and jquery UI Autocomplete calls this source file: ../src/php/registration/getFanLoc.php, that file does a query against the database for certain fields (described below) based on the zipcode entered.

Have a form/jquery on index.php :

Assume that all the jquery UI code is already in this file

<script type="text/javascript">
    $(document).ready(function()
    {
        $('input#zip').autocomplete({
            dataType: "json",
            source: "../src/php/registration/getFanLoc.php",
            minLength: 3
        });
    });
</script>

<form method="post" id="FanDetail">
  <div class="ui-widget">
     <label for="zip">Zip: </label>
     <input id="zip" name="zipcode" value="US Zipcode" onFocus="clearText(this)" /><br />
  </div>
</form>

the getFanLoc.php file - that queries mysql for any records corresponding to the 5 digit zipcode entered:

     <?php
    try{
        ///////////////////////////////////////////////////////////
        $zip = mysql_real_escape_string($_GET['zipcode']); //////
        ///////////////////////////////////////////////////////////
        require_once('../../cfg/dbi.php') or die('Cant require dbi');
        $dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );  
        $sth = $dbh->prepare(
        'SELECT fanDetLocID, fanDetLocCity, fanDetLocState FROM fanDetailLocation WHERE fanDetLocZip = ?');
        $sth->bindParam(1, $zip);
        $sth->execute();
    while ($res = $sth->fetch(PDO::FETCH_ASSOC)) {
        foreach($res as $column=>$val) {
            $res[$i][$column] = $val;
        }
        $i++;
        }   
    print json_encode($res);
} 


    catch(PDOException $e){
        file_put_contents('../../../PDODBConnectionErrors.txt', 'ERROR: [getFanLoc.php] about '.$e->getMessage().'<br>', FILE_APPEND);  
    }



    ?>

I was originally having trouble, because it wasn't connecting to the database and PDODBConnectionErrors.txt had the errors, but I've since FIXED that part, and now it doesnt do anything, but now doesnt throw an error in the file either. Anyone see what I'm doing wrong? I'm pretty newbie working with the autocomplete jQuery UI widget, so go easy on me :) .

Changes I've tried:

• Tried adding in the $_GET['zipcode'] above, since the commenter below thought this plugin used $_GET instead of $_POST . Changing it from $_POST to $_GET was correct, but now I face the below problem when trying to access the file directly:

• Also tried just accessing the file directly like getFanLoc.php?zipcode=08039 this seemed to atleast get me a little closer . *The page now states * False when I access it from this url, which makes me think my while() foreach() loop is erroneous. Does someone seem something strikingly strong with it?

To add: I am using this plugin: http://jqueryui.com/demos/autocomplete/ . Thanks @Sennet

**The FIXED While() loop (thanks to Sennett) works on direct access, but not on form:

    $jsonArray = array();
    while ($result = $sth->fetch(PDO::FETCH_ASSOC)) {
     $jsonArray[] = array(
          'label' => $result['fanDetLocID'], 
          'value' => $result['fanDetLocCity']);
    }
    print $jsonArray; // print results of array
    print json_encode($jsonArray); // json encode that array
}   // can't have code between here

// and here
    catch(PDOException $e){
        file_put_contents('../../../PDODBConnectionErrors.txt', 'ERROR: [getFanLoc.php] about '.$e->getMessage().'<br>', FILE_APPEND);  
    }

After 4 hours of working with Sennet, we now have:

<script type="text/javascript">
$(document).ready(function()

{ $('input#zip').autocomplete({

    dataType: "json",
    source: "../src/php/registration/getFanLoc.php",
    minLength: 3,
    select: function(event, args){
        event.preventDefault();
        var joinedValues = args.item.value;
        var id = joinedValues.split("|")[0];
        var cityAndState= joinedValues.split("|")[1];
        document.getElementById('actualZip').value = cityAndState ; 
            document.getElementById('zip').value = id;
    }
});

});

Answer 1

First thing to do would be to try accessing the file directly the webbrowser. Leave it as $_GET. What happens when you browse to getFanLoc.php?zipcode=XXX where XXX is a code known to exist? Do you get any results?

Also, assuming you are using this plugin, you should return JSON and not a load of hyperlinks - the plugin will wrap them in HTML for you.