Display stored value from mysql table into select input field

Question

In my form I have a select field that is populated with data from mysql db. I am currently unable to display the selected value for this select field. The data comes from a table named academy and each academy has a status of active and inactive. The status field in the table is enum type. I am trying to display the value stored in the table through the select input as the selected option. Here is an EXAMPLE.

    //Database connection
 <form action="" method="post">
    try {
        $db_con = new PDO($dsn, $user, $password);
        $db_con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    } catch (PDOException $e) {
        echo 'Connection failed: ' . $e->getMessage();
    } 

    $db_select1  = $db_con->prepare("
    SELECT a.name, 
           a.academy_id
    FROM academy a
    WHERE a.academy_id = 15
    ");
    if (!$db_select1) return false;
        if (!$db_select1->execute()) return false;
        $results1 = $db_select1->fetchAll(\PDO::FETCH_ASSOC);
        if (empty($results1)) return false;
        foreach ($results1 as $value1){
              $result1 .= "<strong>Academy Name: </strong>".$value1['name']."</br>";
              $result1 .= "<strong>Academy ID: </strong>".$value1['academy_id']."</br>";
        }

        echo $result1;
    ?>
     <strong>Academy Status:</strong>
            <?php
                    //Populate select input
                    $table_name2 = "academy";
                    $column_name2 = "status";

                    echo "<select name=\"$column_name2\"><option>Select one</option>";
                    $sql1 = 'SHOW COLUMNS FROM '.$table_name2.' WHERE field="'.$column_name2.'"';
                    $row1 = $db_con->query($sql1)->fetch(PDO::FETCH_ASSOC);
                    foreach(explode("','",substr($row1['Type'],6,-2)) as $option) {
                            echo "<option value='$option'>$option</option>";
                    }
                    echo "</select></br>";   
            ?>
     <input type="submit" name="submit" value="Update">
</form>

Store Values in academy table:

+------------+-------------------+--------+
| academy_id |       name        | status |
+------------+-------------------+--------+
|       15   | Brown High School | Active |
+------------+-------------------+--------+

James Smith James Smith · Accepted Answer · 2013-12-01T02:39:16

You need to use the "selected" attribute of the select html tag:

http://www.w3schools.com/tags/att_option_selected.asp

Do this when you are dynamically populating your options:

$selected = '';
foreach(explode("','",substr($row1['Type'],6,-2)) as $option) {
    if ($status == $option) // $status is the status of your record from the database
       $selected = "selected";
    echo "<option value='$option'" . $selected. ">$option</option>";
}

You will need to change your sql statement to select the status in addition to the name and academy Id

SELECT a.name, 
  a.academy_id
  a.status
FROM academy a
WHERE a.academy_id = 15

Then obviously populate the $status variable:

$status = $value1["status"];

Display stored value from mysql table into select input field

2 Answers