database edit form with multi select fields

iRex

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:

iRex

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!

LordShryku

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

iRex

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

iRex

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

LordShryku

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 🙂

iRex

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>");