Multiple Select List Menu

[karenn1]

Hey everyone,

 

I have a page where users can list their business. I have a multi select list where they can choose multiple categories. This is the code for the dropdown:

 

<?php	
$sql_company = "SELECT * FROM companies WHERE id = ".$_REQUEST['id'];
$result_company = db_query($sql_company);	
$rs_company = mysql_fetch_array($result_company, MYSQL_ASSOC);

$sql_categories = "SELECT id, category FROM categories";
$result_categories = db_query($sql_categories, MYSQL_ASSOC);
?>

<select name="category[]" id="category[]" size="5" multiple="multiple">
<?php
while ($rs_categories = mysql_fetch_array($result_categories))	{
print "<option value=\"".$rs_categories["category"]."\">".ucwords($rs_categories["category"])."</option>";
}
?>
</select>

 

And this is the code to save it into the database:

 

//Multi select for category - store multiple values as array
$array = $_POST["category"];
$counter = 0;
$string_to_db = "";
$separator = "";

foreach($array as $item){
  if($counter <> 0) {
    $separator = ",";
  }
  
  $string_to_db .= "$separator$item";
  ++$counter;
  }

 

I have a INSERT statement that then saves "$string_to_db" into my table. So if the person selects Events and Catering, it gets saved as:

 

Events,Catering

 

My issue now, is when the user updates their page and they want to change categories, how do I draw this from the database. I have this at the moment:

 

<select name="category[]" id="category[]" size="5" multiple="multiple">
<?php
while ($rs_categories = mysql_fetch_array($result_categories))	{
   $selected = (isset($rs_company['category']) == $rs_categories['category']) ? 'selected' : '';
   print "<option value='{$rs_categories['category']}' $selected>".ucwords($rs_categories["category"])."</option>";
}
?>
</select>

 

This ends up selecting everything in the list and not those specific to this company ID.

 

Can someone please help?

 

 

 

 

Thanks!

 

Karen

[karenn1]

Can anyone please help?

 

 

 

Thanks in advance!

 

Karen

[WolfRage]

How are these two tables tied together? What commonalities do they have? That is how you will do your select statement from the database. If you do not ahve a company ID or Category ID that is ties one table to the other then it would be tough to join the two tables together and then generrate a result set that only has the results you are looking for.

[Axeia]

foreach($array as $item){
  if($counter <> 0) {
    $separator = ",";
  }

  $string_to_db .= "$separator$item";
  ++$counter;
  }

Think you're reinventing the wheel there, if you're simply creating a comma seperated string out of an array then use this instead:

$string_to_db = implode( ',', $array );

 

Your problem is a bit hard to tackle since.. this isn't exactly an example of how to keep things readable:

$selected = (isset($rs_company['category']) == $rs_categories['category']) ? 'selected' : '';

If isset( $rs_company['category'] ) will return true or false.

Then if that boolean equals to the value of $rs_categories['category'] you get 'selected' as a string in $selected.

 

Since $rs_categories is a mysql result it can't be a boolean, but it can be a 0 or 1 which respectively can be compared to the booleans false and true.

0 = false.

1 = true.

 

So going by that logic every $rs_categories['category'] in your database apparently holds the value 1?