Solved

One HTML dropdown selecting a value in another without filtering

Posted on 2013-01-26
8
221 Views
Last Modified: 2013-01-28
Hi,

I have some database tables laid out as below:

TRANSACTIONS:
ID.........TransactionName.........ManagerID
1...........CS01..............................1
2...........CS02..............................2
3...........CS03..............................2

USERS:
ID.........Username
1...........Tom
2...........Jim


On my webpage, I have a html SELECT dropdown field called "Transactions". Its values are set by:    SELECT * FROM TRANSACTIONS

On my webpage, I have a second html SELECT dropdown field called "TransactionOwners". Its values are set by:    SELECT * FROM USERS

What I'm trying to do is set it up in such a way that when you update the "Transactions" field, the corresponding ManagerID is looked up against the ID column in the "TranscationOwners" field and it marks this record as the selected one. I need to do this  without a page refresh.

This is a bit different from the usual "one dropdown box updates another" in that it mustn't filter the second dropdown, only highlight the corresponding record.

EXPECTED RESULTS:
Choose CS01, Tom's name is selected, Jim is shown under Tom on the list
Choose CS02, Jim's name is selected, Tom is shown above Jim on the list
Choose CS03, Jim's name is selected, Tom is shown above Jim on the list

Can someone please provide some inspiration for this?

Thanks in advance
0
Comment
Question by:kbit
  • 4
  • 4
8 Comments
 
LVL 82

Expert Comment

by:hielo
Comment Utility
On my webpage, I have a second html SELECT dropdown field called "TransactionOwners". Its values are set by:    SELECT * FROM USERS

Open in new window

You should really be using:
SELECT u.* FROM USERS u INNER JOIN Transactions t ON t.ManagerID=u.id

Otherwise, the second list could potentially end  up listing users that do not exist in transactions.  As an example, if your tables were as follows:

TRANSACTIONS:
ID.........TransactionName.........ManagerID
1...........CS01..............................1
2...........CS02..............................2
3...........CS03..............................2

USERS:
ID.........Username
1...........Tom
2...........Jim
3...........Mary

Then you wouldn't want Mary on the second dropdown list.

What I'm trying to do is set it up in such a way that when you update the "Transactions" field, 

Open in new window

How are you accomplishing that now?  To avoid the refresh you will need to use ajax. It would help if you provide link to your page.
0
 

Author Comment

by:kbit
Comment Utility
Thanks for your reply.

Ideally even if Mary is not on the transaction table, she should be available in the second list.

Here is my code which builds the second list based on the first (which not what I need). Maybe this script could be modified to select instead of filter?

<?php
session_start();
//connection parameters for database go here
?>
<p>

<HEAD>

<TITLE>

</TITLE>


<script>
var listOpt = new Array();
<?php
	//build a list of transactions
	$query = "SELECT ID,TransactionName,ManagerID FROM transactions ORDER BY TransactionName ASC";
	$result = mssql_query($query) or die(" Parent Query failed: " . mssql_error());
	while( $row = mssql_fetch_row($result) )
		{
			//for each transaction, get the users name
			$query2 = "SELECT ID,Name FROM users WHERE ID = '".$row[2]."' ORDER BY Name";

			$result2 = mssql_query($query2);
			echo "listOpt[\"".$row[0]."\"] = [";
			while( $row1 = mssql_fetch_row($result2) )
				{
				echo "['".$row1[0]."','".$row1[0]."'],";
				}
			echo "\"\"];\n";
			echo "listOpt[\"".$row[0]."\"].length--;\n";
			mssql_free_result($result2);
		}
	 mssql_free_result($result);

?>
function loadList(theSel)
{
	 opt = theSel.form.TransactionOwners.options;
	 opt.length = 0;
	 if (listOpt[theSel.value])
	 {
		  optList = listOpt[theSel.value];
		  setSelected = false;
		  for(i=0;i<optList.length;i++)
		  {
			   opt[opt.length] = new Option(optList[i][1], optList[i][0]);
		  }
		  if (setSelected)
		  { opt[tmp].selected = true;
		  }
	 }
}
</script>


</HEAD>


<BODY onLoad="loadList(document.test.Transactions)">
<FORM NAME="test">

