How to save Country, State, City names into database?

I am having a problem with my PHP script. I have been working and researching this problem for days without any resolution. I have a dependent drop-down. When a user selects Country, State, and City from select/option dependent drop-downs my script will only save the ID numbers associated with the Country, State, and City. The script sets the IDs to values in order to access each other. However, I would like the database to ultimately save the actual names of the Countries, States, and Cities and not the ID numbers.

I am also using localstorage to save these names in case of a page refresh. In localstorage the State and City work correctly by saving the actual names. However, once again, the Country saves only an ID here as well.
I am learning PHP as a beginner programmer, so I am not well versed as of yet. If anybody can help me by pointing out my errors and how to fix them I would be very grateful. I will paste my code here.

Thank you all in advance.

<li>
<fieldset>
<div>
<select id="country" class="country" name="country" title="select country">
<?php include("../php_includes/countryAjaxData.php"); ?>
</select>
</div>
<div>
<select id="state" class="state" name="state" title="select your state">
<option value="" selected="selected">Select State</option>
</select>
</div>
<div>
<select id="city” class="city>
<option value="" selected="selected">Select City</option>
</select>
</div>
</fieldset>
</li>
<script>
//Load Country Data into dropdown menus
window.load=$(document).ready(function() {
var countryName = $(this).val();
$.ajax ({
type: "POST",
url: "../php_includes/countryAjaxData.php",
data: 'country='+countryName,
//dataType: 'json', // Required to read response as JSON
success: function(html) {
$('.country').html(html);
$('.country').each(function(i) {
var thisSelection = $(this);
var thisId = thisSelection.attr('id');
var storageId = 'country-' + thisId;
var storedInfo = localStorage.getItem(storageId);
if( storedInfo ) {
var rememberedOptions = storedInfo.split(',');
thisSelection.val( rememberedOptions );
}
thisSelection.on('change', function(e) {
var selectedOptions = [];
thisSelection.find('option:selected').each(function(i) {
var thisOption = $(this);
selectedOptions.push(thisOption.val());
});
localStorage.setItem(storageId, selectedOptions.join(','));
});
});
}
});
});
</script>
<script>
//On country dropdown change trigger this
$('.country').on('change', function () {
var countryId = $(this).val();
var state_select = $(this).closest('fieldset').find('.state');
var city_select = $(this).closest('fieldset').find('.city');
if (countryId) {
$.ajax( {
type: 'POST',
dataType: 'JSON', // Required to read response as JSON
url: '../php_includes/ajaxData.php',
data: {
country_id: countryId
},
success: function (r) {
//console.log('States', r);
if (r.status) {
r.data.map(function (state) {
$(state_select).append(`<option value="${state.id}" name="${state.name}">${state.name}</option>`);
$('.state').each(function(r) {
var stateList = $(state);
var thisSelection = $(this);
var thisId = thisSelection.attr('id');
var storageId = 'state-' + thisId;
var storedInfo = JSON.parse(localStorage.getItem(storageId));
thisSelection.change(function(i) {
var selectedOptions = [];
thisSelection.find('option:selected').map(function(i) {
var thisOption = $(this);
selectedOptions.push(thisOption.val());
localStorage.setItem(storageId, JSON.stringify(thisOption.text()));
})
})
})
})
} else {
//$('.error').html('No States);
$(state_select).html('<option class="nostate" value="no_states " name="state_unavail" selected="selected">No States</option>')
var select_state = $('#countryfieldset').find('[name=state_unavail]');
$(city_select).html('<option class="nocity" value="no_city" name="city_unavail" selected="selected">No Cities</option>')
var select_city = $('#countryfieldset').find('[name=city_unavail]');
}
}
})
};
});
// On state dropdown change trigger this
$('.state').on('change', function () {
var stateId = $(this).val();
var city_select = $(this).closest('fieldset').find('.city');
if (stateId) {
$.ajax( {
type: 'POST',
dataType: 'json', // Required to read response as JSON
url: '../php_includes/ajaxData.php',
data: {
state_id: stateId
},
success: function (r) {
if (r.status) {
r.data.map(function (city) {
$(city_select).append(`<option value="${city.id}">${city.name}</option>`);
$('.city').each(function(r) {
var cityList = $(city);
var thisSelection = $(this);
var thisId = thisSelection.attr('id');
var storageId = 'city-' + thisId;
var storedInfo = JSON.parse(localStorage.getItem(storageId));
thisSelection.change(function(i) {
var selectedOptions = [];
thisSelection.find('option:selected').map(function(i) {
var thisOption = $(this);
selectedOptions.push(thisOption.val());
localStorage.setItem(storageId, JSON.stringify(thisOption.text()));
})
})
})
})
} else {
//$('.error').html('No Cities Available.');
$(city_select).html('<option class="nocity" value="no_city_available" name="city_unavail" selected="selected">No Cities</option>');
var select_city = $('#countryfieldset').find('[name=city_unavail]');
}
}
})
};
});
});
</script>
<script>
$sql = 'SELECT `id`, `name` FROM `countries` ORDER BY `name` ASC' ;
$query = $database_connection_here ->query($sql);
//if there is at least one country display message
if($query->rowCount() > 0) {
echo '<option value="">Select Country</option>';
while($country = $query->fetch(PDO::FETCH_ASSOC)) {
echo ('<option value="' . $country['id'] . '"name="' . $country['name'] . '" >'.$country['name'] . '</option>'); //select country id and show it's name from country table
}
} else {
echo '<option value="">Data Not Available</option>';
}
</script>
<script>
$resp = array(
'status' => false,
'data' => []
);
/*-----------------------------------
| Grab States based on Country
-----------------------------------*/
if (isset($_POST["country_id"]) && !empty($_POST["country_id"])) {
$sql = "SELECT `id`, `name` FROM `states` WHERE `country_id` = " . $_POST['country_id'] . " ORDER BY `name` ASC";

$query = $database_connection_here ->query($sql);

if ($query->rowCount() > 0) {
$resp['status'] = true;
while ($state = $query->fetch(PDO::FETCH_ASSOC)) {
$resp['data'][] = $state;
}
}
echo json_encode($resp);
}

