Multiple Select in Form and Logging to DB

[DontheCat]

I am creating a website for a Pharma Distributors Association. The Members could be representing various Pharmaceutical Companies. This is the Scenario.
A Member could be a distributor for N number of Parent Companies .
He Could represent All or N Divisions of a Parent Company.
The Parent Companies could be anything from 400 - 600 and will be updated as and when New Companies are found.

Each Parent Company could have N number of Divisions within itself.

I have created these Tables

1.Table Parent_Companies (data entered by Admin)
Columns - Company_ID (Autoincremented), CompanyName

2.Table Divisions
Columns - Company_ID, Division_ID(Autoincremented), DivisionName

The DivisionName are entered thru a form by Admin. This Form has CompanyName values as dropdown, but returns the respective Company_ID to the Table2

3.Table Distributor_Companies
Columns - Distributor_ID, CompanyDivision

==================================================

Now this where I'm stuck

I would like to have the Member Select the Company Divisons he represents from a Form. Since the Companies data would be in 1000s, I dont want to use a Drop-Down.

I presume that a 25 Line List-Box would be a better option from which Companies can be added to another multi-line Text-box. The Options in the ListBox should be concatenated as "Parent Company - Division".

How do I do this? I've found a java script that does the Adding/ Removing between the ListBox and TextBox. But I've not been able to figure out how I could store each "Parent Company - Division" as a record in Table3. (I dont want to store as an array)

I understand that I need to use a Loop here, just cant figure out how to do it.

Also, how would I able to genrate a mail to the Admin with details of all the Parent Company - Divisions when the Member submits the Form?

On the other hand, would it be easier to key in the Divisions as "Parent Company - Division" itself in the 1st place in Table1 and avoid Table2?

Is there a simple solution or am I complicating things?

Thanx for the support

[devinemke]

if you are asking the user to picks items from a list that could be 1000's of entries long then i suggest you break everything up into smaller sections. perhaps the initial page would list of the letters of the alphabet and by clicking on a letter the user is taken to all company divisions starting with that letter. for example, a user clicks on "G" and sees a list of all of the "G's" with a checkbox next to each one. the user checks all of the G's that they want and goes back to the main index to goto another letter. since this form will be filled out over many different steps and page requests i suggest you store everything in a session and then enter everything into the db at the last step.

[DontheCat]

[LEFT]Thanks devinemke...

I thought of that, but the client wants a single-page form for this. The reason being several members are not net-savvy or may be connected at dial-up speeds. A member representing 15 companies (assume with diffferent 1st letters) may get bored after a while.

I've created a page and the code is below. How do I store into the companies selected as separate rows (not as an array) into another table refernced by the Distributor_ID ?

<script language="javascript">

function AddToList()
{
var tCbo = eval("document.frmPlanInfo2.lstAvailableAM");  // dropdown box object
//var mailValue = tCbo[tCbo.selectedIndex].value;  

var tCboS = eval("document.frmPlanInfo2.lstSelectedAM");  // dropdown box object
tCboS.text="asas";				
var k;
k=tCboS.length;
//Check for duplication var isFound;				
for(var i=0;i<tCbo.length;i++)
{
	isFound='0';
	if (tCbo[i].selected == true)
{
for(var j=0;j<tCboS.length;j++)
{
if (tCboS[j].value == tCbo[i].value)
{									isFound='1';		
	}							
}
	if (isFound=='0')
{
									document.frmPlanInfo2.lstSelectedAM.options.length++;
									document.frmPlanInfo2.lstSelectedAM.options[k].text=tCbo[i].text;
									document.frmPlanInfo2.lstSelectedAM.options[k].value=tCbo[i].value;
									k=k+1;
	}	
 	}
		}				
			}					

function RemoveFromList()
{
var tCboS = eval("document.frmPlanInfo2.lstSelectedAM"); 
	for(var i=0;i<tCboS.length;)
	{
	if (tCboS[i].selected == true)
	{
	document.frmPlanInfo2.lstSelectedAM.remove(i);					}
	else
	{
	i++;
	}					
	}
	}
	function ShowSelectedList()
	{
var tCboS = eval("document.frmPlanInfo2.lstSelectedAM");

alert("Lets say if the Table structure is DISTRIBUTOR_NAME And COMPANY_NAME then the values are ....."); 

			for(var i=0;i<tCboS.length;i++)
			{
alert(document.getElementById("txtName").value+ " - "+ tCboS[i].text);									
				}
			}
		</script>
	</HEAD>
	<body>
<form name="frmPlanInfo2">
<table width="400" border="0" cellpadding="0" cellspacing="0">
	<tr>
	<td>
	</td>
	</tr>
	<tr>
	<td height="30">&nbsp;</td>
	</tr>
	<tr>
	<td vAlign="top">
	<table cellPadding="1" style="WIDTH: 400px; BORDER-COLLAPSE: collapse" align="center" border="0">
	<TR height="20">
	<TD class="tdHeadBold" colspan="2">
  </TD>
</TR>
<TR>
<TD class="tdAli" align="right">Distributor Name</TD>
		<TD class="tdAli">
                      <input type=text value="DON" id="txtName" name="txtName">
</TD>
		</TR>
			<tr>
								<td class="tdAli" style="HEIGHT: 143px" colspan="2">
<table class="tdAli" cellSpacing="0" cellPadding="0" width="100%" border="0" ID="Table1">
								<tr vAlign="middle" height="25">
								<td class="tdAli" align="center" bgColor="#dcdcdc">
									Available Companies</td>
									<td class="tdAli" bgColor="#dcdcdc">&nbsp;</td>
									<td class="tdAli" align="center" bgColor="#dcdcdc">
									Selected Companies</td>
									</tr>
									<tr>
									<td class="tdAli" align="center" rowSpan="2"><select name="lstAvailableAM" size="4" multiple id="Select2" class="DispText" style="WIDTH:154px;HEIGHT:104px">
									 <option value="2">ABC Drugs Limited</option>
									  <option value="3">Hardcore Manufacturers ( Homeopathy Division)</option>
									  <option value="5">Never Mind &amp; Co ( Pediatric Division)</option>
									  <option value="4">So What Pharma Ltd</option>
									  <option value="1">XYZ Pharmaceuticals ( IV Division )</option>
									</select></td>
									<td align="center"><input name="btnAdd" id="btnAdd" type="button" class="gridformfield" onclick="AddToList()" value="Add >>">
											</td>
<td class="tdAli" align="center" rowSpan="2"><select name="lstSelectedAM" size="4" multiple id="lstSelectedAM" class="DispText" style="WIDTH:154px;HEIGHT:104px">
												</select></td>
										</tr>
										<tr>
<td align="center"><input name="btnRemove" id="btnRemove" type="button" class="gridformfield" value="<< Remove "Width="70px" onclick="RemoveFromList()">
</td>
</tr>
</table>
</td>
</tr>
<tr>
<TD align="right" valign="top">
<INPUT type="button" id="cmdSave"  size="10" value="Save" name="cmdSave" onClick="ShowSelectedList()">
</TD>
	<td align="right" valign="top" colSpan="2"></td>
</tr>
<tr>
								<td colspan="2">&nbsp;</td>
</tr></table>
		</td>
		</tr>
</table>
</form>

[/LEFT]