database design for country,state,city dropdown

[Wailintun]

hello!everybody,
Now I use xampp for build database.
I need simple database design to country,state,city dropdown cuz my database is not good enought.
please!!!!
thanks!

[LastMitch]

I need simple database design to country,state,city dropdown cuz my database is not good enought.

Can you post the code of what you done so far?

Do you know how to create a table in a database?

I mean using PHP & MYSQL?

[Wailintun]

hi! friends.I'm sorry cuz I'm a little dull.please check my database and correct me.
here my database

    main_ID  main_Country   main_state  main_City
        1    Myanmar         Yangon      Yangon
        2    Myanmar         Yangon      Yankin
        3    Myanmar         Yangon      tarmwe
        4    Myanmar         Mandalay   mandalay
        5    Myanmar         Mandalay   kyone gyi
        6    Singapore       Singapore  Sing 1
        7    Singapore       Singapore  Sing 2

[LastMitch]

@Wailintun

hi! friends.I'm sorry cuz I'm a little dull.please check my database and correct me.

So you created a table in the database. Then what is the issue?

All you need to do is to create a query to fetch the data from the database.

[OsaMasw]

$sql = "SELECT * FROM YOUR_TABLE ";
$run = mysql_query($sql);
while($data = mysql_fetch_assoc($run)) {
echo $data['main_country'];
// and any type of design you like for information
}

[AARTI SHRIVAS]

<select>country
<option>Please select country</option>
<?php while($date=mysql_fetch_array($run))
{
?>
<option><?php echo $data['main_country'];?></option>
<?php }?>
</select>

use this same for fetching city and state name in drop down

[alfiechiong]

or you can create a function

function get_country(){
$query = "Select * from tablename";
$myquery = mysql_query($query);
while($row = mysql_fetch_array($myquery)){
echo "<option>".$row['main_country']."</option>";
}
}

same with the other fields

[gon1387]

Do you mean normalize further your table?

COUNTRY
country_id | country_name

COUNTRY_STATE
country_id | state_id

STATE
state_id | state_name

STATE_CITY
state_id | city_id

CITY
city_id | city_name

This one's requires joins, and lesser the size as yours; but I would prefer your design than this cause, a DB for location like this one would certainly require only a few bytes to fill up, since there's only a finite and fixed amount of values. And your normalized table is a lot better to reference at. The only question is, how to properly query.

can you please also tell us how should the table be used?