Jquery Autocomplete with JSON beginners question

Question

I am using the official Jquery Autocomplete plugin. I have an ODBC database with a list of advertisers. I currently have an input box where the user types in part of the name and I am successfully returning a list of partial matches. The problem I'm having is I do not understand how to return both the name and the ID of the advertiser. I am assuming I will want to use a Jquery function to adjust the value of a hidden field to store the ID after retrieving it.

php file to return the advertiser name that needs to be modified to return the UsageNumber as well:

$sql = "SELECT DISTINCT tNAdvertisors.UserName, tNAdvertisors.UsageNumber
FROM tNAdvertisors
WHERE (((tNAdvertisors.UserName) Like '$term%'));";
$rs=odbc_exec($conn,$sql);
$a = array();
while (odbc_fetch_row($rs)) {
    $a[] = htmlentities(odbc_result($rs,"UserName"));
}
echo json_encode($a);

current jquery code for the autocomplete that needs to be modified to include a function to handle the ID?

    $("#single").autocomplete({
    source: "ajaxSearchForAdvertiser.php",
    minLength: 3
});

Thank you for your time and help.

Answer 1

From looking at the autocomplete demo page image search, you could try combining the important information into a string, say the ID with the advertiser's name from the database (sorry I can't help with that since I don't know php & SQL well enough):

1234#Exxon
2345#BP
3456#Shell

Then the autocomplete script would be something like this:

$("#imageSearch").autocomplete("images.php", {
 formatItem: function(data, i, n, value) {
  return value.split("#")[1]; // returns just the name for display in the input box
 },
 formatResult: function(data, value) {
  var result = value.split('#');
  return [ result[0], result[1] ]; // returns [ ID, name ]
 }
});