I have three tables:
- Countries [CountryId, CountryName]
- States [StateId, StateName, CountryId_FK]
- Cities [CityId, CityName, CityInfo, StateId_FK]
On my html form, I am able to list Countries & States in two dependant drop downs using PHP & Ajax. The States drop down is dependant on the country drop down which is working fine.
However, I am stuck trying to have the input text box search for a city based on the selection of the state and if the city exists, it should display the CityName and CityInfo preferably to a table.
Below is the php code where I think I am stuck at.
if (isset($_POST['StateId'])){
$query = "SELECT * FROM tblCities WHERE StateId=".$_POST['StateId'] ;
// GET Search here???
$result = $conn->query($query);
if ($result->num_rows > 0){
//DO SOMETHING HERE
}
NOTE: I can echo results into a table just fine but I want to SEARCH for a city first before displaying the match.
ajaxdata.php
if (isset($_POST['CountryId'])){
$query = "SELECT * FROM tblStates WHERE CountryId=".$_POST['CountryId'];
$result = $conn->query($query);
if ($result->num_rows > 0){
echo '<option selected disabled value="">Select States</option>';
while ($row = $result->fetch_assoc()){
echo '<option value='.$row['StateId'].'>'.$row['StateName'].'</option>';
json_encode($row, $name);
}
}else {
echo '<option>No State Found!</option>';
}
}
functions.js
function FetchCountry(id){
$('#countries').html('');
$('#states').html(''<option selected disabled>Select State</option>');
$.ajax ({
type:'post',
url:'ajaxdata.php',
data : {CountryId : id},
success : function(data,){
$('#states').html(data);
console.log(data); //Log data
}
});
}
function FetchStates(id){
$('#cities').html('');
$.ajax ({
type:'post',
url:'ajaxdata.php',
data : {StateId : id},
success : function(data,){
$('#cities').html(data);
console.log(data);
}
});
}
index.php
<?php
include "database.php";
$query = "SELECT * FROM tblCountries";
?>
<!DOCTYPE html>
//CSS/JS/JQUERY here
<head>
<script src="functions.js">
</head>
<body>
<div class="container">
<form method="POST">
<select id="countries" onchange="FetchCountries(this.value)">
<option selected disabled>Select Country</option>
<php?
if ($result->num_rows > 0){
while ($row = $result->fetch_assoc()){
echo '<option value='.$row['CountryId'].'>'.$row['CountryName'].'</option>';
}
}
?>
<select id="states" onchange="FetchStates(this.value)">
<option selected disabled>Select State</option>
<input type="text" id="search" class="form-control"></input>
<button class="btn-Search" name="submit" type="submit"></button>
</form>
</div>
<--Results Area -->
<div class="container">
<table id="cities"></table>
</div>
</body>
</html>
My Answer:
To implement the search functionality for cities based on the selected state, you can modify your PHP code as follows:
php
if (isset($_POST['StateId'])){
$query = "SELECT * FROM tblCities WHERE StateId=".$_POST['StateId'];
if(isset($_POST['search']) && !empty($_POST['search'])){
$search = $_POST['search'];
$query .= " AND (CityName LIKE '%$search%' OR CityInfo LIKE '%$search%')";
}
$result = $conn->query($query);
if ($result->num_rows > 0){
while ($row = $result->fetch_assoc()){
echo ''.$row['CityName'].' '.$row['CityInfo'].'
}
}else {
echo 'No City Found!
}
}
In your index.php file, you can add the search functionality to the form:
html
And in your functions.js file, you can modify the FetchStates function to include the search functionality:
javascript
function FetchStates(id){
$('#cities').html('');
var search = $('#search').val();
$.ajax ({
type:'post',
url:'ajaxdata.php',
data : {StateId : id, search: search},
success : function(data){
$('#cities').html(data);
console.log(data);
}
});
}
With these changes, when a state is selected and the search button is clicked, the search term will be sent to the PHP script along with the state ID. The PHP script will then search for cities based on the state and the search term, and display the results in a table format on the page.
Rate this post
4 of 5 based on 6747 votesComments