<SELECT NAME="Transactions" onChange="loadList(this);">
<OPTION SELECTED VALUE=""></OPTION>
	<?php

	 $query1="SELECT ID,TransactionName,ManagerID FROM transactions ORDER BY TransactionName ASC";
	  $result = mssql_query ( $query1, $con);
	  while( $row2 = mssql_fetch_row($result) )
	  {
		print "<OPTION VALUE=\"$row2[0]\" ";
		print " >" . $row2[0] . ' (' . $row2[1] . ')' . "</option>";
	  }

	?>
</SELECT>


<SELECT NAME="TransactionOwners">
</SELECT>

</FORM>
</BODY>
</HTML>

Open in new window

0
 
LVL 82

Accepted Solution

by:
hielo earned 500 total points
Comment Utility
I believe line 29 of your previous post should be:
echo "['".$row1[0]."','".str_replace("'","\\'",$row1[1])."'],";

Open in new window


>>Ideally even if Mary is not on the transaction table, she should be available in the second list.
Huh?  Initially you wrote:

EXPECTED RESULTS:
Choose CS01, Tom's name is selected, Jim is shown under Tom on the list
Choose CS02, Jim's name is selected, Tom is shown above Jim on the list
Choose CS03, Jim's name is selected, Tom is shown above Jim on the list

But if you have:
TRANSACTIONS:
ID.........TransactionName.........ManagerID
1...........CS01..............................1
2...........CS02..............................2
3...........CS03..............................2

USERS:
ID.........Username
1...........Tom
2...........Jim
3...........Mary

(Notice that there is no 3 under ManagerID), what would you choose to get Mary in the second list? Or am I misunderstanding something?

Save the code below as "hielo.php" and try it:
<?php
session_start();
//connection parameters for database go here
?>
<html>
	<HEAD>
		<TITLE></TITLE>


		<script type="text/javascript">
<?php
	//build a list of transactions
	$query = "SELECT t.ID, t.TransactionName, t.ManagerID, u.UserName FROM transactions t INNER JOIN Users u ON t.ManagerID=u.ID ORDER BY t.TransactionName ASC";
	$result = mssql_query($query) or die(" Parent Query failed: " . mssql_error());
	$data=array();
	while( $row = fetch_assoc($result) )
	{
		$id=$row['ID'];
		unset($row['ID']);
		$data[$id]=$row;
	}
	echo 'var transactions='.json_encode($data).';';
	mssql_free_result($result);
	unset($data);

	$result=mssql_query('SELECT ID, UserName FROM Users');
	$data=array();
	while( $row = fetch_assoc($result) )
	{
		$id=$row['ID'];
		unset($row['ID']);
		$data[$id]=$row;
	}
	echo 'var users='.json_encode($data).';';
	mssql_free_result($result);
	unset($data);
?>
//the above should generate something similar to:
//var transactions={	"1":{"TransactionName":"CS01","ManagerID":1},"2":{"TransactionName":"CS02","ManagerID":2},"3":{"TransactionName":"CS03","ManagerID":2}};
//var users={	"1":{"UserName":"Tom"},"2":{"UserName":"Jim"},"3":{"UserName":"Mary"}};
            function loadList(theSel, selection)
            {
            	theSel.options.length=0;
            	theSel.options[0]=new Option('','');
            	var counter=1;
            	if('Transactions'==theSel.name)
            	{
					
            		for( var k in transactions )
            		{
            			theSel.options[counter++]=new Option( k+' ('+transactions[k].TransactionName+')', k, selection==transactions[k].ManagerID, selection==transactions[k].ManagerID);
            		}
					if( 'undefined'==typeof(selection))
						loadList(document.test.TransactionOwners);
            	}
				else if('TransactionOwners'==theSel.name)
            	{
            		for( var k in users )
            		{
						var selected=(typeof(transactions[selection])!='undefined' && transactions[selection].ManagerID==k);
            			theSel.options[counter++]=new Option( users[k].UserName, k, selected, selected);
            		}
            	}
			return true;
            }


            window.onload=function(){
            	loadList(document.test.Transactions)
            };
		</script>
	</HEAD>
	<BODY>
		<FORM NAME="test">
			<SELECT NAME="Transactions" onchange="loadList(document.test.TransactionOwners,this.value);"></SELECT>
			<SELECT NAME="TransactionOwners" onchange="loadList(document.test.Transactions,this.value);"></SELECT>
		</FORM>
	</BODY>
</HTML>

Open in new window

0
 

Author Comment

by:kbit
Comment Utility
Thanks for your efforts so far.

