Union And Option Highlighting

[son]

I have a drop-down menu where user should see all available option with already selected options highlighted. I use the following query to get relevant results:

$q2 = '(SELECT ageing_id, ageing_name, 1 AS selected FROM ageing as a, productAge AS p WHERE p.product_id=' . $id . ' AND p.age_id=a.ageing_id)';$q2 .= ' UNION ';$q2 .= '(SELECT ageing_id, ageing_name, 0 AS selected FROM ageing)';$q2 .= ' ORDER BY ageing_id';

My problem, the data is displayed twice for those where the already selected options, the only way to have one being displayed is to take:'1 AS selected' and '0 AS selected' out. But this is no good as I do not know how else I can distinguish where data is coming from to highlight the relevant options as:if ($ageing_id['selected']) echo ' selected="selected"';Highlighting in general works fine.So, how can I mark first section of query as results that should be highlighted without displaying everything twice? DISTINCT is standard anyway and does not help in this case...Son

[justsomeguy]

How about adding a WHERE condition to the second one? You have a WHERE condition for the first one to show which are selected, doesn't it makes sense to have the inverse of that condition to show which are not selected? Or, you could leave out the selected column and check that in PHP.

[son]

How about adding a WHERE condition to the second one? You have a WHERE condition for the first one to show which are selected, doesn't it makes sense to have the inverse of that condition to show which are not selected? Or, you could leave out the selected column and check that in PHP.

How can you check this in PHP?Son

[justsomeguy]

You tell me. How about if the product_id == $id?

[son]

You tell me. How about if the product_id == $id?

I worked it out with:

<?php$q2 = 'SELECT ageing_id, ageing_name FROM ageing ORDER BY ageing_id';$r2 = mysqli_query($dbc, $q2);if (mysqli_num_rows($r2) > 0){?><div style="float:left; width:130px;"><p><label for="ageing"><?php echo check_error('ageing', 'Ageing'); ?></label><br /><select name="ageing[]" id="ageing[]" multiple="multiple" size="3"><option value="0">Not applicable</option><?phpwhile (list($ageing_id, $ageing_name) = mysqli_fetch_array($r2, MYSQLI_NUM)) {echo "<option value=\"$ageing_id\"";// check if there are ageing option assigned to product$q2a = "SELECT age_id FROM productAge WHERE age_id = $ageing_id AND product_id = " . $id . "";$r2a = mysqli_query($dbc, $q2a);if (mysqli_num_rows($r2a) > 0){echo ' selected="selected"';}echo ">" . $ageing_name . "</option>\n";}echo "</select></p>\n";?>

Many thanks for your helpful hints,Son