Create Form to Update Many-to-Many relationship

[bubbles2020]

I posted before about how to query a many to many relationship and with all of your help I have been able to complete this. Thanks again!

 

My next problem is how to create a form to update this many to many relationship. I am attempting to do this with PHP, so if this is the wrong forum please let me know. Though I do feel that this is more of an SQL issue than a PHP one.

 

My database consists of three tables; Groups, Individuals and Junction. Groups has a GroupID field and that is the primary key. Individuals has IndividualID and this is an autonumber, primary key; this table also has LName, FName, and Email fields. The Junction has an autonumber primary key as well as a field for GroupID and IndividualID.

 

I have successfully updated the Individuals table and Groups table through a form, but I am unsure how to relate them in the junction table.

 

$sql="INSERT INTO Individuals (LName, FName, Email)
VALUES
('$_POST[LName]','$_POST[FName]','$_POST[Email]')";

$sql="INSERT INTO Groups (GroupID)
VALUES
('$_POST[GroupID]')";

 

I have seen many tutorials saying that in the Junction table, rather than having a separate primary key, could have a primary key consisting of the combination of the IndividualID and GroupID fields. Would this solve my problem? If not, what is the benefit to doing this?

 

Additionally, I would ideally like to create in my form, a drop down select list for the groups so that typographical errors would not result in groups such as: Boys and Girls Club, Boy's and Girl's Club, etc. I know how to create this in a form, but not how to relate it so that the options in it are synchronized with my database.

 

If any of this is unclear, please let me know. Thanks in advance for any help you may have to offer!

 

[fenway]

Well, you need to insert the newly linked "pair" of IDs into the "junction" table, as you call it.

[kickstart]

Hi

 

How many groups and how many individuals are you talking about? 10s, 100s, 1000s?

 

If the numbers are reasonable then on screen I would be tempted to display a grid of check boxes. Probably columns of groups and rows of individuals.

 

To get the current values something like:-

 

SELECT a.GroupId, b.IndividualId, c.JunctionId
FROM Groups a,
Individuals b,
LEFT OUTER JOIN Junction c
ON a.GroupId = c.GroupsId
AND b.IndividualId = c.IndividualId
ORDER BY b.IndividualId, a.GroupId

 

That should give you a row for every combination of group and individual. If they already match on the junction table then it will also bring back the Id junction record.

 

That should give you everything to generate the grid and populate the checkboxes. Store the JunctionIds in a hidden field associated with the check boxes and you can see which have changed and delete / add the necessary records when you update.

 

All the best

 

Keith

[bubbles2020]

Thank you for your replies, I apologize that I did not get a chance to respond earlier.

 

Fenway - I know that is what I need to do, but I do not know how to go about doing it.

 

Ideally, I want someone who did not create this to be able to update either Groups or Individuals or both.

 

Kickstart - I am talking about individuals in the high 100's. Currently around 700. I expect it to eventually number in the 1000's. I am not sure the checkbox grid is the ideal solution.

[kickstart]

Kickstart - I am talking about individuals in the high 100's. Currently around 700. I expect it to eventually number in the 1000's. I am not sure the checkbox grid is the ideal solution.

 

Depending on the number of groups then you could just set up some paging for the individuals (even if just crudely based on the first letter of the individuals name). If the groups are in the hundreds as well then I think you are going to struggle to come up with a user friendly front end.

 

All the best

 

Keith

[bubbles2020]

The number of groups will most likely not total more than 100 or so. I am having trouble visualizing your solution, that is, understanding what it will do for me.

[kickstart]

Hi

 

I can't draw for toffee so will have to try and explain it.

 

Assume basic paging with one page per possible first character of the individuals name, so (say) 50 individuals on a page.

 

Have a table. Left hand column contains each name, one name per row. Then one column per group.In each cell of the table you would have a check box which would represent member ship of the group for that column for the individual of that row.

 

When you process the form you can either do it very crudely and delete all records on your junction table for those individuals on the page, and then insert fresh records for those that have the check box entered (and you can work that out crudely from having the check box names containing the indivuduals id and the groups id, something like chkJunction_5_22 ). Better would be to store in hidden fields on the form the original value, group id and indivudual id of each check box, then just loop through them and process any that have changed.

 

