Multiple dropdown with jquery ajax and php

83

Filtering results or providing option to users based on some criteria is one of the most sought after thing in web development. User friendliness is the key, So in this tutorial I am going to explain how can you create multiple drop down with jquery ajax and php for filtering options based on some criteria.

View Demo

Multiple drop downs: what will this script do?

Let’s take a situation where a user submits a form, he has to fill up his country, state and city but state must be dependent on the selected country and so is the city which depends on the state. So this script provides the solution for this problem.

Setup your database first. Create three tables viz. country, state, city.

Below is the sql for creating country table in mysql.

Now create state table. In the state table add a field named country_id so you can make relation between both the table.

And finally create the city table and just like state table create a field named state_id to manage the relation between the state and city tables.

Database set. Start coding!

Create a php file name configure.php that will have the database connection code.

In the landing page display all the countries in a drop down. Bind a function showState() with the drop down onChange event so you can use it in jquery ajax call.

Create two div container where you want both your state and city drop down to be displayed.

Write the jquery code inside the function showState(). Add an ajax function that will call fetch_state.php which will return the drop down with the results based on the country.

In the page fetch_state.php assign the country_id in a variable and then execute a query based on that country id.

Just repeat the above two step for fetching city, write the javascript/jquery code for fetching the city drop down via ajax, and again the php file that will return the city drop down.

The file fetch_city.php that will return the city drop down.

View Demo
Download

Share.
 

About Author

Entrepreneur & Dreamer

I am a passionate Software Professional, love to learn and share my knowledge with others.

Software is the hardware of my life.

