[RESOLVED] How to link city database to menu?

phpzero

Hey all,

Newby problem of the day:

I would like to give the user of my interface the possibility to select two cities from a database to enable him/her to calculate the distance between both.

However, I would like the cities to be selected in two steps. First, "select the country" and then "select the city within the choosen country". My SQL database is made and now I would like to optimize the interface. First I thought about using a rolling menu, but now I'm not so sure it is the way to go.

This is how it looks so far:

<html>
<head><title>Definition of the trip</title></head>
<body>

<ul>
<?
mysql_connect("localhost", "username", "pasword");
mysql_selectdb("cities");

$listofairports = mysql_query("SELECT name, latitude, longitude FROM cities");

$row = mysql_fetch_array($listofcities);
while ($row) {
     echo ("<li>" . $row['name'] . ", " . $row['latitude'] . ", " . $row['longitude'] . "</li>");
     $row = mysql_fetch_array($listofcities);
}
?>
</ul>
<?
mysql_close();
?>


<form method="post" action="definevehicle.php">
<table width="650">
    <tr>
      <td width="250">Please select the city of departure:</td>
      <td><select name="departure" size="1">
        <option value="london"> London
        <option value="paris"> Paris 
        <option value="madrid"> Madrid
      </select><br></td>
       <br>
    </tr>
</table>
</form>

The output gives a list of the cities, followed by a roll-down menu including London, Paris and Madrid. But

My problem is that I have no clue on how to enable the user to select a city in the database without having to inserting them one by one in the form.
I mean how can I automatically include all of the cities of the database in the menu?

Anyone having a suggestion? Thanks a lot in any case!

Cheers!

rincewind456

Assuming you do a database select for the cities and the field is named city (if not change $row['city'] to suit you D😎

$dd = '<select name="departure" size="1"> ';
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){

$dd .= '<option value="'.strtolower($row['city']).'">'.$row['city'].'</option>';

}

$dd .= '</select>';

echo $dd;

And you should be using the Improved mysqli rather than the mysql extension.

phpzero

Hey! Thanks for the tip! 🙂

rincewind456;10930322 wrote:

And you should be using the [B]Improved[/B] mysqli rather than the mysql extension.[/QUOTE]
Could you tell me what "MYSQLI_ASSOC" is needed for please?

johanafm

fetch_array can get the row with array indexing consisting of numeric indexing only, the actual database field names only or both. This gives the 2nd one, an associative array.

phpzero

Thanks for your help 🙂

I tried to implement the suggestion of rincewind456. This is the code I have as a result:

<html>
<head><title>Definition of the trip</title></head>
<body>

<form method="post" action="definevehicle.php">
<?
mysql_connect("localhost", "phpuser", "pasword");
mysql_selectdb("cities");
$result=mysql_query("SELECT name, abbreviation, country FROM cities");
?>
<?
mysql_close();
?>

<table width="650">
    <tr>
      <td width="250">Please select the city of departure:</td>
      <td><$dd = '<select name="departure" size="1">'; 
while ($row = mysql_fetch_array($result, MYSQLI_ASSOC)){ 

$dd .= '<option value="'.strtolower($row['name']).'">'.$row['name'].'</option>'; 
} 
$dd .= 'echo $dd' </select>'; 

<br></td>
       <br>
    </tr>
</table>
If your departure city is not in the list, please provide its latitude and longitude <p>
<table width="400">

  <!--here the user will have to insert the longitude and latitude in degrees and minutes for departure-->
  <tr>  

	<td>Longitude:</td>
    <td><input type="text" name="departurelondeg" size="5"> °</td>
    <td><input type="text" name="departurelonmin" size="5"> '</td>
    <td><input type="text" name="departurelonsec" size="5"> "</td>
  </tr>
  <tr>
    <td>Latitude:  </td>
    <td><input type="text" name="departurelatdeg" size="5"> °</td>
    <td><input type="text" name="departurelatmin" size="5"> '</td>
    <td><input type="text" name="departurelatsec" size="5"> "</td>
  </tr>
  <tr>
    <td width="80"> </td>
	<td width="50" align="right">  <input type="submit" value="OK"></td> 
  </tr>
  </table>

</form>
 <br>
</body>
</html>

However, when using this code, the result I get is this (part of the screenshot):

That is: a roll-down menu '.$row['name'].' in its window.
<$dd:' before it and '; after it