This should give you a panel that is very easy for a user to work on to add or remove lots of individuals to groups / groups to individuals.

 

All the best

 

Keith

[fenway]

Thank you for your replies, I apologize that I did not get a chance to respond earlier.

 

Fenway - I know that is what I need to do, but I do not know how to go about doing it.

 

Ideally, I want someone who did not create this to be able to update either Groups or Individuals or both.

Well, you need to get back LAST_INSERT_ID() from each of these inserts, and then insert it into the link table.

[kickstart]

Hi

 

The number of groups will most likely not total more than 100 or so. I am having trouble visualizing your solution, that is, understanding what it will do for me.

 

I have had a quick play and knocked up a very basic idea of it.

 

<?php

$dbhost = 'localhost';
$dbname = 'test';
$dbuser = 'root';
$dbpasswd = '';

$conn = mysql_connect($dbhost,$dbuser,$dbpasswd);

mysql_select_db($dbname,$conn);

if ($_REQUEST['Save'])
{
foreach($_REQUEST as $Field => $Value)
{
	if (substr($Field,0,5) == "prev_")
	{
		$IdArray = explode("_",$Field);
		$NewVal = $_REQUEST['chk_'.$IdArray[1].'_'.$IdArray[2]];
		if ($Value != $NewVal OR ($Value == 0 AND $NewVal == 0))
		{
			if ($NewVal == "")
			{
				$sql = "DELETE FROM Junction WHERE JunctionId = ".$Value;
			}
			else
			{
				$sql = "INSERT INTO Junction (JunctionId, IndividualId, GroupId) VALUES(NULL,".$IdArray[1].",".$IdArray[2].")";
			}
			$void = mysql_query($sql,$conn) or die(mysql_error());
		}
	}
}
}

$sql = "SELECT Deriv1.GroupId, Deriv1.GroupName, Deriv1.IndividualId, Deriv1.Fname, Deriv1.Lname, IFNULL(c.JunctionId,0) JunctionId
FROM (
SELECT * 
FROM Groups a, Individuals b
) AS Deriv1
LEFT OUTER JOIN Junction c ON Deriv1.IndividualId = c.IndividualId
AND Deriv1.GroupId = c.GroupId
ORDER BY Deriv1.IndividualId, Deriv1.GroupId";

$result = mysql_query($sql,$conn) or die(mysql_error());

$LastIndividualId = 0;
$MainTable = "<tr>";
$TitleRowTable = "<tr><td>Individual</td>";
$FirstRow = true;
while ($row = mysql_fetch_array($result))
{
if ($row['IndividualId'] != $LastIndividualId )
{
	if ($LastIndividualId != 0)
	{
		$MainTable .= "</tr>".chr(13).chr(10)."<tr>";
		$FirstRow = false;
	}
	$MainTable .= "<td>".$row['Lname']." ".$row['Fname']."</td>";
	$LastIndividualId = $row['IndividualId'];
}
if ($FirstRow)
{
	$TitleRowTable .= "<td>".$row['GroupName']."</td>";
}
$MainTable .= chr(13).chr(10)."<td><input type='checkbox' id='chk_".$row['IndividualId']."_".$row['GroupId']."' name='chk_".$row['IndividualId']."_".$row['GroupId']."' value='".$row['JunctionId']."' ".(($row['JunctionId'] == 0) ? "" : "checked='checked'")." />".chr(13).chr(10)."<input type='hidden' id='prev_".$row['IndividualId']."_".$row['GroupId']."' name='prev_".$row['IndividualId']."_".$row['GroupId']."' value='".$row['JunctionId']."' /></td>";
}
$TitleRowTable .= "</tr>";
$MainTable .= "</tr>";

echo "<form><table>$TitleRowTable $MainTable </table><input type='submit' name='Save' value='Save' /></form>";

?>

 

This is using tables:-

 

