将多个选项标记为已选择的数据库结果

Question

I have two tables, post_languages (columns: languageID, languageName) and post_to_languages (languageID, postID) and a select form.

I want to mark one or multiple option tags as selected by languageID using the results from the database. Don't want only to display the languages, I want to mark them as selected from a list of languages provided by the table post_languages.

What I have tried, but only one language is selected, $res returns only one ID:

$stmt = $db->prepare('SELECT languageID FROM post_to_languages WHERE postID = :postID');
$stmt->execute(array(':postID'=>$postID));
$result = $stmt->fetch(PDO::FETCH_ASSOC);
$stmt2 = $db->prepare('SELECT languageID, languageName FROM post_languages ORDER BY languageName');
$stmt2->execute();
echo '<select class="post-language form-control" name="postLangID[]" multiple="multiple" required>';
while($row2 = $stmt2->fetch()){
    foreach ($result as $res) {
        if ($row2['languageID'] == $res) {
            $selected = 'selected';
        } else {
            $selected = '';
        }                      
    } 
    echo '<option value="'.$row2['languageID'].'" '.$selected.'>'.$row2['languageName'].'</option>';
}
echo '</select>';

Answer 1

First reason is $stmt query. You have filtration by postID field. The second one is type of $res variable i think it's an array. Please try to var_dump it. I think it could look sth like:

foreach ($result as $res) {
        if ($row2['languageID'] == $res['languageID']) {
            $selected = 'selected';
        } else {
        $selected = '';
    }                      
} 

If $postID is an Array you can also try this code:

$place_holders = implode(',', array_fill(0, count($postID), '?'));
$stmt = $db->prepare("SELECT languageID, languageName, IF(post_languages.languageID IN (SELECT post_to_languages.languageID FROM post_to_languages WHERE post_to_languages.postID IN ($place_holders)), 1, 0) as selected FROM post_languages ORDER BY languageName");
$stmt->execute($postID);
echo '<select class="post-language form-control" name="postLangID[]" multiple="multiple" required>';
while ($row = $stmt->fetch()) {
    if ($row["selected"]) {
      echo '<option value="'.$row['languageID'].'" selected>'.$row['languageName'].'</option>';
    } else {
      echo '<option value="'.$row['languageID'].'">'.$row['languageName'].'</option>';
    }
}
echo '</select>';