storing, retrieving and display values from multi select forms

iRex

I have been struggling to make my form work for days, and I hope somebody cah help me out here.

Here's the scenario: suppose I have values "apple", "banana", "cherry", "dip" in a multi select list box list (with corresponding id#'s 1, 2, 3, 4). Although apple, banana, cherry, dip are displayed, there are retrieved from parent table PRODUCT; table ORG only stores the id's a concatenated string ("2 3")..

"banana" and "cherry" are selected and the form is submitted, values 2 and 3 are duly inserted into field ORG in table x as a string "2 3". For this particular scenario, I would like to the edit form script to retrieve and display all the values ("apple", "banana", "cherry", "dip" ) from the parent table (table PRODUCT), with the values selected ("banana" and "cherry") above shown as selected.
The following is an excerpt from the edit form script that I only partly works. Can someone please help me out with getting all the above logic to work. I also realize that there may very well be other more efficient ways to handle storiing and retrived linked values like these (and I would appreciate any suggestions in that regard); For instance, I have been reading up about using ENUM to store key values in the database, which I understand would make it easier since I can just store all those name values in the ORG table.
But in the case that I am dealing with - I have a hundred odd values - it just seems like that would be very messy to maintain the list. Hence my opting to have a parent table.

// I retrieve the stored string, i.e., values orginal selected from a list box , "$product_id" - "2 3"

<?$product_id=mysql_result($resultupdate,$x,"product_id"); ?>

<th align=left><b>Products :</b></td>
<td align=left>
<?
/below I change the above string from "2 3" to 2, 3 (for reasons that will be clear later)
$notIn = str_replace(" ", ", ", $product_id);

//split that into the orginal id values "2" and "3"

$pieces = explode(" ", $product_id);

// this part takes the array (split values 2 and 3) and uses them as id's for to retrieve the name values from the parent table (PRODUCT)
// this par
echo "<select multiple name=\"product_id\">";

for($i=0;$i<count($pieces);$i++) {
$id=$pieces[$i];
$query = "SELECT product_name
FROM product
WHERE product_id='$id'";
$xresult = mysql_query($query);
$product_name=mysql_result($xresult,0);
echo "<option value=\"".$product_name."\" selected>".$product_name;
}

// at this point the have a a list box with the two names - banana and cherry selected -
// the next part is where I try to add the rest of the names to the list box above - using the $notIn variable containing the selected values
//comma separated form 2,3 . This is where I get stuck - the list box does not show anything.
//the nex
$query2= mysql_query("SELECT product_name FROM product WHERE product_id != ".$notIn."");
$yresults= mysql_result($query2);
while ($products =mysql_fetch_array($yresult)) {
echo "<option name=\"" . $products. "\">" . $products;
}

echo ("</select>");

}
?>

Can anyone please help me?

kellog

// at this point the have a a list box with the two names - banana and cherry selected -
// the next part is where I try to add the rest of the names to the list box above - using the $notIn variable containing the selected values
//comma separated form 2,3 . This is where I get stuck - the list box does not show anything.
//the nex

You have the right idea, but are going about it the hard way.

Rather than adding the <option> tags for what is selected, then later adding <option> tags for what wasn't selected, you should add all <option> tags at once, then check to see if "selected" needs to be added. For instance, something like this:

$AllProducts = array ("Apples", "Oranges", "Bannanas", "Watermellon");
$SelectedProducts = array("0", "2");

echo "<select multiple name='product_id'>"; 

for ($Counter = 0; $Counter < count ($AllProducts); $Counter++)
{
	echo "<option ";
	if (in_array($Counter, $SelectedProducts))
	{
		echo "selected";
	}
	echo " value='$Counter'>".$AllProducts[$Counter];
}
echo "</select>";
?>

iRex

Thanks for the help. I have a teenie wee bit of a problem - I am a very new newbie and sometimes I can't tell my arrays from fetch_results... So how do I get the array using my code? I've tried several things with no luck. Here's what I have right now - which returns no results

<?
if ($product_id !="") {
$pieces = explode(" ", $product_id);
for($i=0;$i<count($pieces);$i++) {

$query = "SELECT product_id, product_name FROM product WHERE product_id='$pieces[$i]'";
$xresult = mysql_query($query);
         while($row=mysql_fetch_array($xresult))
         {
          $SelectedId=$row['product_id'];
          $SelectedProducts=$row['product_name'];
          }
}

$query2= "SELECT product_id, product_name FROM product";
$yresult= mysql_query($query2);
while($row=mysql_fetch_array($yresult))
         {
          $AllId=$row['product_id'];
          $AllProducts=$row['product_name'];
          }

echo "<select multiple name='product_id'>";
for ($Counter = 0; $Counter < count ($AllProducts); $Counter++)
{
echo "<option ";
if (in_array($Counter, $SelectedProducts))
{
echo "selected";
}
echo " value='$Counter'>".$AllProducts[$Counter];
}
echo "</select>";

}
?>

kellog

This is where my expertise runs out. I havn't dealt with databases in a long time, and therefor don't remember alot of this stuff off the top of my head. But I believe your problem is in how your trying to use the returned data from the database. Someone else with a fresher understanding of database stuff needs to help you.