jQuery autocomplete with json/ajax and odbc results

Question

Hope to find an answer.

I build an web-app in PHP where I have a list of records and some search fields. One of the search fields is "customernumber". Customernumbers are saved in the local database (MySQL). If I search on an customernumber, it gives me the according result-row of the database => logic.

Now I want to build an extention for this search field with jQuery UI Autocomplete.

What I want to do:

I want the user to be abel to search on customerNAME in the searchfield "customernumber" and Autocomplete wil give the correct customernumber into the value.

The customername is saved in an external ODBC database also with the customernumber.

What I have done already:

I've tried to fetch all the names and numbers from the external ODBC database ans I got a list like this:

customername: somename1 
customernumber: number1

customername: somename2
customernumber: number2

customername: somename3 
customernumber: number3

... and so on

If I do autocomplete with an ajax call too this list, it doesn't work at all.

Can someone PLEASE help me!? After several houwers of search, I couldn't find the answer on the internet or anything close to it.

I would be really greatfull! Thanks!

My Example of what I got so far:

jQuery script:

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

        $( "#customer" ).autocomplete({
            source: function( request, response ) {
                $.ajax({
                    url: "customers.php",
                    dataType: "json",
                    success: function( data ) {
                        response( function( item ) {
                            return {
                                label: item.customername,
                                value: item.customernumber
                            }
                        });
                    }
                });
            },
            minLength: 3,
            select: function( event, ui ) {
                log( ui.item ?
                    "Selected: " + ui.item.label :
                    "Nothing selected, input was " + this.value);
            }
        });
    });
</script>

Input HTML:

<div class="ui-widget">
    <label for="customer">Search</label>
    <input id="customer" />
</div>

SQL Query in customers.php

$connection = odbc_connect('host', 'username', 'password') 
              or die('Connection failed!');

$sql = "SELECT 'customername', 'customernumber' FROM 'database.table'";

$results = odbc_exec($connection, $sql);

while($myRow = odbc_fetch_array($results)){
    $rows[] = $myRow;
};

foreach($rows as $row) {
    foreach($row as $key => $value) {
        echo $key . ': '. $value . '<br/>';
    }
};

To be clear

I type into the searchbox the customerNAME, and I want to retrieve the customerNUMBER as the value.

Answer 1

You have to pass data back in the proper format.

 while($r = odbc_fetch_array($results))
    $rows[] = array("label" => $r[0], "value" => $r[1]);    
 echo json_encode($rows);

 $("#search").autocomplete({  
        //define callback to format results  
        source: function(request, response){  
            //pass request to server  
            $.getJSON("query.php?term=" + request.term, function(data) {  
               response(data);
            });  
        });  
    });

See jQuery UI documentation for more options.

EDIT Issues with your current solution

• odbc_fetch_array might return values without column names on some RDBS's (use print_r to debug)

  name number Bob 123

becomes

Array
    (
        [0] => "Bob"
        [1] => 123
    )

• JSON output is not even valid (use firebug to debug), currently output for our example would be:

  0:"Bob" 1:123