1. SQL Database Creation
You have defined SQL queries to create three tables: country_state_city, data_counrty, and tbl_country, which are used to store country, state, city, and district information.
-- Table country_state_city
CREATE TABLE `country_state_city` (
`id` int(11) NOT NULL,
`country` varchar(250) NOT NULL,
`state` varchar(250) NOT NULL,
`city` varchar(250) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- Insert sample data into country_state_city
INSERT INTO `country_state_city` (`id`, `country`, `state`, `city`) VALUES
(1, 'Cambodia', 'Phnom Penh', 'Teok Tlar'),
(2, 'Cambodia', 'Phnom Penh', 'Teok laork'),
(3, 'Cambodia', 'Battambang', 'Battambang Municipality'),
(4, 'Cambodia', 'Kampong Cham', 'Kampong Cham 1'),
(5, 'Cambodia', 'Koh Kong', 'Koh Kong 1'),
(6, 'Cambodia', 'Koh Kong', 'Koh Kong 2'),
(7, 'Cambodia', 'Takeo', 'Takeo 1'),
(8, 'Cambodia', 'Takeo', 'Takeo 2'),
(9, 'Cambodia', 'Takeo', 'Takeo 3'),
(10, 'Thailand', 'Amnat Charoen', 'Amnat Charoen 1'),
(11, 'Thailand', 'Amnat Charoen', 'Amnat Charoen 2'),
(12, 'Thailand', 'Amnat Charoen', 'Amnat Charoen 3'),
(13, 'Thailand', 'Amnat Charoen', 'Amnat Charoen 4'),
(14, 'Viet Nam', 'Bac Kan', 'Bac Kan 0'),
(15, 'Viet Nam', 'Bac Kan', 'Bac Kan 1'),
(16, 'Viet Nam', 'Bac Kan', 'Bac Kan 2'),
(17, 'Viet Nam', 'Bac Kan', 'Bac Kan 3');
2. countryForm.php File
This file contains the form where users can select a country, city/province, and district. It also handles the display of existing data in a table format.
<?php
// Include connection and insert functions
include 'assets/connection/connection.php';
include 'insert.php';
?>
<!DOCTYPE html>
<html lang="en">
<head>
<title>Country Form</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<!-- Bootstrap and DataTables libraries -->
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.1.3/css/bootstrap.css">
<link rel="stylesheet" href="https://cdn.datatables.net/1.10.20/css/dataTables.bootstrap4.min.css">
<!-- Custom CSS -->
<link rel="stylesheet" href="assets/css/table-user.css" type="text/css">
<link rel="stylesheet" href="assets/css/register-user.css" type="text/css">
<!-- jQuery and DataTables Scripts -->
<script src="https://code.jquery.com/jquery-3.3.1.js"></script>
<script src="https://cdn.datatables.net/1.10.20/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.10.20/js/dataTables.bootstrap4.min.js"></script>
</head>
<body>
<div class="container">
<!-- Country form -->
<form action="" method="POST" enctype="multipart/form-data">
<h3 class="text-center">1. Country Information</h3>
<div class="form-group row">
<label class="col-sm-3 col-form-label">Country <span class="star-red">*</span></label>
<div class="col-sm-9">
<select class="form-control action" name="country" id="country">
<option selected>Please select country</option>
<?php echo $country; ?>
</select>
</div>
</div>
<!-- City/Province selection -->
<div class="form-group row">
<label class="col-sm-3 col-form-label">City/Province <span class="star-red">*</span></label>
<div class="col-sm-9">
<select class="form-control action" name="cityProvince" id="cityProvince">
<option selected>Please select province</option>
</select>
</div>
</div>
<!-- District selection -->
<div class="form-group row">
<label class="col-sm-3 col-form-label">District <span class="star-red">*</span></label>
<div class="col-sm-9">
<select class="form-control" name="district" id="district">
<option selected>Please select district</option>
</select>
</div>
</div>
<!-- Save button -->
<div class="clearfix">
<input type="submit" name="save" class="btn btn-primary btnNext float-left" value="Save">
</div>
</form>
<!-- Data Table -->
<table id="tableHorizontalWrapper" class="table table-striped table-bordered table-sm text-center" cellspacing="0" width="100%">
<thead>
<tr>
<th>ID</th>
<th>Country</th>
<th>City/Province</th>
<th>District</th>
<th>Action</th>
</tr>
</thead>
<tbody>
<?php
$mysqli_query = mysqli_query($databaseServer, "SELECT * FROM `data_counrty`");
while($row = mysqli_fetch_array($mysqli_query)) {
?>
<tr>
<td><?= $row['id'] ?></td>
<td><?= $row['country'] ?></td>
<td><?= $row['cityProvince'] ?></td>
<td><?= $row['district'] ?></td>
<td>
<a href="edit.php?id=<?= $row['id']; ?>">
<button class="btn btn-primary btn-sm">Edit</button>
</a>
<a href="delete.php?id=<?= $row['id']; ?>">
<button onClick="return confirm('Are you sure you want to delete?')" class="btn btn-danger btn-sm">Delete</button>
</a>
</td>
</tr>
<?php } ?>
</tbody>
</table>
</div>
<script src="assets/js/javascript.js" type="text/javascript"></script>
</body>
</html>
3. delete.php File
This file handles the deletion of a record from the data_counrty table.
<?php
// Connection to database
include 'assets/connection/connection.php';
$id = $_REQUEST['id'];
$deleteData = "DELETE FROM `data_counrty` WHERE `data_counrty`.`id` = $id";
if ($databaseServer->query($deleteData) === TRUE) {
$successMessage = '<div class="alert alert-success text-center" role="alert">Record deleted successfully.</div>';
} else {
$errorMessage = '<div class="alert alert-secondary text-center" role="alert">Error deleting record.</div>';
}
header("Location: countryForm.php");
// Close database connection
$databaseServer->close();
?>
4. edit.php File
This file allows the editing of an existing record.
<?php
// Connection to database
include 'assets/connection/connection.php';
$id = $_REQUEST['id'];
$mysqli_query = "SELECT * FROM `data_counrty` WHERE id = '$id'";
$result = mysqli_query($databaseServer, $mysqli_query) or die(mysqli_connect_error());
$row = mysqli_fetch_assoc($result);
if (isset($_POST['new']) && $_POST['new'] == 1) {
// Update record
$country = $_POST['country'];
$cityProvince = $_POST['cityProvince'];
$district = $_POST['district'];
$updateData = "UPDATE `data_counrty` SET country = '$country', cityProvince = '$cityProvince', district = '$district' WHERE `data_counrty`.`id` = '$id'";
if ($databaseServer->query($updateData) === TRUE) {
$successMessage = '<div class="alert alert-success text-center" role="alert">Record updated successfully.</div>';
} else {
$errorMessage = '<div class="alert alert-secondary text-center" role="alert">Error updating record.</div>';
}
header("Location: countryForm.php");
}
$databaseServer->close();
?>
5. getcountry.php File
This file handles the dynamic population of city/province and district dropdowns based on the selected country.
<?php
// Connection to database
include 'assets/connection/connection.php';
if (isset($_POST["action"])) {
$output = '';
if ($_POST["action"] == "country") {
$query = "SELECT state FROM country_state_city WHERE country = '".$_POST["query"]."' GROUP BY state";
$result = mysqli_query($databaseServer, $query);
$output .= '<option value="">Please select Province</option>';
while ($row = mysqli_fetch_array($result)) {
$output .= '<option value="'.$row["state"].'">'.$row["state"].'</option>';
}
}
if ($_POST["action"] == "cityProvince") {
$query = "SELECT city FROM country_state_city WHERE state = '".$_POST["query"]."' GROUP BY city";
$result = mysqli_query($databaseServer, $query);
$output .= '<option value="">Please select district</option>';
while ($row = mysqli_fetch_array($result)) {
$output .= '<option value="'.$row["city"].'">'.$row["city"].'</option>';
}
}
echo $output;
}
?>
6. JavaScript (assets/js/javascript.js)
This script handles the AJAX request for dynamically populating the dropdowns.
$(document).ready(function() {
// Populate cities based on country selection
$('#country').change(function() {
var country = $(this).val();
if (country != '') {
$.ajax({
url: "getcountry.php",
method: "POST",
data: { action: "country", query: country },
success: function(data) {
$('#cityProvince').html(data);
}
});
}
});
// Populate districts based on city/province selection
$('#cityProvince').change(function() {
var cityProvince = $(this).val();
if (cityProvince != '') {
$.ajax({
url: "getcountry.php",
method: "POST",
data: { action: "cityProvince", query: cityProvince },
success: function(data) {
$('#district').html(data);
}
});
}
});
});
Summary:
-
You have successfully set up dynamic forms for country, city, and district selection using PHP and MySQL.
-
JavaScript handles dynamic data fetching from the database using AJAX, while PHP handles data insertion, updating, and deletion.
-
Bootstrap and DataTables enhance the UI and provide interactive table features.
If you need any adjustments or further functionality, feel free to ask!