83 Comments

  1. Hi

    This is a pretty good tutorial.

    I have a question, how can I use city_id (after selecting third drop down) further on index.php

    • Hi. It’s quite simple. just like create a div with an unique id like say then bind a function say showMore(); for city dropdown box(onchange) and call and ajax function just the way the state and city is called. Hope this helps 🙂

      • See I have these 3 dropdown then a text box. Now when I will be submitting the form, it will go to ‘action’ page there I will be having the value entered in the text box like $x=$_POST[‘name_of_text_box’];
        but not the value of selected last drop-down for further SQL query.
        How can I get that selected drop-down value can you let me know?

  2. Great Tutorial. I have two questions. Is it possible to have all 3 drop downs show at the same time? instead of showing one drop down at a time? also is it possible to show all the results and then filter them out as selections are made?

    • yes, it’s possible you can put static 3 dropdown all at once, but maintain their name and ids as it is..for the second question it can be done too by filtering results by values placing them in a hidden div and calling them as the selections are made.. but that is not a correct flow according to me as it has a lot of work overhead. It’s quite simpler this way.. “Why make things complex when they can be done the easier way.” take care

  3. hello .. thank you very much for this nice tutorial , I realy I need this code badly, I wonder if you can help me use the $country_id and $city_id and $state_id that the user had select from the menu in database operation like insertion and update and so on ….. thanks alot

    • if you need some tutorial regarding that. you can send me using “Request a tutorial” section in the menu bar. Please provide me with detail what you want. I will surely help u.

  4. Manish Prajapati on

    Hello Shahrukh Khan , First of all. this is very nice tutorial.
    i like it ur way of code. i am new to ajax and jquery. so what i want that, when country , states, city were selected after that two input box will apear as first name and last name. can u do me favor this. ?

    i want like this. by ajax .

  5. Art Reserve Bank on

    Great simple tutorial – Thanks!

    I like to use these multiple dropdowns to edit existing data.

    Having the country set as ‘selected’ is of course no problem. But is it possible to have states and cities shown with options marked as selected *without* changing the country?
    In other words: how to set default ‘selected’ items?

    • for that case you have to tweak the code, first replace the code in in their dropdown from showState(this); showCity(this) to

      showState(this.value); showCity(this.value)

      next set the variable
      var country_id = sel.options[sel.selectedIndex].value;
      var country_id = sel;

      var state_id = sel.options[sel.selectedIndex].value;
      var state_id = sel;

      now final part is..
      suppose u make india as selected.. india ha say id = 1
      in the bottom of page write

      // pass valid country id and state id in the function.
      showState(1);
      showCity(5);

      • Art Reserve Bank on

        Thanx for prompt reply.
        I changed the code accordingliy but it doesn’t seem to work. I am not sure but looks like the two variables (like ‘country_id’ in function showState) are conflicting. Have you tried it?

        • Please try to debug the error. there must be some syntax mistake. I have told u the logic what needed to be done. I am sure it will be some silly mistake.. Try and let me know.

          • Art Reserve Bank on

            Run debugger (but I am not very familiair with Jscript). After altering onchange action showState(this) to showState(this.value) I get an error with the line:

            country_id = sel.options[sel.selectedIndex].value;

            [error: undefined or empty reference]

          • Art Reserve Bank on

            I understand. I want to show just Country and State.
            I load page with default Country and default State both set by the record from database.

            Page loads with correct country selected in Country dropdown.
            function showState(sel) calls fetch_state.php which loads dropdown with the correct States of the selected Country. Great! But the default State is not selected. Dropdown States opens with the option “Please select”.
            That’s what I mean that the function showState() should send an extra parameter (‘sel’ and ‘defaultstate_id’) to fetch_state.php in order to check which State should be marked as ‘selected’ in de Ajax-generated dropdown.

  6. chandrasekhar pachari on

    how to update the data using this did u get my intension. let me explain u

    suppose i have inserted data like this into a table

    insert into details(id,name,countyr_id,state_id,city_id) values(‘1′,’sekhar’,’1′,’2′,’1′);

    i want to update this data using ur drop down how is it possible.

    help me please………………. thanks

  7. hi i have 2 dropdown use first drown class and second dropdown student ,i insert dropdown value into database
    the first dropdown value insert into databse but second dropdown not inserted intio database ..please rply fast

  8. Please how do I get the value of the city_id and state_id….have to insert them into the database table…the only value I could get was country…I mean this:
    $country = $_POST[‘country’];
    $state = $_POST[‘state’];
    $city = $_POST[‘city’];

    The $_POST[‘state’] and $_POST[‘city’] are not holding any value at all…Please help

  9. hi boss,
    its really use full to me… thanks a lot…
    but, there is another problem other than this,
    i want to get activate 2 updated drop downs at a time… means if i select country as India it should show the both states and the city drop downs… how its possible plsss let me know with the code….

    Thank you…

  10. Hi Shahrukh Khan and thanks for you tutorials.
    I have the same problem of Pankaj. After I have selected the first dropdown, the second doesn’t appear.
    I new in php jquery ajax and I don’t know if I have to installa something, such as jquery and/or ajax.
    I have three file, first is configure.php, second is fetch_state.php and the last is fetch_city.php.
    Where I wrong?
    Best regards.

    • Hi Gia. I guess you have some JavaScript problem. Make sure your jquery file is also ok. The ID of the dropdown is binded with the onchange event i.e. the functions which fires and ajax call to the php script. best of luck

      • Hi Shahrukh and thanks for your reply. Now it works! I’m trying to modify the code in order to achive my goal, that is the following: when I select the third dropdown, I need to launch a query on mysql based on the item selected in the previous three dropdown menu and retrieve one row only. How can I do it? Best regards.

        • Create a function same as before accepts the value off three dropdown and fire and ajax request to the php page. and whatever the value is returned display it on the dropdown. The process of creating function and all are mentioned in the tutorial above.

          • Hi Shahrukh. Ok, but how I can create a function that accepts three values?
            This is the function:

            function showAttenuation(sel) {
            var frequencyMHz = sel.options[sel.selectedIndex].value;
            if (frequencyMHz.length > 0) {
            $.ajax({
            type: “POST”,
            url: “fetch_attenuation.php”,
            data: “frequencyMHz=” + frequencyMHz,
            cache: false,
            beforeSend: function() {
            $(‘#output3’).html(”);
            },
            success: function(html) {
            $(“#output3”).html(html);
            }
            });
            } else {
            $(“#output3”).html(“”);
            }
            }

            I have to declare more “data” in the ajax? How I can do it?
            Thanks and best regards.

          • you need to accept values store in a variable and in this link below append it.
            data: “frequencyMHz=” + frequencyMHz+”&variable2=value2&variable3=value3″

  11. Ok, but why it does not work?!

    I have in the index.php the following code:

    ……
    function showAttenuation(selFreq, selCabTyp) {
    var frequencyMHz = selFreq.options[selFreq.selectedIndex].value;
    var selCabTyp = document.getElementById(“tipoCavo”);
    var cableType = selCabTyp.options[selCabTyp.selectedIndex].value;
    if (frequencyMHz.length > 0 & cableType.length > 0) {
    $.ajax({
    type: “POST”,
    url: “fetch_attenuation.php”,
    data: “frequencyMHz=” + frequencyMHz+ “cableType=” + cableType,
    cache: false,
    beforeSend: function() {
    $(‘#output3’).html(”);
    },
    success: function(html) {
    $(“#output3”).html(html);
    }
    });
    } else {
    $(“#output3”).html(“”);
    }
    }
    ……

    while the fetch_attenuation.php is this:

    <?php

    require("configure.php");
    $frequencyMHz = ($_REQUEST["frequencyMHz"] “”) ? trim($_REQUEST[“frequencyMHz”]) : “”;
    $cableType = ($_REQUEST[“cableType”] “”) ? trim($_REQUEST[“cableType”]) : “”;

    if ($frequencyMHz “”) {
    //$sql = “SELECT attdB100m, attdB100ft, avgPwrkW FROM cable WHERE frequencyMHz = :freqid ORDER BY attdB100m”;
    $sql = “SELECT * FROM cable WHERE cableType = :cabtypid AND frequencyMHz = :freqid ORDER BY attdB100m”;

    try {
    $stmt = $DB->prepare($sql);
    $stmt->bindValue(“:cabtypid”, trim($cableType));
    $stmt->bindValue(“:freqid”, trim($frequencyMHz));
    $stmt->execute();
    $results = $stmt->fetchAll();
    } catch (Exception $ex) {
    echo($ex->getMessage());
    }
    if (count($results) > 0) {
    ?>
    Attenuazione:

    Please Select

    <option value="”>

    The three dropdown appear and works but the last query doesn’t go.
    May you help me?
    Thanks a lot.

  12. it is good…………. after clicking those dropdowns…… I want to add some images were i should add images? were i should give image references

  13. Hi Khan

    This tutorial is pretty good and useful. I kindly need a favor on how to sort data from other table using data from dependent dropdown. That means i will be able to sort all data from other table say ‘firstName’, ‘lastName’ and ‘phoneNumber’ with country,state and city selected from dropdown after clicking the sort button.

    Regards.

    • Hello Germano.
      Concept is very similar for your problem. just create dropdown and send value via ajax call. In the php page then get those value you have passed and create a query based on that and make sql call. Rest are similar.
      Eg. suppose you send value or want to sort with “firstname” send “?sort=fname” via ajax and in php page you have $sort = $_GET[“sort”];
      Now just in your sql append/update
      say you sql is $sql = “select * from table where 1”;
      if ($sort == “fname”)
      $extraSql = $sql.” ORDER BY firstname ASC “; (taking firstname is your column name)

      hope this helps.

  14. hello,
    Nice tutorial but I am facing such a problem
    SQLSTATE[HY000] [1045] Access denied for user ‘abcd’@’localhost’ (using password: NO)

  15. Many thanks for your wonderful tutorial. I use netbeans and have constructed all the tables in xampp and links as well as the index.php and the other php files. Sadly the construction of the ‘landing page’ which I think you mean the index page is not part of the integration that is easily comprehended. At least by me! I have looked at your css and html results in my browser and tried to understand what files or scripts you put here and there with little comprehension. I think it is the ‘integration’ linking and schematic outline of all elements of any project that poses the greatest challenge to most folks particularly when many ‘types’ of programing languages are used.
    Would love to comprehend the integration more fully.
    cheers,
    John

  16. hey,friend sahrukh,just awesome tutorial…
    But i want to show all filtered values like country_name, state_name,city_name as output in a single page….
    then what i have to do? please help me out ,its so important to me. thanks in advance.

  17. Hi Shahrukh,
    Very nice and useful tutorial and thanks.
    I have tried to connect it using sql server but i am unable to select the country, state and city.Please kindly help me on how to connect it using sql server which i have installed on my laptop

  18. Hi shahrukh
    Very nice and useful tutorial and thanks.
    But can i use this code with single table like id,country,state,city?
    So please how to use this with single table.

  19. Christopher Astbury on

    Hi Really nice example, that I have working, how can I echo the results onto a webpage, and also push those results into a database, not the ID’s but the actual names please

  20. hello sir ,

    i’m inserting values to database with two dropdowns , got inserted .
    and if i’ve again inserting values to database means the second dropdown must be checked if the same data is present in database dependent to first dropdown . how to do it in php mysqli using jquery???

    plz help me sir

Leave A Reply

CommentLuv badge

Get more stuff like this
in your inbox

Subscribe to our mailing list and get interesting stuff and updates to your email inbox.