0

Hi friends,

I have a drop down list which contains Country that i get values of country through store procedure
Now i Have another combobox that have operators that also i have to get through store procedurebut one condition is that when i select country i get only operators of that country not all operators
Please help me out; i am new to JS and php. Please help me in learning..Thanks in advance.

3
Contributors
13
Replies
64
Views
4 Years
Discussion Span
Last Post by diafol
0

It would help if you posted the code you already have and table structures so we can build from there.

Basicaly what you do is:

  1. read the database to get the list of countries
  2. build the first drop down (html select element ) with options containing countries
  3. the drop down (select element) should have onchange event that calls a javascript (jquery) function that will use AJAX to fetch a list of operators for a chosen country
  4. a php script that will read the list of operators for a selected country form the database, process it and return it as html code for the second drop down list (select element)
  5. an empty div and an initial second drop down that will be replaced with the code returned from the above mentioned php script

This way a user will select a country and the second drop down will automaticaly get populated with a list of operators.

If you search DW for two dropdowns or two selects using ajax you might already find something useful since this topic is quite common here. And as said if you post the code you have so far and the table structure it will be easier to help.

0
<?php

    //Gets client content presentation configurations.
    include_once ("includes/checkSession.php");

    //Gets client content presentation configurations.
    include_once ("includes/contentConfig.php");

?>
<html>


        <title><?php echo "$title"; ?> :: Admin Login</title>
        <link rel="shortcut icon" href="images/w_icon.gif">
        <link rel="stylesheet" type="text/css"  href="stylesheets/shreeweb.css" >
        <script  type="text/javascript" src="scripts/shreeweb.js">

        </script>
        <script  type="text/javascript" src="scripts/sorttable.js">
        </script>

    </head>  
        <body>
        <div class="main" align="center">
            <?php
                //Checks whether javascript is enabled or not.
                include_once ("../w2bframework/checkJavascript.php");

                include_once("includes/manageLog.php");


            ?>
            <form name="frmPrice" method="post" id="form1a">
            <table width="40%">


                         <tr>
                         <td></td><td><b>Country:</b></td>
                        <td>
                            <select size="1" name="cmbCountry" id="cmbCountry" class="textbox"  onchange="cmbCountry_selection_changed(this);">
                            <option value="0">Select Country</option>
                            <?php showAllCountry($Country);?>

                            </select>
                        </td>
                    </tr>

                    <tr>
                         <td></td><td><b>Operator:</b></td>
                        <td>
                            <select size="1" name="cmbOperator" id="cmbOperator" class="textbox">
                            <option value="0">Select Operator</option>
                            <?php showAllOperator($Country);?>

                            </select>
                        </td>
                    </tr>

                    <tr>
                         <td></td><td><b>Gateway:</b></td>
                        <td>
                            <select size="1"name="cmbGateway" class="textbox">
                            <option value="0">Select Gateway</option>
                            <?php showAllGateway(); ?></select>
                        </td>
                    </tr>
                    <tr><td></td>
                    <td></td>
                    <td>Price:<input type="text" name="txtPrice"></td> 

                    </tr>

                     <tr>
                        <td></td>
                        <td></td>
                        <td>
                            <input type="submit" value="Update" name="submit">
                        </td>
                    </tr>
                </table>

                </form>
                </div>
                <div class="main" align="center">
            <?php 
                if (isset($_POST['submit'])){

                ?>
                <table class="sortable" cellspacing="0;" width="100%">
                <?php
                    addPrice($_POST['cmbCountry'],$_POST['cmbOperator'],$_POST['cmbGateway'],$_POST['txtPrice']);
                ?>
                </table>
                <?php
                }
            ?>
        </div>

    </body>
</html>



Function showAllOperator:
function showAllOperator($Country) {
        $customer = new Customers();

        $customers = $customer->getOperator($Country);
        if ($customers) {
            foreach($customers as $customer) {
                $Operator= $customer['Operator'];

                echo "<option value=$Operator>$Operator</option>";
            }
        }
        unset($Operator);
        unset($customers);
        unset($customer);

    }

    Function getOperatr:
    function getOperator($Country) {
            $this->Connection();
            $this->openConnection();
            $customers= $this->fetchRows("CALL spGetOperator1('$this->Country')");
            if (count($customers)>0) {
                $this->closeConnection();
                unset($this->Connection);
                return $customers;
            } else {
                $this->closeConnection();
                unset($this->Connection);
                return false;
            }
        }

