mysql可选输入是什么意思_使用mysql的值填充选择输入字段

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

$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 "

echo "

";

foreach ($results2 as $value2){

echo "

";

echo "

Name #".$s."";

echo "

";

$s++;

}

echo "

";

echo "

";

?>

would like to integrate this to select statement:

$table_name2 = "person";

$column_name2 = "person_status";

echo "Select one";

$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";

}

echo "";

?>

解决方案

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 "

";

echo "

Name #".$s."";

// Added

echo '

';

foreach($personStatuses as $option) {

echo '

if ($value2['person_status'] == $option) {

echo 'selected="selected"';

}

echo '>' . htmlspecialchars($option) . '

';

}

echo '

';

// Your code again

echo "

";

$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