selecting country and list [Archive]

hemi519

11-18-2010, 07:04 PM

Hi All,

I was trying to select some country and with respective to that the states should be displayed in the states drop down filed. But I am able to countries list but not the states list. Plz anyone help me to solve my problem

The below is my code and i have attached the database file also.

Dont mind about the table alignment.

<?php

mysql_connect("localhost","root","")or die ("connection failed");
mysql_SELECT_db("countries")or die ("connection error");

echo"
<table align=500 ><tr><td width='456'>
Country <select name='statecountrycode' id='code'> <option value='0'> select category </option>";

$res1=mysql_query("select * from Country ");
if($res1) {
while($row=mysql_fetch_array($res1))
{
echo "<option value='{$row[code]}' >{$row[Name]} </option>";
}
}
echo"
</select>
</td></tr>
<tr><td>
<select name='statecountryode' id='coe'> <option value='0'> select category </option>";
$res1=mysql_query("select * from States where CountryCode='$row[code]'");
if($res1)
{
while($rowq=mysql_fetch_array($res1))
{
echo "<option value='{$rowq[ID]}' >{$rowq[District]} </option>";
}
}
echo"</select>
</table>";

?>

Schmoopy

11-18-2010, 07:21 PM

I've put your SQL in my database, but it's missing the "States" table, unless the "city" table is what you're referring to.

Also, you're trying to use a variable that is not available in the while loop here:

$res1=mysql_query("select * from States where CountryCode='$row[code]'");

The $row[code] bit is not going to work, as you're not in the while loop anymore that has access to $row.

I think for this to work effictively, you're going to want to use AJAX to send off a request with the CityCode from the first dropdown, which will then populate the second dropdown with all the cities for that country.

If that's what you want it to do, let me know and I'll show you how.

Schmoopy

11-18-2010, 08:11 PM

Sorry to double post, but got bored and decided to write this script anyway, which does what I described above:

Relies on two files, the main file and the lookup.php file that gets the cities via an AJAX request (relies on jQuery for this to work):

Main php file:

<html>

<head>
<script type="text/javascript" src="http://code.jquery.com/jquery-1.4.4.min.js"></script>
<script type="text/javascript">
$(document).ready( function () {

// Attach event handler to the countryselect element
$('#countryselect').change( function() {

if($(this).val() == '') {
$('#cityselect').html('<option value="">Please select a country first....</option>');
} else {

// Show the user that the script is actually doing something
// This won't ever really be seen when working on a local machine
$('#cityselect').html('<option value="">Loading cities...</option>');

// Send off AJAX request with the code the user specified
$.ajax({
url : 'lookup.php',
data : 'code=' + $(this).val(),
success : function(data) {
// Populate the city dropdown with the cities for the country
$('#cityselect').html(data);
},
type : 'get'
});

}
});

});
</script>
</head>

<body>

<?php

mysql_connect("localhost","root","") or die("Failed connect to the database");
mysql_select_db("countries") or die("Failed to select the database");

?>
<table><tr>
<td>Select Country:</td> <td><select name='statecountrycode' id='countryselect'> <option value=''></option>

<?php

$res1 = mysql_query("SELECT * FROM country");

if($res1) {

while($row=mysql_fetch_array($res1)) {

echo "<option value='" . $row['Code'] . "'>". $row['Name'] . "</option>";

}

}
?>
</select></td>
</td></tr>
<tr><td>Select City:</td><td><select name='statecountryode' id='cityselect'> <option value='0'>Please select a country first...</option>

</select></td></tr>
</table>

</body>

</html>

lookup.php:

<?php

if(isset($_GET['code'])) {

// Connect / select to the database
mysql_connect("localhost","root","") or die("Failed to connect to the server");
mysql_select_db("countries") or die("Failed to select the database");

// Escape input for query
$code = mysql_real_escape_string($_GET['code']);

// Select all cities for the given code
$SELECT_CITIES = "SELECT * FROM city WHERE CountryCode = '" . $code ."'";

$CITIES = mysql_query($SELECT_CITIES);

// Loop through the cities and output HTML for JavaScript to use
while($city = mysql_fetch_array($CITIES)) {

echo '<option value="' . $city['ID'] . '">' . $city['Name'] . '</option>';

}

}

?>

This will work with the tables created via the world.sql file that hemi519 provided.

hemi519

11-19-2010, 07:05 AM

thanks for that, but with the code u sent is still not populating the cities for me. I am able to get countries list but not states. I think, when th country is changed javascript is not working and it is not taking into lookup.php file

Schmoopy

11-19-2010, 07:09 AM

What sort of error(s) are you getting, if any?
I've tested this with the exact zip file you uploaded, it should work fine.
Remember that in your original post you were using "States" as a table, so you may need to rename that table to "city", or rename it in the code, either way will work.

If you still can't get it to work, just try and put up any SQL / JavaScript errors you're getting.

hemi519

11-19-2010, 07:44 AM

No I am not getting errors,country is not loading any state. the state filed is just empty. i have modified from city to States in the table. In the javascript i have written a alert to display when a country is selected at the very starting of function(). But when the country is changed no alert is showed, by this iam thinking that it is not calling any function

Schmoopy

11-19-2010, 07:47 AM

See what's being passed when the AJAX function is called, $(this).val() should contain a country code.

Try alerting it to see what you get, then follow it through the lookup.php file and see where it's going wrong, maybe the path is incorrect?

hemi519

11-19-2010, 08:54 AM

This is how iam using the main php file.*

<?php

mysql_connect("localhost","root","xxxx") or die("Failed connect to the database");
mysql_select_db("countries") or die("Failed to select the database");

?>

<html>

<head>
<script type='text/javascript' src='http://code.jquery.com/jquery-1.4.4.min.js'></script>
<script type='text/javascript'>
$(document).ready(

function ()
{

alert("ddd");

// Attach event handler to the countryselect element
$('#countryselect').change( function()
{

alert("ddd");

if($(this).val() == '')
{

alert("ddd");

$('#cityselect').html('<option value=''>Please select a country first....</option>');

}
else
{

// Show the user that the script is actually doing something
// This won't ever really be seen when working on a local machine
$('#cityselect').html('<option value="">Loading cities...</option>');

// Send off AJAX request with the code the user specified
$.ajax({
url : 'lookup.php',
data : 'code=' + $(this).val(),
success : function(data) {
// Populate the city dropdown with the cities for the country
$('#cityselect').html(data);
},
type : 'get'
});

}
});

});
</script>
</head>

<body>

<table><tr>
<td>Select Country:</td> <td><select name='statecountrycode' id='countryselect'> <option value=''></option>

<?php

$res1 = mysql_query("SELECT * FROM Country");

if($res1) {

while($row=mysql_fetch_array($res1)) {

echo "<option value='" . $row['Code'] . "'>". $row['Name'] . "</option>";

}

}
?>

</select></td>
</td></tr>
<tr><td>Select City:</td><td><select name='statecountryode' id='cityselect' onchange='javascript: document.countries.submit();'> <option value='0'>Please select a country first...</option>

</select></td></tr>
</table>

</body>

</html>

hemi519

11-19-2010, 03:02 PM

thanks for that thr was a problem in my DB. now i got it running