[RESOLVED] need to apply WHERE to SELECT clause that uses CONCAT.... I'm confused....

[annie_webby]

I'm in over my head again and hoping someone can lend me a hand.

I'm trying to use a select CONCAT query to create one of the values for my database driven Select Form field. the problem is that I need to limit it to only create options for items which appear in the "aircraft" array I've created earlier in the page. Because the field I want to limit by "model_code" is not in the CONCAT request it won't work (I get a "unknown column" type of error) I've been trying to figure something out but I just feel like that has to be a fairly straightforward way to do this.

Here is where I pull the list I'd like to limit the results to and make it usable:

<?php
$aircraft_id=$_GET['article_id'];

connection good stuff here:


$query=" SELECT * FROM articles WHERE id='$article_id'";
$result=mysql_query($query);
$num=mysql_numrows($result);



$i=0;
while ($i < $num) {

$aircraft=explode(',', $aircraft_result);
$num_ac = count($aircraft);


++$i;
}

?>

Below is pretty much the whole code relating to the select boxes. The left box has a full list of aircraft and I'd like the right box to only have the aircraft that have previously been selected and are stored in my database as a comma delineated list.

<table border="0">

<tr>

<td>



<select multiple size="20" style="width:70" name="listLeft">



<?php

$query1 = "SELECT *,CONCAT( make, ' ', model ) AS aircraft_label FROM aircraft_models";

$result1 = mysql_query($query1) or die(mysql_error());



$primary = array();


while ($row = mysql_fetch_assoc($result1)) {
$primary[$row['model_code']] = $row['aircraft_label'];
}

$primary = str_replace(" ", " ", $primary);


foreach ($primary as $key => $value)
{
echo '<OPTION value='.$key.'> '.$value.'';
}
echo '</option>';

?>

</select>

</td>

<td><NOBR>

<input type="button" style="width:90" onclick="moveDualList( this.form.listLeft, this.form.listRight, false );arrayWrite()"

name="Add >>" value="Add >>"> <BR>

<NOBR>

<input type="button" style="width:90" onclick="moveDualList( this.form.listRight, this.form.listLeft, false );arrayWrite()"

name="Add <<" value="Remove <<"> <BR>

<NOBR>

<input type="button" style="width:90" onclick="moveDualList( this.form.listLeft, this.form.listRight, true );arrayWrite();"

name="Add All >>" value="Add All >>"> <BR>

<NOBR>

<input type="button" style="width:90" onclick="moveDualList( this.form.listRight, this.form.listLeft, true );arrayWrite()"

name="Add All <<" value="Remove All <<"> <BR>

</NOBR>

</td>

<td>

<select multiple size="20" style="width:70" name="listRight">
<option value="test">test2</option>

<?php

$query4 = "SELECT *,CONCAT( make, ' ', model ) AS aircraft_label2 FROM aircraft_models";
$result4 = mysql_query($query4) or die(mysql_error());

$primary4 = array();

while ($row = mysql_fetch_assoc($result4)) {
$primary4[$row['model_code']] = $row['aircraft_label2'];
}

$primary4 = str_replace(" ", " ", $primary4);

foreach ($primary4 as $key => $value)
{
echo '<OPTION value='.$key.'> '.$value.'</option>';
}

mysql_close();

?>
</select>
</td>
</tr>
</table>

Any suggestions or hints would be very welcome!

[chazzy]

i'm not sure i follow. is there some kind of pseudo foreign key between the two tables that you're using to try to limit the results? since you're using * everywhere, it's hard to tell what the structure of the tables are.

[annie_webby]

Sorry. I'm pretty sloppy when it comes to code. That's what happens when designers try to be coders. :o

Anyway. I actually just got it working so I'm going to slap a resolved on this one. Thanks for taking the time to look at my mess though!