one to many db question

[condoug]

Hi all

I am keen to find out how others approach this.

My example. I have 2 tables. People and Hobbies.

In People I have unique records of people (name, dob, telephone etc) and these people records can have many hobbies which will be updatable through a profile page by the users themselves. They do this via a multiple select box and click save.

Now, at this point in the development, how would you store the records they have chosen in the Hobbies table but more importantly, how do you get rid of the ones they have not chosen if they had chosen them in the past?

Do you have to flush the table where the 'people.id.' exists in Hobbies before inserting the new choices?

Any thoughts would be greatly appreciated.
D

[bpat1434]

The way I do it is the following:

1.) Select all rows in the Hobbies table which belong to the specific user
2.) Create an array of the items
3.) Create an array of the posted items
4.) Use [man]array_intersect/man to get the similarities between the arrays.
5.) From the main database array, remove the "duplicates" which are going to stay.
6.) Remove the "duplicates" which are already in the posted array.
7.) Run your queries to remove and/or add items as necessary.

Here's an example of some code:

				// 1.) Remove duplicates from $orgs array (if there are any)
				$orgs = array_unique($orgs);

			// 2.) Take out their primary orgID as defined in $org_id above...
			if(($key = array_search($org_id, $orgs)) !== false)
			{
				unset($orgs[$key]);
			}

			// 3.) Get their curent orgs (if any) from the DB
			$db_orgs = array();
			$query  = "SELECT `id`, `hobby` FROM `table` WHERE `user_id`=" . (int)$userID;
			$result = mysql_query($query) or db_error($query, __FILE__, __LINE__);
			while($row = mysql_fetch_array($result))
			{
				$db_orgs[$row['id']] = $row['org_id'];
			}
			$free = mysql_free_result($result);

			// 4.) Which organizations are duplicates between DB and $orgs:
			$dups = array_intersect($db_orgs, $orgs);
			if(is_array($dups) && count($dups) > 0)
			{
				foreach($dups as $id=>$val)
				{
					$_orgs_id = array_search($val, $orgs);
					unset($db_orgs[$id]);
					unset($orgs[$_orgs_id]);
				}
			}
			unset($dups);

			// 5.) Remove those left in $db_orgs from DB
			if(!empty($db_orgs))
			{
				$keys = array_keys($db_orgs);
				$query = "DELETE FROM `table` WHERE `id` IN (" . implode(',', $keys) . ")";
				$result = mysql_query($query) or db_error($query, __FILE__, __LINE__);
				if(mysql_affected_rows() != count($db_orgs)) { return false; }
				$free = mysql_free_result($result);
			}

			// 6.) Add those left in $orgs
			if(!empty($orgs))
			{
				$query = "INSERT INTO `organization_admin` (`user_id`, `org_id`) VALUES ";
				foreach($orgs as $org)
				{
					$query .= "\n(" . (int)$userID . ", " . (int)$org . "),";
				}
				$query = substr($query, 0 ,-1);
				$result = mysql_query($query) or db_error($query, __FILE__, __LINE__);
				if(mysql_affected_rows() != count($orgs)) { return false;}
			}

Hope that helps.

[condoug]

Thanks bpat1434, that's very interesting and I will look into that method rather than flushing everything for the user and re-inserting.