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.
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:
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.
<?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
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.
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
and i want to ask that how to define that onchange function cmbCountry_selection_changed(this).
Thank you
cmbCountry_selection_changed(this) is a javascript function that uses AJAX to:
The easiest way to do this is using jquery ajax functionality.
i understood that but how do i define to fetch the values of operators i am not getting that.
I have to go right now but will provide some code later tonight.
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.
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...).
I made a blog post about a similar use some time ago here:
http://diafol.blogspot.co.uk/2013/02/creating-linked-select-dropdowns-from.html
You may find it useful.
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.
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.
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.