semiColon 0 Posted November 7, 2007 Report Share Posted November 7, 2007 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.. Quote Link to post Share on other sites
Ingolme 1,020 Posted November 7, 2007 Report Share Posted November 7, 2007 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 Quote Link to post Share on other sites
justsomeguy 1,135 Posted November 7, 2007 Report Share Posted November 7, 2007 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 Quote Link to post Share on other sites
semiColon 0 Posted November 7, 2007 Author Report Share Posted November 7, 2007 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? Quote Link to post Share on other sites
justsomeguy 1,135 Posted November 7, 2007 Report Share Posted November 7, 2007 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 />"; } Quote Link to post Share on other sites
semiColon 0 Posted November 8, 2007 Author Report Share Posted November 8, 2007 Yeah, It's better now.. But I don't know why it doesn't return any thing when i choose the country : ( Quote Link to post Share on other sites
Synook 47 Posted November 9, 2007 Report Share Posted November 9, 2007 What code do you have currently? Quote Link to post Share on other sites
semiColon 0 Posted November 9, 2007 Author Report Share Posted November 9, 2007 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);?> Quote Link to post Share on other sites
Ingolme 1,020 Posted November 9, 2007 Report Share Posted November 9, 2007 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> Quote Link to post Share on other sites
semiColon 0 Posted November 9, 2007 Author Report Share Posted November 9, 2007 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 : ( Quote Link to post Share on other sites
Ingolme 1,020 Posted November 9, 2007 Report Share Posted November 9, 2007 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> Quote Link to post Share on other sites
semiColon 0 Posted November 9, 2007 Author Report Share Posted November 9, 2007 yeah, that's fine now. Thanks : )but, do you know why i get that error? Quote Link to post Share on other sites
Ingolme 1,020 Posted November 9, 2007 Report Share Posted November 9, 2007 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. Quote Link to post Share on other sites
semiColon 0 Posted November 9, 2007 Author Report Share Posted November 9, 2007 Ops! so sorry : (. I'm really nervous this week because of the exam : (Thank you so much. Quote Link to post Share on other sites
Synook 47 Posted November 10, 2007 Report Share Posted November 10, 2007 A list of all HTTP codes can be found at http://www.modemhelp.net/httperrors/httperrors.shtml Quote Link to post Share on other sites
semiColon 0 Posted November 10, 2007 Author Report Share Posted November 10, 2007 Thanks Synook.. Quote Link to post Share on other sites
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.