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.
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.
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;
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.
JQuery Video Tutorials