/*-----------------------------------
| Grab Cities based on State
-----------------------------------*/
if (isset($_POST["state_id"]) && !empty($_POST["state_id"])) {
$sql = "SELECT `id`, `name` FROM `cities` WHERE `state_id` = " . $_POST['state_id'] . " ORDER BY `name` ASC";

$query = $database_connection_here ->query($sql);

if ($query->rowCount() > 0) {
$resp['status'] = true;
while ($city = $query->fetch(PDO::FETCH_ASSOC)) {
$resp['data'][] = $city;
}
}
echo json_encode($resp);
}
</script>

That’s not really the normal way of doing things, if you are referring to storing the country, state and city in other tables than their definition tables, for example as part of a customer record. You have a table containing the various countries, states and cities names alongside their unique IDs, and you should not duplicate those names in other tables without a very good reason.

One of the reasons is to save storage space (which most people don’t care about nowadays) but also think about what happens if one of your cities changes name or spelling - right now all you have to do is change the city table to have the new spelling, and anything that references that city will automatically retrieve the new spelling. Your way would mean finding and changing every single reference in every different table.

Have a read up on “database normalisation”.

1 Like

Hello droopsnoot, thanks for the information. When I view the users now, I am looking at IDs. Given my code, how would I view the users country, state, and city without having to manually look up the IDs through thousands of IDs present? Thanks

You would use a JOIN in your query to get names of states, etc.

1 Like

Hello SamA74, thanks for the response. I am unfamiliar with the ‘join’ query. I will have to research it.

The jquery.country plugin may helps, is a plugin for country/state/city dropdown without any use of database.

Hello ross24256, thanks for the info. However, I already have a full database of countries, states and cities. I am just trying to work with it.

  Thanks

Hello SamA74, I have located information on joining tables. However, my question is now almost the opposite. How do I get the state and city to just show IDs now? The results are mixed, the country always show IDs and the state and city is a bit strange. It bounces back and forth. I can not predict it. If I use join then I should have all IDs in the table fields. Can anyone help with my code given?
Thanks

Show us the code and someone will help.

1 Like

Hello droopsnoot,
What more of the code do you need? I am pretty sure it is all there.

Thanks

You mentioned you had read up on JOINs and were now having trouble with it showing names instead of IDs, and I couldn’t see any JOINs in that code.

I must admit I was really only looking at the PHP sections of the code as this is the PHP section, I imagine your problems are more in the Javascript.

Some minor things:

I don’t think the option tag has a name parameter, but you seem to be assigning one. The name is surely attached to the select tag?

As your PHP code is retrieving information to populate the drop-downs, I believe it should really be accessed using GET. POST is traditionally only used when POSTing new data to the PHP code. A very minor thing, and doesn’t make any difference to how well your code works.

Hello droopsnoot,
The name in the options was one of the things I originally tried but obviously did not change anything.

However, since SamA74 lead me down the road of joining tables, I have the opposite problem now.

Before I even join the tables I am getting names placed into my users table instead of the ID. So my issue is either make everything show names(original problem), OR everything show IDs (current issue).

Currently I have mixed columns…and to make things worse the state and city inconsistently show names or IDs. The country is very consistently showing IDs only.

My php and javasrcipt/jquery/ajax is shown above. If you can see the problem please enlighten me.

Are you storing these in your users table in PHP, or is that your localStorage function? If it is localStorage, could it be that you are storing thisOption.text() rather than thisOptions.val()?

As I said, I don’t know JS but I try to learn stuff by looking at things like this, but I can’t do that easily as I don’t have any data. So I think I’ll wait to see how others suggest solutions to this issue.

Hello droopsnoot,
That is a possible problem. However, if I change " thisOption.text() " to " thisOptions.val() " my localstorage will then replace the names in the field into IDs. The users will then only see a bunch of ID numbers and not the state and city they chose.

We need to see the queries you are using. You should be able to select whatever columns you want from any of the tables joined in the query, then access them from the data retrieved.

Hello SamA74, I pasted all the queries above in the original question. Please see above.

    Thanks

Again, I’m not really that “up” on JS and Local Storage, but for me the idea is that you store the IDs, and you display the names, consistently. It’s really to have an ID and convert it to a name, but it’s much less easy to have a name and convert it to an ID.