Adding/ Deleting Country, State and City data for list box

We have seen how to display three linked dropdown list box. Here we will learn how to add records to the table with links to other tables.

Adding a Country

We can easily add country name and country code to our plus2_country table. There are two input text boxes, one will take country name and other is for country code. These two data will be posted by POST method to dropdown3-admin-add-countryck.php. Inside this PHP file we will validate the data first and then insert into plus2_country table.
<?Php
require "config.php";// connection details
error_reporting(0);//no error reporting 

///////////////

$country_code=$_POST['country_code'];// Country code
$country=$_POST['country'];

///////////// Validate the inputs ////////////

if(strlen($country_code) > 0 and strlen($country)>0){

$sql=$dbo->prepare("insert into plus2_country (country,country_code) values(:country,:country_code)");
$sql->bindParam(':country',$country,PDO::PARAM_STR,25);
$sql->bindParam(':country_code',$country_code,PDO::PARAM_STR,4);
if($sql->execute()){
$state_id=$dbo->lastInsertId(); 
$msg= " Country  Added : $country : code = $country_code ";
}
else{
$msg = " Database Problem  Not able to add data please contact Admin ";
}
$("#f1")[0].reset();
}else{
$msg=" Enter  Country and Country Code";
}

echo $msg;
?>

JQuery

We will be posting country code and country name as entered by user to backend script. If we get a confirmation that data is added then we will refresh the page to reflect the new country.
<script>
$(document).ready(function() {
///////////////////////////
$("#b1").click(function(){
$("#msg").html('Just Wait ...'); 
$("#msg").show();

$.post( "dropdown3-admin-add-countryck.php", {"country_code":$('#country_code').val(),"country":$('#country').val()},function(return_data,status){
$("#msg").html(return_data.msg); 
setTimeout(function() { $("#msg").hide(); }, 2000);
if(return_data.db_status=='OK'){
 window.location = "dropdown3-admin-add-country.php";
}
},'json');
});
////////////////////////////
})
</script>

Adding State

While adding state we will first select Country from the drop down list and we can enter State name. The text box to enter state name will be enabled only when country is selected. On Submit the state name along with country code ( from country dropdown list ) is passed to backend script to insert to plus2_state table. After adding the state name the unique id of state is displayed. The PHP backend script receives country code and state name as entered by user. Before storing them in table the data is checked for any blank entry. Once it is cleared then data is stored in plus2_state table and value of state_id along with state name is returned to main script for confirmation.
if(strlen($country_code) > 0 and strlen($state)>0){

$sql=$dbo->prepare("insert into plus2_state (state,country_code) values(:state,:country_code)");
$sql->bindParam(':state',$state,PDO::PARAM_STR,25);
$sql->bindParam(':country_code',$country_code,PDO::PARAM_STR,4);
if($sql->execute()){
$state_id=$dbo->lastInsertId(); 
$msg= " State   Added : $state : State_id = $state_id";
}
else{
$msg = " Database Problem  Not able to add data please contact Admin ";
}
}else{
$msg=" Select   Country and enter state name";
}

echo $msg;

JQuery

Inside our JQuery code initially we keep the text input box ( for state name ) and the submit button as disabled. We will allow to enter text and submit the data only when country is selected.
<script>
$(document).ready(function() {
$("#b1").prop('disabled',true);
$("#t1").prop("disabled", true);
////////////////////////////////////
$("#country_code").change(function(){ // change function of listbox
$("#b1").prop('disabled',false);
$("#t1").prop("disabled",false);

});
///////////////////////////
////////////////////////////////////
///////////////////////////
$("#b1").click(function(){
$("#msg").html('Just Wait ...'); 
$("#msg").show();

$.post( "dropdown3-admin-add-stateck.php", {"country_code":$('#country_code').val(),"state":$('#t1').val()},function(return_data,status){
$("#f1")[0].reset();

$("#msg").html(return_data); 
setTimeout(function() { $("#msg").hide(); }, 2000);

});
});
////////////////////////////
})
</script>

