Need help with updating stored SQL array

[whodigs]

I have run into a problem with a script I created awhile back. The initial script was to set up a preferred employer section for a website.

The original script worked by having the user define an employer and then associating them with a property id.

I store the relationships in an array within the 'employer' table and used existing property id's to link them to communities. Originally the system called for only the employers to define the communities and now both need control. I am unsure how to execute the changes.

Presently I am able to display if an employer should be checked or not when viewed by a specific community by using the following code:

<input type="checkbox" name="prop[]" value="<?php echo ($row->EmployID); ?>"   <?php 
  $each = array("Test"=>explode(",", $row->relationships) );
  if(in_array($PropID, $each['Test'])){
echo " checked=\"checked\"";}
else{ echo "";}
?>><?php echo ($row->name); ?>

The 'relationship' field currently stores data as '123, 30, 46, 77' with each number being a unique property id.

I can not think of a way to use the existing field to also allow communities to choose employers. Is there a way to perform an SQL query that would scan the 'relationships' column of the 'employers' table and make changes as needed based on what employers are checked/not checked?

I assume I need some type of loop to check for the PropID in each relationship and either add or remove based on the relationship defined in the form.

[johanafm]

Database normalization. Remove the property field from the employer table. Create a property table for the employer. I assume you have one table called "employer" and one called "community", so this new table will in essence be a link table to allow many-to-many relationships. As such, perhaps call it employer_community. Have it contain two fields, employer_id and community_id, primary key(employer_id, community_id).

If I understood you correctly, communities and employers can set their preferences regardless of each other. If this is the case, you'd need another table called community_employer looking identical to the above. The only difference would be that employers decide what goes into one table, communities decide what goes into the other.

Then just LEFT JOIN on the appropriate id. Check this id: if null, don't check. otherwise checked="checked"

[whodigs]

I knew I was over thinking this.

Thanks for the reply