Pre selected dynamic list from DB set.

Hi I've been searching for a solution for this for a few days now but can't find the answer. I have a 2 tables in a DB one called stock and one called colors. The stock table has a "color" field which is a set and relates to the color table like below:

--------------------------
| id | color_en | color_es |
| 1  | white    | blanco   |
| 2  | black    | negro    |
| 3  | blue     | azul     |

etc etc...

Code (markup):

These colores can be multi selected via a form list and are inserted into the DB as a set eg. if white and blue are selected the DB entry looks like 1,3

I want to make an edit page which when opened has a form (multiple list) with all the colors listed and that item's colors pre selected.

this is the code I have:

<select name="colours[]" size="12" multiple id="colours">
<?php 
$colsel = explode(",",$row_edit['colours']);

?>
<?php foreach ($colsel as $val) { 
echo '
<option value="'.$val.'" selected="'.$colsel[$val].'">'.$val.'</option>';
}
?>

</select>

Code (markup):

This only calls up a list with the colors pre selected for that item but not all the colors. How can I make this display all the colors.

here are the queries:

$colname_edit = "-1";
if (isset($_GET['stockid'])) {
  $colname_edit = (get_magic_quotes_gpc()) ? $_GET['stockid'] : addslashes($_GET['stockid']);
}

mysql_select_db($database_giusi, $giusi);
$query_edit = sprintf("SELECT * FROM stock WHERE stockid = %s", $colname_edit);
$edit = mysql_query($query_edit, $giusi) or die(mysql_error());
$row_edit = mysql_fetch_assoc($edit);
$totalRows_edit = mysql_num_rows($edit);

mysql_select_db($database_giusi, $giusi);
$query_colours = "SELECT * FROM colours";
$colours = mysql_query($query_colours, $giusi) or die(mysql_error());
$row_colours = mysql_fetch_assoc($colours);
$totalRows_colours = mysql_num_rows($colours);

Code (markup):

 

Just use SELECT * FROM stock to get all of them, you might add an ORDER BY clause to sort them as you wish.

 

But I'm only editing one item determined by stockid.

 

That's the second time I got in trouble for just skimming a post.

OK, so you're using SELECT * FROM colours That should do it for you.

It looks like you problem is that you're getting your list of colors from $row_edit['colours'] instead of a database query. What exactly is the contents of $row_edit['colours'] ?

 

$row_edit['colours'] is that item's colors ie 1,3,7.

$row_colours['colorid'] has all the colors ie 1,2,3,4,5,6,7,8

I just can figure out the PHP loop to make the html form list with all the colors and that item's colors preselected.

 

<?php
$r = mysql_query ('select * from colors where 1 order by color_es');
$colors = array ();
while ($one = myslq_fetch_assoc($r)) {
  $colors[$one['id']] = $one['color_es'];
}

$currently_selected_color_id = 2;

echo '<select name="colours[]" size="12" multiple id="colours">';

foreach ($colors as $id => $name) { 
    $selected = '';
    if ($id == $currently_selected_color_id) {
      $selected = ' selected="selected" ';
    }
    echo '<option value="'.$id.'" '.$selected.'>'.$name.'</option>';
  }

echo '</select>';
?>

Code (markup):

 

Hi Exam, thanks for helping me on this.

That is only calling up one color "verde" which is colourid 2

I suppose from this

$currently_selected_color_id = 2;

I want to list all colors and have that item's colors selected.

you can see where I'm at here:

http://www.giusiferrato.com/admin/list.item.php

just click on alg00120 to see the edit page. that item's colors are 8,7,5,4,3,2,1

PS I changed your code a bit:

<?php
$r = mysql_query ('select * from colours where 1 order by es_colour');
$colors = array ();
while ($one = mysql_fetch_assoc($r)) {
  $colors[$one['id']] = $one['es_colour'];
}

$currently_selected_color_id = 2;

echo '<select name="colours[]" size="12" multiple id="colours">';

foreach ($colors as $id => $name) { 
    $selected = '';
    if ($id == $currently_selected_color_id) {
      $selected = ' selected="selected" ';
    }
    echo '<option value="'.$id.'" '.$selected.'>'.$name.'</option>';
  }

echo '</select>';
?>

Code (markup):

 

In the end this is how I resolved this problem. Thanks to James Ledden @ Ibiza Spotlight for this great function.

You just need to input 2 arrays and a name for the form list. The first array is of the options you want to be selected and the second is all the options.

// allow multiple select, allow double loop to list all possibles 
// arr_active_vars is array of all dynamic ids 

function FORM_pulldown_multiple($arr_active_vars, $name_of_menu, $array_options, $size = 12) {
	$var = "\n";
	$var .= '<select name="' . $name_of_menu . '[]" size="' . 
	$size . '" multiple>';
	$var .= "\n";
	foreach ($array_options as $k => $v)
	{
		if (is_array($arr_active_vars) && in_array($k, $arr_active_vars))
		{
			$var .= '<option value="' . $k  . '" selected>' . $v . '</option>';
			$var .= "\n";
		}
		else
		{
			$var .= '<option value="' . $k . '">' . $v . '</option>';
			$var .= "\n";
		}
	}
	$var .= "\n";
	$var .= '</select>';
	return $var;
}

Code (markup):