In store procedure im taking operators like this
CREATE DEFINER=root@localhost PROCEDURE spGetOperator1(IN cmbCountry varchar(100))
BEGIN

<?php

    //Gets client content presentation configurations.
    include_once ("includes/checkSession.php");

    //Gets client content presentation configurations.
    include_once ("includes/contentConfig.php");

?>
<html>


        <title><?php echo "$title"; ?> :: Admin Login</title>
        <link rel="shortcut icon" href="images/w_icon.gif">
        <link rel="stylesheet" type="text/css"  href="stylesheets/shreeweb.css" >
        <script  type="text/javascript" src="scripts/shreeweb.js">

        </script>
        <script  type="text/javascript" src="scripts/sorttable.js">
        </script>

    </head>  
        <body>
        <div class="main" align="center">
            <?php
                //Checks whether javascript is enabled or not.
                include_once ("../w2bframework/checkJavascript.php");

                include_once("includes/manageLog.php");


            ?>
            <form name="frmPrice" method="post" id="form1a">
            <table width="40%">


                         <tr>
                         <td></td><td><b>Country:</b></td>
                        <td>
                            <select size="1" name="cmbCountry" id="cmbCountry" class="textbox"  onchange="cmbCountry_selection_changed(this);">
                            <option value="0">Select Country</option>
                            <?php showAllCountry($Country);?>

                            </select>
                        </td>
                    </tr>

                    <tr>
                         <td></td><td><b>Operator:</b></td>
                        <td>
                            <select size="1" name="cmbOperator" id="cmbOperator" class="textbox">
                            <option value="0">Select Operator</option>
                            <?php showAllOperator($Country);?>

                            </select>
                        </td>
                    </tr>

                    <tr>
                         <td></td><td><b>Gateway:</b></td>
                        <td>
                            <select size="1"name="cmbGateway" class="textbox">
                            <option value="0">Select Gateway</option>
                            <?php showAllGateway(); ?></select>
                        </td>
                    </tr>
                    <tr><td></td>
                    <td></td>
                    <td>Price:<input type="text" name="txtPrice"></td> 

                    </tr>

                     <tr>
                        <td></td>
                        <td></td>
                        <td>
                            <input type="submit" value="Update" name="submit">
                        </td>
                    </tr>
                </table>

                </form>
                </div>
                <div class="main" align="center">
            <?php 
                if (isset($_POST['submit'])){

                ?>
                <table class="sortable" cellspacing="0;" width="100%">
                <?php
                    addPrice($_POST['cmbCountry'],$_POST['cmbOperator'],$_POST['cmbGateway'],$_POST['txtPrice']);
                ?>
                </table>
                <?php
                }
            ?>
        </div>

    </body>
</html>



Function showAllOperator:
function showAllOperator($Country) {
        $customer = new Customers();

        $customers = $customer->getOperator($Country);
        if ($customers) {
            foreach($customers as $customer) {
                $Operator= $customer['Operator'];

                echo "<option value=$Operator>$Operator</option>";
            }
        }
        unset($Operator);
        unset($customers);
        unset($customer);

    }

    Function getOperatr:
    function getOperator($Country) {
            $this->Connection();
            $this->openConnection();
            $customers= $this->fetchRows("CALL spGetOperator1('$this->Country')");
            if (count($customers)>0) {
                $this->closeConnection();
                unset($this->Connection);
                return $customers;
            } else {
                $this->closeConnection();
                unset($this->Connection);
                return false;
            }
        }

In store procedure im taking operators like this
CREATE DEFINER=root@localhost PROCEDURE spGetOperator1(IN cmbCountry varchar(100))
BEGIN
SELECT Operator FROM NNCData
WHERE Country=cmbCountry;
but im not getting values of operators
i Have table called NNCData where country and operators are belong

Edited by ankitamalu

0

