Nesting SQL Queries in PHP output

[shamwowy]

Hi all, I have 2 SQL queries. One pulls from my items table, another that is just a reference table for item_type, which is a column in my items table. I'm trying to build a dynamic SELECT input, where if the item has a type, it will show up as selected already. If not, the default top item will be selected. Heres' the code, pared way down but hopefully you can see what I'm trying to do.

 

$query1 ="SELECT * FROM jitem JOIN jloc ON jloc.jloc_id IN(jitem.jitem_location)"; //SELECT ALL MY jitem items

$query2 ="SELECT jloc_id, jloc_loc FROM jloc"; //SELECT from a reference table for the select input options list

 

if( $r = mysql_query($query1) )

{

    while( $row = mysql_fetch_assoc($r) )

    {

          print "<tr>";

          print "<td align=center valign=top><img src='thumbs/{$row['jitem_thumb']}' alt='{$row['jitem_title']}' /></td>";

          print "<td align=center valign=top><input size=25 type=text name=jitem_title value='{$row['jitem_title']}'></td>";

          print "<td align=center valign=top><textarea  name=jitem_desc maxlength=1000 cols=35 rows=5>{$row['jitem_desc']}</textarea></td>";

          print "<td align=center valign=top>\$<input size=5 type=text name=jitem_price value='{$row['jitem_price']}'></td>";

 

          if( $r2 = mysql_query($query2) )

{

if (query1.jitem_loc = query2.jloc_id) //SYNTAX FOR THIS?

              {

    $selected = 'selected';

}

else

{

    $selected = '';

}

 

print "<td align=center valign=top><select>";

while( $row = mysql_fetch_assoc($r2) )

    {

          print "<option $selected value={$row['jloc_id']} name=jloc_id>{$row['jloc_loc']}</option>";

    }

print "</select></td>";

          }

[jcbones]

Have you tried something along the lines of?  Although your question (albeit non-existent) is very vague.

$query1 ="SELECT jitem.*, jloc.jloc_id, jloc.jloc_loc FROM jitem LEFT JOIN jloc ON jloc.jloc_id IN(jitem.jitem_location)"; //SELECT ALL MY jitem items
//$query2 ="SELECT jloc_id, jloc_loc FROM jloc"; //SELECT from a reference table for the select input options list

if( $r = mysql_query($query1) )
{
     while( $row = mysql_fetch_assoc($r) )
    {
          print "<tr>";
          print "<td align=center valign=top><img src='thumbs/{$row['jitem_thumb']}' alt='{$row['jitem_title']}' /></td>";
          print "<td align=center valign=top><input size=25 type=text name=jitem_title value='{$row['jitem_title']}'></td>";
          print "<td align=center valign=top><textarea  name=jitem_desc maxlength=1000 cols=35 rows=5>{$row['jitem_desc']}</textarea></td>";
          print "<td align=center valign=top>\$<input size=5 type=text name=jitem_price value='{$row['jitem_price']}'></td>";

         
	$selected = (!empty($row['jloc_id'])) ? 'selected="selected"' : NULL;
           $option[] = "<option $selected value={$row['jloc_id']} name=jloc_id>{$row['jloc_loc']}</option>";
    }
	print "<td align=center valign=top><select>";
		foreach($option as $value) { echo $value . "\n"; }
	print "</select></td>";
}

[Psycho]

I reversed the queries since the code I have uses the results of the jloc table first

//Function to create select options and pre-select the selected value
function selectOptions($optionsAry, $selectedValue)
{
    $optionsHTML = '';
    foreach($optionsAry as $value => $label)
    {
        $selected = ($value==$selectedValue) ? ' selected="selected"' : '';
        $optionsHTML .= "<option value=\"{$value}\"{$selected}>{$label}</option>\n";
    }
    return $optionsHTML;
}

//Create and run queries
$query1 ="SELECT jloc_id, jloc_loc FROM jloc"; //SELECT from a reference table for the select input options list
$query2 ="SELECT jitem_thumb, jitem_title, jitem_desc, jitem_price, jitem_loc
          FROM jitem JOIN jloc ON jloc.jloc_id IN(jitem.jitem_location)"; //SELECT ALL MY jitem items
$result1 = mysql_query($query1);
$result2 = mysql_query($query2);

if(!$result1 || !$result2)
{
    echo "There was a problem retrieving the records.";
}
{
    //Dump results of query1 into array
    $optionsAry = array();
    while($row = mysql_fetch_assoc($result1))
    {
        $optionsAry[$row['jloc_id']] = $row['jloc_loc']; 
    }
    
    while($row = mysql_fetch_assoc($result2))
    {
        print "<tr>\n";
        print "<td align=center valign=top><img src='thumbs/{$row['jitem_thumb']}' alt='{$row['jitem_title']}' /></td>\n";
        print "<td align=center valign=top><input size=25 type=text name=jitem_title value='{$row['jitem_title']}'></td>\n";
        print "<td align=center valign=top><textarea  name=jitem_desc maxlength=1000 cols=35 rows=5>{$row['jitem_desc']}</textarea></td>\n";
        print "<td align=center valign=top>\$<input size=5 type=text name=jitem_price value='{$row['jitem_price']}'></td>\n";
        print "<td align=center valign=top>\n";
        print "<select name=\"jloc_id\">\n";
        print selectOptions($optionsAry, $row['jitem_loc']);
        print "</select></td>\n";
    }   
}

[shamwowy]

Thanks to you both!! Very awesome of you (and very different from how that works in CF)