populating linked db table from checkbox list

[qloops]

I have a linked table (MusicCategory) that contains 1 record for each category/music relationship

music
music_id
music_name

category
category_id
category_name

MusicCategory <–Link table
music_id
category_id

Using a leftjoin, I have a checkbox list displaying like so, with the checkbox appearing checked for each category item found for a particular music item in MusicCategory table:

[php]
$query_categories = ‘SELECT category.cat_id,category.cat_name,MusicCategory.music_id as checked FROM category left join MusicCategory on category.cat_id = MusicCategory.category_id’
. " AND MusicCategory.music_id = ‘$m_id’"
. ’ LIMIT 0, 30 ';
$categories = mysql_query($query_categories, $tempscoremusic_dev) or die(mysql_error());
$row_categories = mysql_fetch_assoc($categories);
$totalRows_categories = mysql_num_rows($categories);
[/php]

world & ethnic

romance

cartoon & comedy

ambient textures

corporate

action adventure

suspense & tension

horror thriller

dramatic thematic

light

weird & quirky <br
era

chase & pulse

solitary

modern

I am getting stuck trying to update the MusicCategory link table with the user’s checkbox selection from the HTML form. Any suggestions?

[phphelp]

In your html code, music_id seem to be static. This means that you’re updating categories for this particular music_id, right? If so, this php code below should do the work:
[php]<?php
if(isset($_POST[‘music_id’])){
// delete old category associations for music_id
mysql_query(“delete from MusicCategory where music_id=’”.mysql_real_escape_string($_POST[‘music_id’])."’");
// adding new category associations for music_id
if(is_array($_POST[‘category’])){
$insert = “”;
foreach($_POST[‘category’] as $cat){
$insert.= ($insert==""?"":",")."(’".mysql_real_escape_string($_POST[‘music_id’])."’,’".mysql_real_escape_string($cat)."’)";
}

if($insert!=""){
  mysql_query("insert into MusicCategory (music_id,category_id) values ".$insert);
}

}
}
?>[/php]

[jwebhost]

Why don’t you populate from the database??

That would give that little bit more control and would only need update from the database.

$sql=mysql_query("QUERY");
echo "<select name=\"category\" id=\"tcategory\" type=\"text\">";
echo "<option>Select Categorty:</option>";
while($row = mysql_fetch_array($sql))
  {  
  echo "<option value=\"".$row['ID']."\">".$row['Title']."</option>";
  }
echo "</select>";