Tnanx for posting the code. It seems that all the functionality is already there. The showAllCountry($Country) function obviously generates options for countries (even though I do not know why the argument $Country is needed) and on change the cmbCountry_selection_changed(this) javascript function is called that obviously returns the operators options.

The reason for code not working is hard to figure out from this. Have you copied the code from somewhere? The code includes the Customers class which you have not provided the code for. Also the code for the showAllCountry function has not been provided. It would be easier to help if you decided that you do this from scratch and not using some existing code without understanding it.

0

I am not copied codefrom somewhere. I am doing from scratch only customers class because in customers.php page function getOperator() belongs the code for showAllCountry is

function showAllCountry() {
        $customer = new Customers();
        $customers = $customer->getCountryOperator();
        if ($customers) {
            foreach($customers as $customer) {
                $Country= $customer['Country'];

                echo "<option value=$Country>$Country</option>";
            }
        }
        unset($Country);
        unset($customers);
        unset($customer);
    }



        function getCountryOperator() {
            $this->Connection();
            $this->openConnection();
            $customers= $this->fetchRows("CALL spGetCountryOperator()");
            if (count($customers)>0) {
                $this->closeConnection();
                unset($this->Connection);
                return $customers;
            } else {
                $this->closeConnection();
                unset($this->Connection);
                return false;
            }
        }


        and storeprocedure islike this:
        CREATE DEFINER=`root`@`localhost` PROCEDURE `spGetCountryOperator`()
BEGIN
    SELECT DISTINCT Country FROM NNCData;
END

that selectsallcountries from table NNCData and i want when dropdown select any country then the operator of that country will be displayed in Operartor's dropdown

lyk this

0

and i want to ask that how to define that onchange function cmbCountry_selection_changed(this).
Thank you

0

cmbCountry_selection_changed(this) is a javascript function that uses AJAX to:

  1. call the php script that will read the operators form the database (or with stored procedure); this function passes the chosen country usualy using POST HTTP method; the chosen county is read from DOM
  2. waits for the response form the php script (a html code for options) and injects the response into the second select element

The easiest way to do this is using jquery ajax functionality.

0

i understood that but how do i define to fetch the values of operators i am not getting that.

0

ok. fine. thank you so much. actually i am totaly new to this coding thats why.. and if u want to change that onchange method you can change.

Edited by ankitamalu

0

No problem. I just started coding but will need structures of the Countries and Operators tables and some data for testing. You can get structure and data in phpmyadmin by executing the following query:

SHOW CREATE TABLE <table name here>

Please post the structure here. And tell me which extension do you use to connect to the database (mysql, mysqli, pdo...).

Edited by broj1

0

I have prepared an example using your code but stripping it down a bit to make it more understandable. I made up two test tables: countries (country_id, country_name) and operators (operator_id, fk_country_id, operator_name) so I could test the code. Replace the table and field names with your table definition. I am also using a mysqli for connection to the DB. Change connection and querying code to suit your needs. For more info see the comments in the code.

This is the HTML page with the dropdowns:

<?php 

// connect to the database
// replace this code with your code for DB connection
require('../common/dbConnMysqli.php');
$dbobj = new dbConnMySqli;
$db = $dbobj->getmysqliObject();

?>
<html>
<head>
<title><?php echo "DOUBLE SELECT TEST"; ?> :: Admin Login</title>
<link rel="shortcut icon" href="images/w_icon.gif">
<link rel="stylesheet" type="text/css"  href="stylesheets/shreeweb.css" >
<script  type="text/javascript" src="scripts/shreeweb.js"></script>
<script  type="text/javascript" src="scripts/sorttable.js"></script>


<!-- Include this link to be able to use jQuery -->
<script src="//ajax.googleapis.com/ajax/libs/jquery/2.0.0/jquery.min.js"></script>


</head>  
<body>
<div class="main" align="center">

<form name="frmPrice" method="post" id="form1a">

<label for="cmbCountry">Country</label>
<select size="1" name="cmbCountry" id="cmbCountry" class="textbox"  
onchange="cmbCountry_selection_changed(this);">
<option value="0" selected="selected" disabled="disabled">-- Select Country --</option>