Yes the following situation could arise when a person is not associated with a transaction (or the primary person is out sick) and another person must be chosen:

TRANSACTIONS:
ID.........TransactionName.........ManagerID
1...........CS01..............................1
2...........CS02..............................2
3...........CS03..............................2

USERS:
ID.........Username
1...........Tom
2...........Jim
3...........Mary

Your code gave an error as follows:
Call to undefined function fetch_assoc()

Years ago, through EE, I did get some code which allows one dropdown to filter a second dropdown which in turn selects the corresponding person (if found) on a list of users in a third dropdown. I've used this code many times over the years but I can't adapt it for my needs. I've extracted the relevant parts of it and shown them below in case it's of help.

<?php

// Get a list of "general objects" from the database
$objectsQuery = "SELECT roles.ID, Area, Role, ManagerID FROM roles INNER JOIN areas ON roles.AreaID=areas.ID WHERE roles.Status='Active' ORDER BY roles.ID ASC";
$objectsResult = mssql_query($objectsQuery) or die("Unable to retrieve objects. mssql said: " . mssql_error());

// Get a list of Area data
$areaQuery = "SELECT DISTINCT Area FROM roles INNER JOIN areas ON roles.AreaID=areas.ID WHERE roles.Status='Active'";
$areaResult = mssql_query($areaQuery) or die("Unable to retrieve Area data. mssql said: " . mssql_error());

// Get a list of Users
$usersQuery = "SELECT ID,Name FROM users WHERE Status = 'Active' ORDER BY Name";
$usersResult = mssql_query($usersQuery) or die("Unable to retrieve Users. mssql said: " . mssql_error());
?>



<script type="text/javascript" language="Javascript">
var generalObjects = new Array();
<?php
$counter = 1;
while($row = mssql_fetch_object($objectsResult))
{
	?>generalObjects[<?php echo $counter; ?>] = new generalObject(<?php echo $row->ID; ?>, '<?php echo $row->Area; ?>', '<?php echo $row->Role; ?>', '<?php echo $row->ManagerID; ?>');
<?php
	$counter++;
}
?>

var area = new Array();
<?php
$counter = 1;
while ($row = mssql_fetch_object($areaResult))
{
	?>area[<?php echo $counter; ?>] = '<?php echo $row->Area; ?>';
<?php
	$counter++;
}
?>

var Users = new Array();
<?php
$counter = 1;
while  ($row = mssql_fetch_object($usersResult))
{
	?>Users[<?php echo $counter; ?>] = new generalUsers(<?php echo $row->ID; ?>, '<?php echo addslashes($row->Name); ?>');
<?php
	$counter++;
}
?>


function populateArea()
{
	// Define the output fields
	AreaSelect = document.getElementById("Area");
	RoleSelect = document.getElementById("Role");
	ManagerIDSelect = document.getElementById("RoleOwnerID");
	AreaSelect.options[0] = new Option('', 0);

	for (var i=1; i<area.length; i++)
	{
		AreaSelect.options[i] = new Option(area[i], area[i]);
	}

	populateRoleOwnerID();
}

function populateRoleOwnerID()
{
	ManagerIDSelect.value = '';
	var defaultOption = document.createElement('option');
	defaultOption.text = '';
	defaultOption.value = 0;

	try
	{
		ManagerIDSelect.add(defaultOption, null);
	}
	catch (ex)
	{
		ManagerIDSelect.add(defaultOption); // For IE
	}

	for (var i=1; i<Users.length; i++)
	{
		var newOption = document.createElement('option');
		newOption.text = Users[i].name;
		newOption.value = Users[i].id;

		try
		{
			ManagerIDSelect.add(newOption, null);
		}
		catch (ex)
		{
			ManagerIDSelect.add(newOption);
		}
	}
}

function populateRole()
{
	if (AreaSelect.selectedIndex == 0)
	{
		//RoleSelect.disabled=true;
		//ManagerIDSelect.disabled=true;
		return false;
	}
	//ManagerIDSelect.disabled = true;
	ManagerIDSelect.value = '';
	//RoleSelect.disabled=false;

	// Clear all previous options
	if (RoleSelect.length)
	{
		while (RoleSelect.length)
		{
			RoleSelect.remove(0);
		}
	}

	RoleSelect.options[0] = new Option('', 0);

	for (var j=1; j<generalObjects.length; j++)
	{
		if (generalObjects[j].area == AreaSelect.value)
		{
			var newOption = document.createElement('option');
			newOption.text = generalObjects[j].role;
			newOption.value = generalObjects[j].role;
			try
			{
				RoleSelect.add(newOption, null);  // Standards compliant method, but doesnt work in some versions of IE
			}
			catch (ex)
			{
				RoleSelect.add(newOption);
			}
		}
	}
	RoleSelect.selectedIndex=0;
}