I tried a couple of things, but it only made it worse. Anyone seeing what I'm doing wrong?

rincewind456

Yes you dropped out of PHP before you did your while loop. The code I gave you was to create the drop down in an array$dd, the idea is that you can then echo it out anywhere you want in the page.

Try this

<html>
<head><title>Definition of the trip</title></head>
<body>

<form method="post" action="definevehicle.php">
<?php
mysql_connect("localhost", "phpuser", "pasword");
mysql_selectdb("cities");
$result=mysql_query("SELECT name, abbreviation, country FROM cities");



$dd = '<select name="departure" size="1">';
while ($row = mysql_fetch_array($result, MYSQLI_ASSOC)){

$dd .= '<option value="'.strtolower($row['name']).'">'.$row['name'].'</option>';
}
$dd .= '</select>';
mysql_close();
?>

<table width="650">
    <tr>
      <td width="250">Please select the city of departure:</td>
      <td><?php echo $dd;?></td>
       <br>
    </tr>
</table>
If your departure city is not in the list, please provide its latitude and longitude <p>
<table width="400">

  <!--here the user will have to insert the longitude and latitude in degrees and minutes for departure-->
  <tr>  

    <td>Longitude:</td>
    <td><input type="text" name="departurelondeg" size="5"> &#176;</td>
    <td><input type="text" name="departurelonmin" size="5"> '</td>
    <td><input type="text" name="departurelonsec" size="5"> "</td>
  </tr>
  <tr>
    <td>Latitude:  </td>
    <td><input type="text" name="departurelatdeg" size="5"> &#176;</td>
    <td><input type="text" name="departurelatmin" size="5"> '</td>
    <td><input type="text" name="departurelatsec" size="5"> "</td>
  </tr>
  <tr>
    <td width="80"> </td>
    <td width="50" align="right">  <input type="submit" value="OK"></td>
  </tr>
  </table>

</form>
<br>
</body>
</html>

phpzero

We are getting closer, thanks a lot! 🙂 However, I still have a blank (empty) roll-down menu.
Any idea of what might still be missing/wrong in the code below? I double-checked the database, and it seems to be ok (I mean, it's not like it's empty or anything). However, I have a database called "cities", in which the table is called "cities" as well (the data I want to look up are inside that table). Could that be the problem?

<html>
<head><title>Definition of the trip</title></head>
<body>

<form method="post" action="definevehicle.php">
<?
mysql_connect("localhost", "phpuser", "password");
mysql_selectdb("cities");
$result=mysql_query("SELECT name, country FROM cities");

$dd = '<select name="departure" size="1"';
while ($row = mysql_fetch_array($result, MYSQLI_ASSOC)){
$dd .= '<option value="'.strtolower($row['name']).'">'.$row['name'].'</option>'; 
}
$dd .= '</select>';
mysql_close();
?>

<table width="650">
<tr>
<td width="250">Please select the city of departure:</td>
<td> <?php echo $dd;?></td>
<br>
</tr>
</table>
If your departure city is not in the list, please provide its latitude and longitude <p>
<table width="400">


<tr>

<td>Longitude:</td>
<td><input type="text" name="departurelondeg" size="5"> °</td>
<td><input type="text" name="departurelonmin" size="5"> '</td>
<td><input type="text" name="departurelonsec" size="5"> "</td>
</tr>
<tr>
<td>Latitude: </td>
<td><input type="text" name="departurelatdeg" size="5"> °</td>
<td><input type="text" name="departurelatmin" size="5"> '</td>
<td><input type="text" name="departurelatsec" size="5"> "</td>
</tr>
<tr>
<td width="80"> </td>
<td width="50" align="right"> <input type="submit" value="OK"></td>
</tr>
</table>

</form>
<br>
</body>
</html>

Thanks again for all your knowledge 😉 !!

rincewind456

I wouldn't think that would cause a problem, however you could:
1. Test the sql on the command line or through phpmyadmin to see if you get results.
2. Add some error checking to the query like

$result=mysql_query("SELECT name, country FROM cities") or die(mysql_error());

3. Change your query so that there is no ambiguity like

SELECT name, country FROM cities.cities

BTW why are you getting name and country in the query when you're only using name?

phpzero

Alright thanks, I've been looking/coding around and it works now 🙂

Concerning name and country. It's just because this is a first step and I would like to use both later on. Thanks again for your help! Problem solved! 🙂