Dynamic Drop Down lists using ajax sql and php

Question

I have a code that is effective. The dropdowns are filled based on the previous list, but there is an issue. The option value =""> field in the HTML form displays "id," a number value rather than "name." Please explain to me how "name" can be displayed instead of the value. The true issue is that when data is saved in a SQL database, the matching "id" of the country, state, or city is stored instead of its "name."

This is the code that I am using. I have tried changing the last line in ajax.php echo "<option value='$entity_id'>$enity_name</option>"; to echo "<option value='$entity_name'>$enity_name</option>"; but then the dynamic dropdowns do not work as they are dependent upon "id". Many thanks for your help.

ajax.php

<?php
    /* File : ajax.php
     * Author : Manish Kumar Jangir
    */
    class AJAX {

        private $database = NULL;
        private $_query = NULL;
        private $_fields = array();
        public  $_index = NULL;
        const DB_HOST = "localhost";
        const DB_USER = "admin";
        const DB_PASSWORD = "admin";
        const DB_NAME = "disciples";


        public function __construct(){
            $this->db_connect();                    // Initiate Database connection
            $this->process_data();
        }

        /*
         *  Connect to database
        */
        private function db_connect(){
            $this->database = mysql_connect(self::DB_HOST,self::DB_USER,self::DB_PASSWORD);
            if($this->database){
                $db =  mysql_select_db(self::DB_NAME,$this->database);
            } else {
                echo mysql_error();die;
            }
        }

        private function process_data(){
            $this->_index = ($_REQUEST['index'])?$_REQUEST['index']:NULL;
            $id = ($_REQUEST['id'])?$_REQUEST['id']:NULL;
            switch($this->_index){
                case 'country':
                    $this->_query = "SELECT * FROM countries";
                    $this->_fields = array('id','country_name');
                    break;
                case 'state':
                    $this->_query = "SELECT * FROM states WHERE country_id=$id";
                    $this->_fields = array('id','state_name');
                    break;
                case 'city':
                    $this->_query = "SELECT * FROM cities WHERE state_id=$id";
                    $this->_fields = array('id','city_name');
                    break;
                default:
                    break;
            }
            $this->show_result();
        }

        public function show_result(){
            echo '<option value="">Select '.$this->_index.'</option>';
            $query = mysql_query($this->_query);
            while($result = mysql_fetch_array($query)){
                $entity_id = $result[$this->_fields[0]];
                $enity_name = $result[$this->_fields[1]];
                echo "<option value='$entity_id'>$enity_name</option>";
            }
        }
    }

    $obj = new AJAX;

?>

index.html

<html xmlns="http://www.w3.org/1999/xhtml"><head profile="http://gmpg.org/xfn/11">
<head>
<title>Country State City Dependent Dropdown using Ajax</title>
<script type="text/javascript" src="jquery-1.5.2.min.js"></script>

<script type="text/javascript">
$(document).ready(function(){
    load_options('','country');
});

function load_options(id,index){
    $("#loading").show();
    if(index=="state"){
        $("#city").html('<option value="">Select city</option>');
    }
    $.ajax({
        url: "ajax.php?index="+index+"&id="+id,
        complete: function(){$("#loading").hide();},
        success: function(data) {
            $("#"+index).html(data);
        }
    })
}
</script>
</head>
<body>
<div style="width:800px; margin:auto;padding-top:100px;">
<h1>Country,State,City dynamic dependent dropdown using Ajax and Jquery</h1>
<form>
        <label>Select Country</label>
        <select id="country" name="country" onchange="load_options(this.value,'state');">
            <option value="">Select country</option>
        </select>
        &nbsp;&nbsp;&nbsp;
        <label>Select State</label>
        <select id="state" name="state" onchange="load_options(this.value,'city');">
            <option value="">Select state</option>
        </select>
        &nbsp;&nbsp;&nbsp;
        <label>Select city</label>
        <select id="city" name="city">
            <option value="">Select City</option>
        </select>
        <img src="loader.gif" id="loading" align="absmiddle" style="display:none;"/>
</form>
</div>
</body>
</html>

Can someone please help me with this?

narikkadan · Answer 1 · Sep 4, 2022

The script might be changed to do whatever you want by:

displaying a data attribute for the id. Show result in Ajax ().
using that attribute as the ajax call's id parameter. loading options ().
pass load options the entire "option" element ().

The ajax query with the cahages blow's this.value is incorrect because it is a name, but this still contains the id as a data attribute.

<select id="country" name="country" 
        onchange="load_options(this, 'state');">
    <option value="">Select country</option>
</select>

We must make adjustments to get the id correct because the id argument is the option element from the form.

function load_options(id,index){
...
that = $(id).find(":selected");
id = that.data('realid');

$.ajax({
    url: "ajax.php?index="+index+"&id="+id,

let the value attribute hold name, but keep the id.

public function show_result(){
...
    while ($result = mysql_fetch_array($query)){
        ...
        printf( '<option data-realid="%s" value="%s">%s</option>',
               $entity_id,
               $enity_name,
               $enity_name );

Your query should be answered by this, and depending on how the form is submitted and saved, your issue might also be resolved. However, I doubt it because the section you omitted to show us is probably expecting an ID for the state and city rather than their names.