<?php 
// read countries from the DB for the first dropdown
$query = 'SELECT * FROM countries';
$res = $db->query($query);

// add options (countries) to the first dropdown
while ($row = $res->fetch_assoc()) {
    echo '<option value="' . $row['country_id'] . '">';
    echo $row['country_name'] . '</option>';
}
?>

</select>
<div id="operatorsDiv" style="display:inline-block;">
<label for="cmbOperator">Operator</label>
<select size="1" name="cmbOperator" id="cmbOperator" class="textbox">
<option value="0" disabled="disabled">-- Select country first --</option>
</select>
</div>

<label for="cmbGateway">Gateway</label>
<select size="1"name="cmbGateway" class="textbox">
<option value="0">Select Gateway</option>
</select>

<label for="txtPrice">Price</label>
<input type="text" name="txtPrice">

<input type="submit" value="Update" name="submit">

</form>

</div>

<div class="main" align="center">
<?php 
if (isset($_POST['submit'])){

    echo '<table class="sortable" cellspacing="0;" width="100%">';
    addPrice($_POST['cmbCountry'],$_POST['cmbOperator'],$_POST['cmbGateway'],$_POST['txtPrice']);
    echo '</table>';
}
?>
</div>

<script type="text/javascript">
// function to retrieve the operators for selected country
// it is executed each time the Countries change
// it uses jQuery
function cmbCountry_selection_changed(selectElement) {

    // read the selected option (the ID of the country from the countries table)
    var selectedOption = $(selectElement).val();

    // URL of the PHP script that will return the options for operators dropdown
    // the country ID will be passed in a querystring
    var operatorsScript = 'get_operators.php?countryId=' + selectedOption;

    // execute ajax call using jquery post
    $.post(
        operatorsScript, 
        function(data) { $('#operatorsDiv').html(data); }
    );
}
</script>

</body>
</html>

And this is the PHP script that is called by AJAX call, reads the operators from the DB, creates a HTML code for the second dropdown and return this code to the AJAX callback function

<?php
// first check if the country ID is existing in query string ($_GET array)
// if existing, read the operators from database for this country
// and create a html code for the second select element (dropdown)
if(isset($_GET['countryId'])) {

    // connect to the database
    // replace this code with your code for DB connection
    require('../common/dbConnMysqli.php');
    $dbobj = new dbConnMySqli;
    $db = $dbobj->getmysqliObject();

    // assign country ID to a variable, casting it to integer for security reasons
    $countryId = (int) $_GET['countryId'];

    // prepare the query for reading operators from the database
    $query = "SELECT * FROM operators WHERE fk_country_id=$countryId";

    // execute the query
    $res = $db->query($query);

    // start the HTML code for the second select element
    $sel  = '<label for="cmbOperator">Operator</label>';
    $sel .= '<select size="1" name="cmbOperator" id="cmbOperator" class="textbox">';
    $sel .= '<option value="0" disabled="disabled" selected="selected">';
    $sel .= '-- Select operator --</option>';

    // add other options (operators) for the second dropdown
    while ($row = $res->fetch_assoc()) {
        $sel .= '<option value="' . $row['operator_id'] . '">';
        $sel .= $row['operator_name'] . '</option>';
    }

    // end the select element
    $sel .= '</select>';

    // echo the HTML code for the second select element (operators)
    // this echoed code will get back to the cmbCountry_selection_changed function
    // as data variable, which will be inserted in div with id=operatorsDiv
    echo $sel;

} else {

    // if the country ID is not existing something went wrong
    // echo some error message
    echo 'Sometnihg went wrong, sory.';
}
?>

This approach looks complex but if you take time and study it you might use it often and it pays to be familiar with it. Diafols example from above post works in similar fashion with differencies in some details. Take a look at it, too, it will help you to clarify things.

Edited by broj1

0

Just to clarify, broj1's solution uses Ajax, my example just uses JS after page load. Both should work, but my example depends upon reasonably static data to be truly useful. Ajax will retrieve up to the second data from the DB. So your decision may revolve around the nature of your data.

Try both and see how they turn out - compare the initial load times, the response times etc. There are a hundred ways to do everything.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.