Insert Delete Data From Multiple List Selections?

[wilhud]

Hello,

I was wondering how I would go about starting to do this. I have 3 tables. One contains companies, one contains categories and the last one contains the ids that relate the category ids to the companies ids. What I want to do is create a form that either adds or deletes from this third table based on which categories are selected in a menu list. I'll try and show it here...

Companies
Company_ID | Company_Name
1 | Bob's Towing
2 | Jenni's Deli

Categories
Category_ID | Category_Name
1 | Auto Repair
2 | Services
3 | Food Services

Comp_Cats
Category_id | Company_ID
1 | 1
2 | 1
3 | 2

So I'd like to in a select menu choose multiple categories and have it so when I submit the form, it adds or deletes the relationships accordingly.

I'm assuming I'll have to looop through the results and insert the data into the table (altough I'm not sure how to do that). Plus I'm not sure how to delete them when they are unnecessary anymore.

Any help would be greatly appreciated. I just need some direction, I can usually catch on pretty quick once I have it.

Thanks!
Wil

[wilhud]

Thinking about it just now, would I first delete all items where the company_id = the company I'm editing? Then I could go in and insert the new relationships. Seems like that would work. I'm just not sure how to loop through the results and insert them into the database.

[wilhud]

Here's what I've done so far, not sure if this is gonna work. Seems like it should.

if ($id == 0) {
		$add_query = "INSERT INTO companies (company_name) VALUES ('$company')";
		$id = mysql_insert_id();
		$result = mysql_query($add_query);
		foreach($cat_array as $val) {
			$add_cat_query = "INSERT INTO comp_cats (company_id,category_id) VALUES ($id,$val)";
			$result = mysql_query($add_cat_query);
		}
		$status = "New Company added!";
	} else {
		$upd_query = "UPDATE companies SET company_name='$company' WHERE company_id=$id";
		$result = mysql_query($upd_query);
		$del_cat_query = "DELETE FROM comp_cats WHERE company_id = $id";
		$result = mysql_query($del_cat_query);
		foreach ($cat_array as $val) {
			$add_cat_query = "INSERT INTO comp_cats (company_id,category_id) VALUES ($id,$val)";
			$result = mysql_query($add_cat_query);
		}
		$status = "Company Update Successful!";
	}

However, when I submit the form, it doesn't submit my categories in a comma delimited list. Here's what I've got...

<select name=category size=5 multiple id=category></select>

I select multiple categories in the list and rather than submitting 1,4,7 the form submits 7, whatever the last id was. I thought multiple made it submit all the values. But I think if I can get that to work, then I'm almost done.

Anybody know?

Thanks,
Wil

[wilhud]

Aha! I've fount that in order to properly format your select menu form variable, you must include [] after the name. So mine would looke like this...

<select name=category[] size=5 multiple></select>

Now to test it out.

[wilhud]

Everything appears to be working, here's my code...

if ($id == 0) {
		$add_query = "INSERT INTO companies (company_name) VALUES ('$company')";
		$result = mysql_query($add_query);
		$id = mysql_insert_id();
		foreach($category as $val) {
			$add_cat_query = "INSERT INTO comp_cats (company_id,category_id) VALUES ($id,$val)";
			$result = mysql_query($add_cat_query);
		}
		$status = "New Company added!";
		Header("Location:companies.php?status=$status");
	} else {
		$upd_query = "UPDATE companies SET company_name='$company' WHERE company_id=$id";
		$result = mysql_query($upd_query);
		$del_cat_query = "DELETE FROM comp_cats WHERE company_id = $id";
		$result = mysql_query($del_cat_query);
		foreach ($category as $val) {
			$add_cat_query = "INSERT INTO comp_cats (company_id,category_id) VALUES ($id,$val)";
			$result = mysql_query($add_cat_query);
		}
		$status = "Company Update Successful!";
		Header("Location:companies.php?status=$status");
	}

I had some mistakes before, but I think this is good. If anyone knows of a better way to pull this off, please let me know.

Thanks,
Wil