Adding City

To add city to database we must select Country first and then matching State of the country and then add city name to table. The backend script is here.
if(strlen($state_id) > 0 and strlen($city) > 1){

$sql=$dbo->prepare("insert into plus2_city (city,state_id) values(:city,:state_id)");
$sql->bindParam(':city',$city,PDO::PARAM_STR,25);
$sql->bindParam(':state_id',$state_id,PDO::PARAM_INT,4);
if($sql->execute()){
$city_id=$dbo->lastInsertId(); 
$msg= " City Added : $city : id = $city_id ";
}
else{
$msg = " Database Problem  Not able to add data please contact Admin ";
}
}else{
$msg=" Enter all details ";
}
echo $msg;

JQuery

<script>
$(document).ready(function() {
$("#b1").prop('disabled',true);
$("#t1").prop("disabled", true);
////////////////////////////////////
$("#country_code").change(function(){//change function of listbox
$("#state_id,#city_id").empty();
$("#b1").prop('disabled',true);
$.post( "dropdown3ck.php", {"country_code":
$('#country_code').val()},function(return_data,status){
$("#state_id").append("<option value=''>Select One</option>");

$.each(return_data.state, function(key,value){
$("#state_id").append("<option value=" + value.state_id +">"
+value.state_id + ':' + value.state+"</option>");
});

},"json");

});
///////////////////////////
////////////////////////////////////
$("#state_id").change(function(){//change function of listbox
if ($( "#country_code" ).val().length >0 && 
$( "#state_id" ).val().length >0 ){
$("#b1").prop('disabled',false);
$("#t1").prop("disabled", false);
}
});
///////////////////////////
$("#b1").click(function(){
$("#msg").html('Just Wait ...'); 
$("#msg").show();

$.post( "dropdown3-admin-add-cityck.php", 
{"state_id":$('#state_id').val(),"city":$('#t1').val()},
function(return_data,status){
$("#msg").html(return_data); 
setTimeout(function() { $("#msg").hide(); }, 2000);
$("#f1")[0].reset();
$("#t1").attr('value','');
});
});
////////////////////////////
})
</script>

Deleting records

We can delete any city without affecting state or country table but when we delete a state then we must delete all the matching city of the state. Similarly while deleting country we must delete linked state and then linked city from the state and city table respectively. Read more on how to delete records in multiple table here

Deleting City

The query comes with simple Delete command
DELETE FROM plus2_city  WHERE city_id=:city_id

Deleting State

To delete a state record we have to delete in two tables, one is the plus2_state and plus2_city ( having same state_id ). We will join these two tables by using INNER JOIN. Here is the Query used.
DELETE FROM plus2_state,plus2_city using plus2_state 
INNER JOIN plus2_city 
WHERE plus2_state.state_id=plus2_city.state_id 
AND plus2_state.state_id=:state_id

Deleting Country

While deleting country from plus2_country we must delete matching state from plus2_state and matching city from plus2_city table. Here is the query by joining three tables.
DELETE  FROM plus2_country,plus2_state,plus2_city USING plus2_state
 INNER JOIN plus2_city 
 INNER JOIN plus2_country 
 WHERE plus2_country.country_code=plus2_state.country_code 
 AND plus2_state.state_id=plus2_city.state_id 
 AND  plus2_country.country_code=:country_code

Deleting records

In listing page there are records shown in a row. Any data can be deleted here. In demo script tables can't be updated but you can download and run the script to test different features.

Script Installation

Download the zip file and place in your server path.
Open config.php file and enter database login details.
Use dropdown3.sql file to create tables inside your PHPMyAdmin
Open dorpdown3.php file to see the three dropdowns.



Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com



    Post your comments , suggestion , error , requirements etc here .







    Most Popular JQuery Scripts

    1

    Two dependant list boxes

    2

    Calendar with Date Selection

    3

    Data change by Slider

    4

    Show & Hide element


    JQuery Video Tutorials




    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer