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>';
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>';