By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
449,312 Members | 1,877 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 449,312 IT Pros & Developers. It's quick & easy.

Inserting selection box elements into a database

P: 6
Hello,

I realise there are a lot of topics related to this problem but many of what I have found has run cold or unresolved. What I have is an addressbook clone where there are groups which have members. I have this formatted in a way where the groups are in a drop down box. Once a group is selected, the two selection boxes below are refreshed with those who are members or non members of the selected group. The user has the ability to transfer those contacts from the members box to the non members box, and vice versa. PHP, html and MySQL are being used.

I also have three tables:
contacts: containing account details including contactID as its PK
groups: containing the group description including groupID as its PK
groupmembers: containing the previous two PKs as FKs

After searching for resources, I've managed to get a javascript function that allows the transfer of contacts from one selection box to the other

[HTML]function move(nonMemBox, memBox)
{
var arrNonMemBox = new Array();
var arrMemBox = new Array();
var arrLookup = new Array();
var i;

// This for loop gets the length from the destination field
for (i = 0; i < memBox.options.length; i++)
{
arrLookup[memBox.options[i].text] = memBox.options[i].value;
arrMemBox[i] = memBox.options[i].text;
}
var nonMemLength = 0;
var memLength = arrMemBox.length;

// This for loop gets the length of the source field
for(i = 0; i < nonMemBox.options.length; i++)
{

arrLookup[nonMemBox.options[i].text] = nonMemBox.options[i].value;
// If if the item is selected and not empty
if (nonMemBox.options[i].selected && nonMemBox.options[i].value != "")
{
arrMemBox[memLength] = nonMemBox.options[i].text;
memLength++;
}
else
{
arrNonMemBox[nonMemLength] = nonMemBox.options[i].text;
nonMemLength++;
}
}

// This alphabetically sorts the array of the items
//arrFbox.sort();
//arrTbox.sort();
nonMemBox.length = 0;
memBox.length = 0;
var c;

// now transfer the items stored in the arrFbox[] array
for(c = 0; c < arrNonMemBox.length; c++)
{
var no = new Option();
no.value = arrLookup[arrNonMemBox[c]];
no.text = arrNonMemBox[c];
nonMemBox[c] = no;
}

// now send all the items to the destination field
for(c = 0; c < arrMemBox.length; c++)
{
var no = new Option();
no.value = arrLookup[arrMemBox[c]];
no.text = arrMemBox[c];
memBox[c] = no;
}
}

function addMe()
{
theValues = document.combo_box.member;

for(i = 0; i < theValues.length; i++)
{
alert(theValues[i].value);
if(document.combo_box.theNumbers.value == "" )
{
document.combo_box.theNumbers.value = theValues[i].value;
}
else
{
document.combo_box.theNumbers.value += "," + theValues[i].value;
}
}
return true;
}[/HTML]

The function responsible for refreshing the page with the drop down box are as follows:

[HTML]function reload(form)
{
var val=form.Group.options[memberAssignmentForm.Group.options.selectedIndex].value;
self.location='groupContacts.php?Group=' + val ;
}[/HTML]

The above function uses $Group = $_GET['Group'] to get the selected groupID. That is then used for the following code

