Populating select input field with value from mysql

Question

My form has input fields with values populated from mysql table. In my select statement I am passing these values to the fields. The table is called person and has a unique id person_id and foreign key academy_id. Each person has a status active or inactive stored in field name person_status. I am having difficulties pulling the values from person_status for each person. How would I show the status for each person inside the select query? EXAMPLE

Select query to populate

<?php

   $id = 15; 
$db_select2  = $db_con->prepare("
        SELECT     a.name, 
                   a.academy_id,
                   p.person_id,
                   p.person_status,
                   p.first_name
        FROM academy a
        LEFT JOIN person p ON a.academy_id = p.academy_id
        WHERE a.academy_id = :id
        ");
        if (!$db_select2) return false;
        if (!$db_select2->execute(array(':id' => $id))) return false;
            $results2 = $db_select2->fetchAll(\PDO::FETCH_ASSOC);
            if (empty($results2)) return false;
            $result2 = '';
            $s = 1;
            echo "<table>";
            echo "<tbody>";
        foreach ($results2 as $value2){ 
            echo "<tr>";
            echo "<td>Name #".$s."<input type=\"hidden\" name=\"person_id_".$s."\" value='". $person_id = $value2['person_id']."' readonly=\"readonly\"/><input id=\"person_fname_".$s."\" name=\"person_fname_".$s."\" placeholder=\"Person #".$s." First Name\" type=\"text\" value='" . $value2['first_name'] ."'/></td>";
            echo "</tr>";
                $s++;   
        }
        echo "</tbody>";
        echo "</table>";    


?>

would like to integrate this to select statement:

<?php
$table_name2 = "person";
$column_name2 = "person_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);
    $selected1 = '';
    foreach(explode("','",substr($row1['Type'],6,-2)) as $option) {
    if ($status == $option){
          $selected1 = "selected=selected";
    }else{
          $selected1='';
    }
          echo "<option value='$option'" . $selected1. ">$option</option>";
    }
    echo "</select></br>";   
?>

I would also recommend tagging this with mysql and/or sql. Also, one nit pick: If you aren't using variable substitution, and are instead just writing text out or concatenating strings, don't use double quotes. That's going to tell the parser to read that string for variables — Zarathuztra

Peter van der Wal Peter van der Wal · Accepted Answer · 2013-12-07T19:15:14

Get options only once (no need to repeat this for every person):

$sqlStatuses = 'SHOW COLUMNS FROM '.$table_name2.' WHERE field="'.$column_name2.'"';
$rowStatuses = $db_con->query($sql1)->fetch(PDO::FETCH_ASSOC);
$personStatuses = explode("','",substr($rowStatuses['Type'],6,-2));

Then, walk over the persons

foreach ($results2 as $value2) { 
    // Your code
    echo "<tr>";
    echo "<td>Name #".$s."<input type=\"hidden\" name=\"person_id_".$s."\" value='". $person_id = $value2['person_id']."' readonly=\"readonly\"/><input id=\"person_fname_".$s."\" name=\"person_fname_".$s."\" placeholder=\"Person #".$s." First Name\" type=\"text\" value='" . $value2['first_name'] ."'/></td>";

    // Added
    echo '<td><select name="person_status_'.$s.'">';
    foreach($personStatuses as $option) {
        echo '<option value="'.htmlspecialchars($option).'" ';
        if ($value2['person_status'] == $option) {
            echo 'selected="selected"';
        }
        echo '>' . htmlspecialchars($option) . '</option>';
    }
    echo '</select></td>';

    // Your code again
    echo "</tr>";
    $s++;   
}

Building this into one SELECT-query is unneccessary complex (although possible, but gives you unreadable code).

Oh, and take a look at htmlspecialchars(), if a name contains a "-character your HTML get screwed up

Populating select input field with value from mysql

1 Answers