如何根据用户选择从表中查找ID

Question

So I am creating some php code for the add accommodation section of my website. The user is given the option of selecting a city from the drop down menu (the list is retrieved from the city table in the mysql database by using a simple query and echo). But what needs to be stored in the add accomodation table is the city ID and of course I can't be asking the user for that! What I can't figure out is how to query the city table using the selected city and retrieve the city ID from the same table and store that in the accommodation table in the backend database. Here is the code I have so far.

<?php
$query = "SELECT City_Name FROM Cities";
$result = mysql_query($query);
?>
<p>
<label for = "City_Name" class = "fixed_required">City: </label>
<select name="select_City" id="select_City" value="<?php if(isset($_POST['City_Name'])) echo $_POST['City_Name']; ?>"/>
</p>
<?php
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
?>
<option value="<?php echo $line['City_Name'];?>"> <?php echo $line['City_Name'];?> </option>
<?php

Answer 1

Try this :

value="<?php echo $line['City_ID'];?>"

and change your query to include the ID :

$query = "SELECT City_Name, City_ID FROM Cities";

EDITED :

Ok...you also need to set up the "SELECTED" attribute base on $_POST['select_City'] on the OPTION tag and remove the value="<?php if(isset($_POST['City_Name'])) echo $_POST['City_Name']; ?>" on the SELECT tag....And like @Dan Revah suggested, use MYSQLI instead since mysql is deprecated as of PHP 5.5.0

<?php
$query = "SELECT City_Name, City_ID FROM Cities";
$result = mysql_query($query);
?>
<p>
<label for = "City_Name" class = "fixed_required">City: </label>
<select name="select_City" id="select_City" />
</p>
<?php
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
?>
<option value="<?php echo $line['City_ID'];?>"  
<?php
if(isset($_POST['select_City'])) { 
    if(mysql_real_escape_string($_POST['select_City'])==$line['City_ID']) { 
       echo "selected"
    }
}?>
>
<?php echo $line['City_Name'];?> </option>
<?php