I need you friends to help look into this php code. This is the logic, If a user should select a country from the the drop down menu e.g "select your country" BELGIUM then ALL the states under BELGIUM be loaded inside the "select a states" input tag in the form. I created a database, two tables i.e Table 1, countries with (id,country_id, country). Table 2, (id,state_id,states). with following code frm php:

<td>NATIONALITY:&nbsp;</td>
<td><select name="na"><option name="c">-SELECT YOUR COUNTRY-</option>
<?php $sql = "select * from countries";
$result = mysql_query($sql);
while($data = mysql_fetch_assoc($result)){
echo "<option>".$data["countries"]."</option>"."<br>";
}
?>
</select></td>
</tr>

<tr>
<td>STATE OF ORIGIN:&nbsp;</td>
<td><select>
<?php $sql = "SELECT countries.country, state FROM countries, state WHERE countries.country_id = state.state_id LIMIT 0 , 30";
$result = mysql_query($sql);
$count =mysql_num_rows($result);
if($count==1 && $_REQUEST["na"]==1){
while($row = mysql_fetch_assoc($result)){
echo "<option>".$row["state"]."</option>"."<br>";
}
}
?>
</select></td>

So, the problem I have with above code is that when the page load and a country is selected, the page does not auto load the state of the selected country.

Can someone help here pls.

looking at your query:
"SELECT countries.country, state FROM countries, state WHERE <b>countries.country_id = state.state_id</b>"
perhaps what you intend to say is:
"SELECT state FROM countries WHERE country_id=". $_REQUEST['selected_country_id']
(notice I've made the query shorter, and a bit more efficient, you don't need the aliases)
then:

$row["state"] should give the right state.

"SELECT state FROM countries WHERE country_id=". $_REQUEST['selected_country_id']


so you mean STATE will be a field in the COUNTRIES table, like that? for all the countries i.e if i have 10countries under country field then their states or do you mean the STATE will hav its own table?



thanks

If you're trying to represent countries and their respective states, a schema you can consider is:
[countries] <-- table 1
+ id (auto_increment)
+ name (country names)

[states] <-- table 2
+ id (auto_increment)
+ name (state name)
+ country_id (foreign key referencing countries.id, be sure your MySQL engine is set to InnoDB if not this will not work)

So to see what country has what state or vice-versa
If someone selects a country ID of 45, your DB structure will look like:
[countries]
id name
---- | ---------
45 Nigeria

[states]
id | name | country_id
--- ------ -----------
23 Lagos 45

And your query is:
"SELECT name FROM states WHERE country_id=".$_REQUEST['country_value_selected']

OR if you want to see what country Lagos belongs to, now comes the fancy query :
"SELECT c.name, s.name
FROM states AS s
INNER JOIN countries AS c
ON c.id = s.country_id
WHERE s.country_id=".$_REQUEST['country_value_selected']

(notice how I used aliasing now, because I'm referencing two different tables vs when i'm referencing the same table)

So it's up to you, just keep your data as rational as possible to get the query you want (btw, i didn't test any of these, just from the top of head, so test carefully if you're using this in production).

d states are 36 sum country 20 states u made reference to lagos alone. I hav al 36 states in state table and so on for other countries. Pls see to this

I'm using that as an example. Just use that as an example for multiple states, same principle applies

so you mean STATE will be a field in the COUNTRIES table, like that? [img]http://www.dubaa.info/hardware.gif[/img]

Read my above analysis carefully.

Thanks a lot