2 Autocomplete/suggestion input boxes using jQuery or Ajax with second box based on first selections with multiple items

Question

I have tried many jquery and ajax auto complete scripts. I am finding it very hard to try and integrate any of them into what I need.

Let me explain what I am trying to achieve.

I need 2 autocomplete boxes. The second pulls data from the first. Also they must both support multiple entries. Querying a DB for the data. I am using local data for now but can change that in the future.

We will use State and City as examples.

I have a mysql DB and it contains:- id,state,city. I was using a JSON format to pull in the results.

So if we choose 2 States in the first box. I'm in Spain, so say Malaga & Cadiz. Then in the second autocomplete box for City, instead of querying my DB for all Cities it only gives results for the Cities in Malaga & Cadiz only.

None of the scripts I have used seem to have detailed enough explanations for me to achieve this. I am quite familiar with PHP but a complete noob when it comes to JS-jQuery/JSON/Ajax. I made progress to the extent that I can get the first autocomplete box working and pulling the 2 states but after that I cannot seem to make any head way.

The script I was using currently was Drew Wilsons Auto suggest. http://code.drewwilson.com/entry/autosuggest-jquery-plugin

If what I am trying to achieve can be done with this script great. I am also open to other suggestions scripts or ideas.

Here is some of the code from my php script. (p3.php) It does not work how I need it to, as I seem to have got stuck somewhere. It does select multiple States though! A good start at least.

<?php
// DB connection details (removed from here)
//DB Connection
$dblink_main = mysql_connect($host_main,$user_main,$pass_main);
$selectdb_main=mysql_select_db($database_main,$dblink_main);

// changed default q in autosuggest.php!
$input  = $_GET["plkup"];
//$input2   = $_GET["clkup"];
//$province2    = $_GET["province2"];
//$province = 'Cadiz';
//$province2    = "Cadiz";

$data = array();
// query your DataBase here looking for a match
$query = mysql_query("SELECT distinct province FROM spanish_regions WHERE province LIKE         '%$input%'");
// query 2 - doing this wrong!
//$query2 = mysql_query("SELECT city, province FROM spanish_regions WHERE province LIKE     '%$input2%'");

while ($row = mysql_fetch_assoc($query)) {
$json = array();
$json['province'] = $row['province'];
$data[] = $json;
}
//while ($row = mysql_fetch_assoc($query2)) {
//$json = array();
//$json['city'] = $row['city'];
//$data[] = $json;
//}

header("Content-type: application/json");
echo json_encode($data);
?>

HTML code.

<form action="">
<label>Province: </label><input type="text" id="province" name="" size="20" value=""         autocomplete="off" />
</form>
<form action="">
<label>City: </label><input type="text" id="city" name="" size="20" value=""     autocomplete="off" />
</form>

<script language="javascript" type="text/javascript">
jQuery(function(){
jQuery( "#province" ).autoSuggest("p3.php",  {selectedItemProp: "province",         selectedValuesProp: "province", searchObjProps: "province", queryParam: "plkup", minChars:     1, matchCase: false}); 
});
// Below does not work. Again I am probably going around this wrong way.
jQuery(function(){
jQuery( "#city" ).autoSuggest("p3.php",  {selectedItemProp: "city", selectedValuesProp:     "city", searchObjProps: "city", minChars: 1, queryParam: "clkup", matchCase: false}); 
});
</script>

I really am not sure how to go about getting the 2nd autocomnplete box to get data from the db filtering by just the states selected in the first. Any ideas or solutions or thoughts would be greatly appreciated.

Many thanks Mark.

Answer 1

Here's a very simple procedure. I will just show the main skeleton. Adding flash-meat will be on your part.

Suppose we have two <select> in ajax.php like this:

   <select id="parent">
        <option value="1">value1</option>
        <option value="2">value2</option>
        <option value="3">value3</option>
        <option value="4">value4</option>
    </select>

    <select id="child">

    </select>

So what we need? We need to populate the 2nd <select> on event of its selection.

Here comes the javascript part (ajax.php)

---

<script type="text/javascript">
            $(function(){                    
                $('#parent').change(function(){ //on change event
                var parentVal = $('#parent').val(); //<----- get the value from the parent select
                $.ajax({
                    url     : 'process.php', //the url you are sending datas to which will again send the result
                    type    : 'GET', //type of request, GET or POST
                    data    : { parentValue: parentVal}, //Data you are sending
                    success : function(data){$('#child').html(data)}, // On success, it will populate the 2nd select
                    error   : function(){alert('an error has occured')} //error message
                })
            })

            })
    </script>

This will send the request to process.php

Now comes the process.php

---

Here we need the data to populate the 2nd <select>

Now what goes inside <select>?

<option value="someValue">someText</option>

You need to output something like this:

    <option value="11">value11</option>
    <option value="12">value12</option>
    <option value="13">value13</option>
    <option value="14">value14</option>

Value and the text inside fill up with your needs.

As it will come from datanbase:

• You can get the parent select value in process.php with $_GET['parentValue']
• With that value, query the database
• Then from the result returned from the database, use a loop while, foreach or for (depending upon the way you are retreiving it), make the <option value="someValue">someText</option>.
• you are done!

For multiselect

to make life little bit easier, you can use chosen multiselect

An advice

mysql_* is deprecated. use PDO instead. Here's a link to a tutorial