PHP, MySQL

I have a database form with on multiple select field (drop down list). I concatenate and store the selected values in a single field ( 1 2 3). I can do this without a problem. I can also retrieve and split the values quite well. My problem, as you will see from the following snippet is that the values are id's - and I would like to use these to retrieve associated descriptions (names) from a parent table.

<?

$product_id=mysql_result($resultupdate,$x,"product_id");
$pieces = explode(" ", $product_id);

$countx =count($pieces);
$i = 0;
while ($i < $countx) {
$id=$pieces[$i];
echo $id;
$query = "select product_name from products where product_id='$id'";
$product_name = MYSQL_QUERY($query);
$product_name;
echo("\n");
$i++;

	} 

?>

everything works except the part where I attempt to retrieve 'product_name'. Can anybody help please. I am relatively new at this and would appreciate a response in layman's terms if possible.

I have also been grappling with the idea of not only returning the product_name values, but displaying them 'selected' in a drop-down list. I have been wrestling withis for days - I'm a little more than a beginning programmer, but I sure could use some help.

iRex:eek:

    I gigured out part of the problem; although any suggestions to improve the efficacy of the code are appreciated - here's what works:

    <?

    $product_id=mysql_result($resultupdate,$x,"product_id");
    $pieces = explode(" ", $product_id);

    $countx =count($pieces);
    $i = 0;
    while ($i < $countx) {
    $id=$pieces[$i];
    $query = "select product_name from product where product_id='$id'";
    $xresult = MYSQL_QUERY($query);
    $product_name=mysql_result($xresult,0);
    echo $product_name;
    echo("\n");
    $i++;

    	} 

    ?>

    I still can't figured out how to display these selected in a drop-down list, i.e., together with the non-selected values. Help!

      If you want them to be selected, you can add the selected value in the dropdown. Try something like this:

      <? 
      $product_id=mysql_result($resultupdate,$x,"product_id"); 
      $pieces = explode(" ", $product_id); 
      
      echo "<select multiple name=\"blah\">";
      
      for($i=0;$i<count($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;
      }
      ?> 
      

        thanks, LordShryku
        I tried out your solutions as is - and all I got was the same value repeated 'count' times. I tried inserting the line
        $id=$pieces(i); but although I could get the correct selected values, I was not getting all the possible values form the field.

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

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

        Here's the scenario: suppose I have values a, b, c, d in a multi select list box list (with corresponding id#'s 1, 2, 3, 4). Although a, b, c and d are displayed, there are retrieved from parent table z; table x only stores the id's (concatenated, as I previously pointed out.

        b an c are selected and the form is submitted, values 2 and 3 are duly inserted into field x1 in table x as a string "2 3". When the string retrieved, I first split into the original id's 2 and 3; and then I retrieve the respective display values b nd c from the parent table - and display them in a list box as selected... I have been able to succesful do this with your help...

        But what I eventually want to have is the resulting list box display all the values (a,b,c and d) and showing b and c selected (highlighted). Can you please help me ou with this part?

        $id=$pieces($i);

          correction...to make your suggestion work i had to make two changes...otherwise I kept getting one value repated $count number of times:

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

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

            Right, or just

            WHERE product_id= '$pieces[$i]'";

            As for your drop down box, since your storing this in two seperate tables, off the top of my head, I say to do two queries for your drop down. Like, taking you scenario, get the variables that are selected, and display them in the drop down. The create a variable with what is selected(in your scenario "2 3"). Do a str_replace to add a comma.

            $notIn = str_replace(" ", ", ", $result);

            Then grab what's left.

            $query2 = mysql_query("SELECT <field> FROM <table> WHERE <field> NOT IN (".$notIn.")

            That's all I can think of at the moment. I'll have to look at it tomorrow. After I've had a little sleep 🙂

              thanks again for the help; but after 7+ plus hours without success, I'm on my knees looking for help...I tried the last suggestions you gave me and, with some tweaking, I was able to avoid explicit error messages. What I do get is , well, nothing - just blank where I was hoping the would be a populated list box. Here's what I have right now:-

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

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

              $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['product_id'] . "\">" . $products['product_name'] . "</option>\n"); 
              	} 

              echo ("</select>");

                Write a Reply...