Jump to content

how can i select cities from a database?


Recommended Posts

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..

Link to post
Share on other sites

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

Link to post
Share on other sites

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

Link to post
Share on other sites

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?

Link to post
Share on other sites

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 />";  }

Link to post
Share on other sites

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);?>

Link to post
Share on other sites

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>

Link to post
Share on other sites

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
: (
Link to post
Share on other sites

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>

Link to post
Share on other sites

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.

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...