CREATE TABLE `groups` (
  `GroupId` int(11) NOT NULL auto_increment,
  `GroupName` varchar(50) NOT NULL,
  PRIMARY KEY  (`GroupId`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

INSERT INTO `groups` (`GroupId`, `GroupName`) VALUES
(1, 'Knitting Circle'),
(2, 'Brewers Circle');

CREATE TABLE `individuals` (
  `IndividualId` int(11) NOT NULL auto_increment,
  `Lname` varchar(50) NOT NULL,
  `Fname` varchar(50) NOT NULL,
  `Email` varchar(50) NOT NULL,
  PRIMARY KEY  (`IndividualId`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

INSERT INTO `individuals` (`IndividualId`, `Lname`, `Fname`, `Email`) VALUES
(1, 'Smith', 'John', ''),
(2, 'Brown', 'John', ''),
(3, 'Blogs', 'Joe', ''),
(4, 'Jones', 'Jim', '');

-- --------------------------------------------------------

--
-- Table structure for table `junction`
--

CREATE TABLE `junction` (
  `JunctionId` int(11) NOT NULL auto_increment,
  `IndividualId` int(11) NOT NULL,
  `GroupId` int(11) NOT NULL,
  PRIMARY KEY  (`JunctionId`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=17 ;

 

This is very crude but hopefully it gives you the idea of what I meant. Basically presenting a grid with check boxes for the user to see which groups individuals are in, and to update this as well.

 

Hope this helps.

 

All the best

 

Keith

[bubbles2020]

Thank you so much for all of your help!

 

Kickstart - This is not quite what I was looking for. I want the ability to 'create' new individuals rather than add individuals to groups or vice versa. I have no trouble creating the form to 'create' an individual, however, I do not know how to add a section to that form to add that individual to a group. I see how I can use this form you have created to have a 2-step process; 'create' the individual with my form and then use your form to add that individual to a group.

 

Ideally though, I would have something like this:

 

<html>
<body>

<form action="insert.php" method="post">
Last Name: <input type="text" name="LName" />
First Name: <input type="text" name="FName" />
Email: <input type="text" name="Email" />
Group: <select name="mydropdown">
<option value="Group1">Group1</option>
<option value="Group2">Group2</option>
<option value="Group3">Group3</option>
</select>
<input type="submit" />
</form>

</body>
</html>

 

However, the group drop down select would be auto-populated from the groups table in my database. I prefer this solution, because if I use a text field I could have a group called 'Boys and Girls Club' and another called 'Boy's and Girl's Club' due to user error.

 

Is there any way to add an individual to a group using this method?

 

Fenway - I will explore the use of LAST_INSERT_ID() to achieve this.

 

Thanks again!

[bubbles2020]

Ok, so here is my understanding thus far:

 

I will use my form:

 

<html>
<body>

<form action="insert.php" method="post">
Last Name: <input type="text" name="LName" />
First Name: <input type="text" name="FName" />
Email: <input type="text" name="Email" />
Group: <select name="mydropdown">
<option value="Group1">Group1</option>
<option value="Group2">Group2</option>
<option value="Group3">Group3</option>
</select>
<input type="submit" />
</form>

</body>
</html>

 

Except, I need to figure out how to autopopulate the dropdown select.

 

When I submit, it will add the new information to the Individuals table, and if I use LAST_INSERT_ID() I can get the IndividualID (autonumber). I will add this number (LAST_INSERT_ID()) as well as the relevant GroupID (text) to the Junction table and this should be everything I need.

[kickstart]

Hi

 

I will have a play later on if I get a chance.

 

Can in individuals only be a member of one group?

 

All the best

 

Keith

[kickstart]

Hi

 

Quick play:-

 

<?php

$dbhost = 'localhost';
$dbname = 'uniontest';
$dbuser = 'root';
$dbpasswd = '';

$conn = mysql_connect($dbhost,$dbuser,$dbpasswd);

mysql_select_db($dbname,$conn);

if ($_REQUEST['Save'])
{
foreach($_REQUEST as $Field => $Value)
{
	if (substr($Field,0,5) == "prev_")
	{
		$IdArray = explode("_",$Field);
		$NewVal = $_REQUEST['chk_'.$IdArray[1].'_'.$IdArray[2]];
		if ($Value != $NewVal OR ($Value == 0 AND $NewVal == 0))
		{
			if ($NewVal == "")
			{
				$sql = "DELETE FROM Junction WHERE JunctionId = ".$Value;
			}
			else
			{
				$sql = "INSERT INTO Junction (JunctionId, IndividualId, GroupId) VALUES(NULL,".$IdArray[1].",".$IdArray[2].")";
			}
			$void = mysql_query($sql,$conn) or die(mysql_error());
		}
	}
}
if ($_REQUEST['fname'] != "" AND $_REQUEST['lname'] != "" )
{
	$sql = "INSERT INTO Individuals (IndividualId, Lname, Fname, Email) VALUES (NULL,'".$_REQUEST['lname']."','".$_REQUEST['fname']."','".$_REQUEST['email']."')";
	$void = mysql_query($sql,$conn) or die(mysql_error());
	$IndividualId = mysql_insert_id();
	foreach($_REQUEST as $Field => $Value)
	{
		if (substr($Field,0,6) == "chk_0_")
		{
			$IdArray = explode("_",$Field);
			$NewVal = $_REQUEST['chk_'.$IdArray[1].'_'.$IdArray[2]];
			$sql = "INSERT INTO Junction (JunctionId, IndividualId, GroupId) VALUES(NULL,$IndividualId,".$IdArray[2].")";
			$void = mysql_query($sql,$conn) or die(mysql_error());
		}
	}
}
}

$sql = "SELECT Deriv1.GroupId, Deriv1.GroupName, Deriv1.IndividualId, Deriv1.Fname, Deriv1.Lname, IFNULL(c.JunctionId,0) JunctionId
FROM (
SELECT * 
FROM Groups a, Individuals b
) AS Deriv1
LEFT OUTER JOIN Junction c ON Deriv1.IndividualId = c.IndividualId
AND Deriv1.GroupId = c.GroupId
ORDER BY Deriv1.IndividualId, Deriv1.GroupId";

$result = mysql_query($sql,$conn) or die(mysql_error());

$LastIndividualId = 0;
$MainTable = "<tr>";
$TitleRowTable = "<tr><td>Individual</td>";
$NewLine .= "<tr><td><input type='text' id='fname' name='fname' value='' /><br /><input type='text' id='lname' name='lname' value='' /><br /><input type='text' id='email' name='email' value='' />".chr(13).chr(10)."</td>";
$FirstRow = true;
while ($row = mysql_fetch_array($result))
{
if ($row['IndividualId'] != $LastIndividualId )
{
	if ($LastIndividualId != 0)
	{
		$MainTable .= "</tr>".chr(13).chr(10)."<tr>";
		$FirstRow = false;
	}
	$MainTable .= "<td>".$row['Lname']." ".$row['Fname']."</td>";
	$LastIndividualId = $row['IndividualId'];
}
if ($FirstRow)
{
	$TitleRowTable .= "<td>".$row['GroupName']."</td>";
	$NewLine .= "<td><input type='checkbox' id='chk_0_".$row['GroupId']."' name='chk_0_".$row['GroupId']."' value='".$row['JunctionId']."' />".chr(13).chr(10)."</td>";
}
$MainTable .= chr(13).chr(10)."<td><input type='checkbox' id='chk_".$row['IndividualId']."_".$row['GroupId']."' name='chk_".$row['IndividualId']."_".$row['GroupId']."' value='".$row['JunctionId']."' ".(($row['JunctionId'] == 0) ? "" : "checked='checked'")." />".chr(13).chr(10)."<input type='hidden' id='prev_".$row['IndividualId']."_".$row['GroupId']."' name='prev_".$row['IndividualId']."_".$row['GroupId']."' value='".$row['JunctionId']."' /></td>";
}
$TitleRowTable .= "</tr>";
$MainTable .= "</tr>";
$NewLine .= "</tr>";

echo "<form><table>$TitleRowTable $MainTable $NewLine </table><input type='submit' name='Save' value='Save' /></form>";

?>

 

Basic idea again (hence massive opportunity for SQL injection), but one way you could add new individuals and select the groups they are members of.

 

If you wanted you could easily hive off the adding of new individuals onto a new script and process it a similar way.

 

All the best

 

Keith

[bubbles2020]

Thank you so much for all of your help Keith!

 

I don't think the checkbox solution is what I am really looking for. I don't anticipate the need to add/remove large numbers of individuals from groups. I do anticipate the need to add many individuals, and those individuals to 2 or 3 groups.

[kickstart]

Hi

 

Fair enough.

 

I would be tempted to use check boxes on a page to add new individuals (and update exiting ones). Solves the issue you are worried about with mispelt group names. You could use a drop down with multiple selections allowed which would take far less space on screen but be less intuitive for the users.

 

All the best

 

Keith