CODEWITHSUNDEEP
phpmysqlforms
Roddeh
Roddeh

Reputation: 207

php drop-down menu from MYSQL, how to return other column value?

I'm having a input form that asks for a location. The locations are stored in a mysql db and have an id (colomn: id and column: location).

I have a drop down menu that is generated from those records in the db:

    <select name="location">

    <?php                         
            $query="SELECT location FROM locations";
                $result=mysql_query($query) or die;

            while ($row=mysql_fetch_array($result)) {

            $location=$row['location'];

            echo "<option>$location</option>";
    ?>

    </select>

This all works. When the form is sumbitted, I obviously get a POST[location] for example "Belgium".

Let's say Belgium is the location in my db and has id 5, how can I return the ID as the POST variable from the dropdown box, instead of the location. Ofcourse I want the dropdown to show the locations, and not the ID's.

Thanks in advance!

Upvotes: 0

Views: 3184

Answers (4)

CodeAngry
CodeAngry

Reputation: 12985

<select name="location">
<?php                         
// select columns you need, separate by , (comma)
$query = "SELECT `column1`, `column2` FROM `locations`;";
$result = mysql_query($query) or die;
while ($row = mysql_fetch_array($result)) {
    // selected columns become accessible in $row array
    // value attribute needs to be escaped here
    echo '<option value="', htmlentities($row['column1']),'">',
        htmlentities($row['column2']), '</option>'; // escape label too
    // <option> does not accept HTML in label so it should be escaped
} // done!
?>
</select>

^ this (read comments for explanations)

Upvotes: 0

Marty
Marty

Reputation: 4657

if you change the SQl query to include the ID of the location, you can assign that value to the dropdown selected value.

<select name="location">
<?php   
$query="SELECT id, location FROM locations";
$result=mysql_query($query) or die;
while ($row=mysql_fetch_array($result)) {

    $location=$row['location'];
    $id = $row['id'];
    echo "<option value='".$row['id']."'>".$location."</option>";
?>
</select>

Upvotes: 1

Mihai Matei
Mihai Matei

Reputation: 24276

Each option can take a value and show another string so use value="my_value" for each option inside the select tag

<select name="location">
    <?php                         
        $query="SELECT id, location FROM locations";
        $result=mysql_query($query);

        while ($row=mysql_fetch_array($result)) {
            echo "<option value=\"" . $row['id'] . "\">" . $row['location'] . "</option>";
        }
    ?>
</select>

now your POST['location'] will contain the db id for selected location

Upvotes: 3

Rob
Rob

Reputation: 3574

select ID from locations;

$ID=$row['ID'];

Replace ID by the ID-name of your column ofcourse.

If you say select * from locations you can do something like this:

$row['anyCOLUMNNAME']

You can choose any column you like and use that information from that particular row.

Upvotes: 0

Related Questions