how can i select cities from a database?

[semiColon 0]

let's say i have some countries and their cities in the Database..and i have a form, i choose the country.. then it will print all the cities in that country..So.. i suggest that the syntax should be something like this:$result = mysql_query("SELECT * FROM countries");while($row = mysql_fetch_array($result)) { echo ......... ; }i stopped at the echo statement, because I'm not sure how should the data stored in the database. Should Icreate a table for each country? and in the while statement i include an IF statement for each country to select the chosen table?All that should be with: HTML, PHP, MySQL. Thanks..

[Ingolme 1,027]

I recommend haveing a table "cities".Here's an example of how it could look:

cities table:Name	  |  Country--------------------------------Madrid	|  SpainNew Jersey|  U.S.AParis	 |  FranceBarcelona |  SpainNew York  |  U.S.A

To get all the cities of a country:SELECT * FROM cities WHERE country = 'Spain'To get all the countries available:SELECT DISTINCT country FROM cities

[justsomeguy 1,135]

If you want to keep track of country stats other then just the name, like what the capitol is, or the population, or whatever, it might be better to have a country table and a city table. Each country and city would have an ID and the city table would have an ID for the country it belongs to.

Countriesid	name			population--------------------------------1	 Argentina	   403019272	 Brazil		  1838888413	 China		   1321851888Citiesid	name			  country-------------------------------1	 Buenos Aires	  12	 Rosario		   13	 Rio De Janeiro	24	 Sao Paulo		 25	 Beijing		   36	 Shanghai		  3

You could select cities based on a country ID:SELECT * FROM cities WHERE country=1or a country name:SELECT cities.* FROM cities, countries WHERE countries.name='Brazil' AND cities.country=countries.id

[semiColon 0]

Thank you so much, I really appreciate that..Ingolme, Thanks a lot..justsomeguy, thanks so much..I'm trying now the simple one which Ingolme describe it, after that I'll go to justsomeguy's suggestion...this is the Form:

choose a country:<form action="getcitiy.php" method="get"><select name="country"><option value="select">select:</option><option value="france">France</option><option value="USA">U.S.A</option><option value="Spain">Spain</option></select></form>

and this is the "getcity.php":

<?php$con = mysql_connect("localhost","root","");if (!$con)  {  die('Could not connect: ' . mysql_error());  }mysql_select_db("countriesdb", $con);if ( $country=$_POST["france"] )$result = mysql_query(SELECT * FROM cities WHERE country = 'France');elseif $country=$_POST["USA"]$result = mysql_query(SELECT * FROM cities WHERE country = 'USA');elseif $country=$_POST["spain"]$result = mysql_query(SELECT * FROM cities WHERE country = 'Spain');while($row = mysql_fetch_array($result))  {  echo $row['name'] . " " . $row['country'];  echo "<br />";  }mysql_close($con);?>

what do you think?

[justsomeguy 1,135]

It's better to have the value as it appears in the database so that you can just substitute the value into the query instead of using an if structure. You can write the form like this, after you connect to the database:

<form action="getcitiy.php" method="get"><select name="country">  <option value="">select:</option>  <?php  $result = mysql_query("SELECT DISTINCT country FROM cities");  while ($row = mysql_fetch_assoc($result))  {	echo "<option value=\"{$row['country']}\">{$row['country']}</option>";  }  ?></select></form>

Then your other part can look like this:

$result = mysql_query("SELECT * FROM cities WHERE country = '" . mysql_real_escape_string($_POST['country']) . "'");while($row = mysql_fetch_array($result))  {  echo $row['name'] . " " . $row['country'];  echo "<br />";  }

[semiColon 0]

Yeah, It's better now.. But I don't know why it doesn't return any thing when i choose the country : (

[Synook 47]

What code do you have currently?

[semiColon 0]

This is the form:

<?php$con = mysql_connect("localhost","root","");if (!$con)  {  die('Could not connect: ' . mysql_error());  }mysql_select_db("countriesdb", $con);?>choose a country:<form action="getcitiy.php" method="get"> <select name="country">  <option value="">select:</option>  <?php  $result = mysql_query("SELECT DISTINCT country FROM cities");  while ($row = mysql_fetch_assoc($result))  {	echo "<option value=\"{$row['country']}\">{$row['country']}</option>";  }  ?></select></form>

and this is the "getcity.php":

<?php$con = mysql_connect("localhost","root","");if (!$con)  {  die('Could not connect: ' . mysql_error());  }mysql_select_db("countriesdb", $con);$result = mysql_query("SELECT * FROM cities WHERE country = '" . mysql_real_escape_string($_GET['country']) . "'"); while($row = mysql_fetch_array($result))  {  echo $row['name'] . " " . $row['country'];  echo "<br />";  }mysql_close($con);?>

[Ingolme 1,027]

Well, you might want to submit the form first:<form action="getcitiy.php" method="get"><select name="country"> <option value="">select:</option> <?php $result = mysql_query("SELECT DISTINCT country FROM cities"); while ($row = mysql_fetch_assoc($result)) { echo "<option value=\"{$row['country']}\">{$row['country']}</option>"; } ?></select><input type="submit" value="Select" /></form>

[semiColon 0]

Thanks a lot a lot Ingolme..Isn't there another way to submit it without the submit button? I mean: when the user selects the country, it'll be submit..I tried to add the submit button as you mentioned above, but i face an error that is:

Object not found!The requested URL was not found on this server. The link on the referring page seems to be wrong or outdated. Please inform the author of that page about the error.If you think this is a server error, please contact the webmaster.Error 404localhost11/09/07 16:12:48Apache/2.2.6 (Win32) DAV/2 mod_ssl/2.2.6 OpenSSL/0.9.8e mod_autoindex_color PHP/5.2.4

: (

[Ingolme 1,027]

Well, the action attribute of the <form> tag was misspelled. You can make the form submit when you select an option by doing this:<form action="getcity.php" method="get" id="myForm"><select name="country" onchange="document.getElementById('myForm').submit()"><option value="">select:</option><?php$result = mysql_query("SELECT DISTINCT country FROM cities");while ($row = mysql_fetch_assoc($result)){echo "<option value=\"{$row['country']}\">{$row['country']}</option>";}?></select></form>

[semiColon 0]

yeah, that's fine now. Thanks : )but, do you know why i get that error?

[Ingolme 1,027]

That error means that the file (getcitiy.php) wasn't found on the server. Seeing as it was spelled wrong it's not a surprise the error was returned. It's called a 404 error, one between a list of errors that the servers have programmed to handle certain requests.

[semiColon 0]

Ops! so sorry : (. I'm really nervous this week because of the exam : (Thank you so much.

[Synook 47]

A list of all HTTP codes can be found at http://www.modemhelp.net/httperrors/httperrors.shtml

[semiColon 0]

Thanks Synook..