function selectManagerID()
{
	//ManagerIDSelect.disabled = false;

	var areaValue = AreaSelect.value;
	var roleValue = RoleSelect.value;

	var ManagerIDsIndex = "";
	for (var i=1; i<generalObjects.length; i++)
	{
		if ((generalObjects[i].area == areaValue) && (generalObjects[i].role == roleValue))
		{
			ManagerIDsIndex = generalObjects[i].managerid;
		}
	}

	if (ManagerIDsIndex != "")
	{
		ManagerIDSelect.value = ManagerIDsIndex;
	}
	else
	{
		ManagerIDSelect.selectedIndex = 0;
	}
}

function area(inputId, inputTitle)
{
	// Define an object called "area" with two properties: 'id' and 'title'
	this.id = inputId;
	this.title = inputTitle;
}

function roles(inputId, inputAreaId, inputTitle)
{
	// Define an object called "roles" with two properties: 'id' and 'title'
	this.id = inputId;
	this.areaId = inputAreaId;
	this.title = inputTitle;
}

function generalUsers(inputId, inputName)
{
	// Define a users object that will hold the id, name
	this.id = inputId;
	this.name = inputName;
}

function generalObject(inputId, inputArea, inputRole, inputManagerID)
{
	// Define a general object that will hold the id, area, role, and the managerid
	this.id = inputId;
	this.area = inputArea;
	this.role = inputRole;
	this.managerid = inputManagerID;
}

</script>




<BODY onload="populateArea();">


<TR>
<TD WIDTH="40%"><FONT SIZE="1"><b>Area</b>:
<TD>
<SELECT id="Area" NAME="Area" style="width:300px; font-size:11px;" onchange="javascript:populateRole();"></SELECT>
</TR>

<TR>
<TD><FONT SIZE="1"><b>Role</b>:
<TD>
<SELECT id="Role" NAME="Role" style="width:300px; font-size:11px" onchange="javascript:selectManagerID();"></SELECT>
</TR>

<TR>
<TD><FONT SIZE="1">Role Owner:
<TD>
<SELECT id="RoleOwnerID" NAME="RoleOwnerID" style="width:300px; font-size:11px;"></SELECT>
</TR>

Open in new window

0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 82

Assisted Solution

by:hielo
hielo earned 500 total points
Comment Utility
>>Call to undefined function fetch_assoc()
My apologies for the oversight.  I left code from my test code and forgot to update the code before posting.  Lines 16 and 28 are missing the "mssql_" prefix on the "fetch_assoc()" function call.  It should have been:

while( $row = mssql_fetch_assoc($result) )
0
 

Author Comment

by:kbit
Comment Utility
In "View source" I can see that the arrays are building perfectly although the dropdowns are not displaying any values, does this code need to be called in the body onload?
0
 
LVL 82

Expert Comment

by:hielo
Comment Utility
>> does this code need to be called in the body onload
The last chunk of cody you posted is NOT what I posted.  Thus, when you say"this code" it is not clear to me if you are referring to my post or yours.

I can only speak for what I posted.  On lines 68-70 of my post you will find:
            window.onload=function(){
            	loadList(document.test.Transactions)
            };

Open in new window


which is equivalent to <BODY onload="loadList(document.test.Transactions)">.  So yes, it does require a form of body onload, which you can achieve either via javascript (as I did) OR by altering the <BODY> tag to:
<BODY onload="loadList(document.test.Transactions)">
0
 

Author Comment

by:kbit
Comment Utility
Sorry yes I was referring to your code. When I moved

     
loadList(document.test.Transactions)

Open in new window


to the BODY tag it worked perfectly, thank you very much for your help.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction HTML checkboxes provide the perfect way for a web developer to receive client input when the client's options might be none, one or many.  But the PHP code for processing the checkboxes can be confusing at first.  What if a checkbox is…
Article by: DanRollins
This article describes a JavaScript program that creates a maze made of hexagonal cells.  In Part 2 (http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/A_7850-Hex-Maze-Part-2.html), we'll extend the program by adding a depth-…
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now