[HTML]<form name="memberAssignmentForm" method="post" action="groupContacts.php" onsubmit="return addMe();">
<input id="theNumbers" name="theNumbers" type="hidden" />
<table>
<tr>
<td><b>Group: </b><select name="Group" onchange="return reload(this.form);">
<option value="select">Select one</option>
<?
// Retrieves and displays groups from db
$query="select * from groups order by Name";
$result=$db->query($query);
$num_results=$result->num_rows;
for($i=0; $i<$num_results; $i++)
{
$row=$result->fetch_assoc();
echo '<option value=';
echo $row['groupID'];
echo '>';
echo $row['Name'];
echo '</option>';
}
$result->free();
?>
</select></td>
</tr><tr></tr>
<tr>
<td align="center"><b>Non-members</b></td>
<td><p>&nbsp;</p></td>
<td align="center"><b>Members</b></td>
</tr>
<tr>
<td><select name="nonMember" size="10" multiple style="width: 150px;" ondblclick="move(this.form.nonMember,this.form.mem ber)">
<?
// Retrieves contact names that belong to selected group and displays in the right selection box
$query = "select * from contacts where contactID not in (select contactID from groupcontact where groupID = '".$Group."')";
$result=$db->query($query);
$num_results=$result->num_rows;
for($i=0; $i<$num_results; $i++)
{
$row=$result->fetch_assoc();
echo '<option value=';
echo $row['contactID'];
echo '>';
echo $row['Surname'],', ', $row['Firstname'];
echo '</option>';
}
$result->free();
?>
</select></td>
<td><input type="button" name="Disable" value="&gt; " style="width: 20px;" onClick="move(this.form.nonMember,this.form.member )"><br>
<br>
<input type="button" name="Enable" value="&lt;" style="width: 20px;" onClick="move(this.form.member,this.form.nonMember )"><br></td>
<br>
<td><select name="member" size="10" multiple style="width: 150px;" ondblclick="move(this.form.member,this.form.nonMem ber)">
<?php
// Retrieves contact names that are don't belong to selected group and displays in the left selection box
$query = "select * from contacts where contactID in (select contactID from groupcontact where groupID = '".$Group."')";
$result=$db->query($query);
$num_results=$result->num_rows;
for($i=0; $i<$num_results; $i++)
{
$row=$result->fetch_assoc();
echo '<option value=';
echo $row['contactID'];
echo '>';
echo $row['Surname'], ', ', $row['Firstname'];
echo '</option>';
}
$result->free();
?>
</select></td>
</tr>
<tr>
<td><input type="submit" name="submit" value="Save"></td>
</tr>
</table>
</form>
</body>
</html>[/HTML]

Now what I am trying to resolve is to submit the form where the contacts in their respective boxes and insert them into the database - specifically the groupmembers table, which would end up looking like...

groupID contactID
12 44
12 45
12 46
13 22

Another problem I'm having difficulty understanding is that when I select a group from the drop down box, it obviously refreshes. However, once it does the correct contacts are displayed but the drop down box reverts back to its original state eg. Select one

What am I missing that ensures the selected group is displayed once refreshed?

I have tried working with arrays on the selection box inputs but it breaks the move function. An answer to these two questions would be greatly appreciated. Apologies for the length of this question but I figured giving this much information would provide a better answer. Of course I'll be happy to clarify anything that is vague.

Thank you
Mar 11 '08 #1
Share this Question
Share on Google+
4 Replies


dlite922
Expert 100+
P: 1,584
to quickly select the value that was selected before the page refresh, simple follow this logic:

[PHP]
for ($i = 0; $i < count($someArray); $i++)
{
if($someArray[$i] == $someID)
echo "<option value=$someArray[$i] selected='selected'>$someArray[$i]";
else
echo "<option value=$someArray[$i]>$someArray[$i]";
}
[/PHP]

I'll see if i have time to cover your other issues, anybody else feel free to jump in.
Mar 11 '08 #2

P: 6
[PHP]
for ($i = 0; $i < count($someArray); $i++)
{
if($someArray[$i] == $someID)
echo "<option value=$someArray[$i] selected='selected'>$someArray[$i]";
else
echo "<option value=$someArray[$i]>$someArray[$i]";
}
[/PHP]
Thank you for the response. Regarding your suggestion. I am not using arrays to access the drop down box whatsoever so I'm confused as to how I would apply that logic. It's just querying the database to retrieve the results.

[PHP]$query="select * from groups order by Name";
$result=$db->query($query);
$num_results=$result->num_rows;
for($i=0; $i<$num_results; $i++)
{
$row=$result->fetch_assoc();
echo '<option value=';
echo $row['groupID'];
echo '>';
echo $row['Name'];
echo '</option>';
}[/PHP]
Mar 12 '08 #3

P: 6
I have solved my refresh problem. It was so simple, it's criminal. I just needed to echo the inputs as selected in a loop. Thanks dlite922 for kickstarting the idea.
[PHP]
$query="select * from groups order by Name";
$result=$db->query($query);
$num_results=$result->num_rows;
for($i=0; $i<$num_results; $i++)
{
$row=$result->fetch_assoc();
if($row['groupID'] == $Group)
{
echo '<option selected value=';
echo $row['groupID'];
echo '>';
echo $row['Name'];
echo '</option>';
}
else
{
echo '<option value=';
echo $row['groupID'];
echo '>';
echo $row['Name'];
echo '</option>';
}
}[/PHP]

One problem solved. Another one to go.
Mar 13 '08 #4

P: 6
No one has a solution?
Mar 24 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.