Scripts
Dynamic Linked Dropdown Menu (PHP,MYSQL,JAVASCRIPT

Door Jeroen de Jonge -12-12-2005 00:00 -20.111 views
Aangezien mijn test server's HD gecrashed is en hierdoor vanavond niets kan doen. Word het eens tijd om wat code te delen met de gebruikers hier. Aangezien ik vaak tips en tricks hier vandaan heb gehaald(bedankt hiervoor). In mijn zoektocht op internet naar het onderstaande script heb ik alleen nog maar bot gevangen. Daarom hier de uitgewerkte code voor een dynamic linked dropdown select uit mysql. Script 1: Geeft 2 gelinkte dropdown boxes Bijvoorbeeld: 1e dropdown menu - selecteer auto merk de 2e dropdown word gevuld met waarden die relevant zijn voor wat in de eerste dropdown is geselecteerd. Bijvoorbeeld eerste selectie vak is volvo De 2de groep geeft dan de opties week s40 s50 s60 etc etc Hieronder staan 3 scripts: respectievelijk 2 , 3 en 4 dropdowns. Afhankelijk van de groote van je database en de snelheid van je server zou ik het aantal records beperkt houden. stel 2 dropdowns genereren 1 kb aan code, 3 dropdowns hebben deze hoeveelheid in het kwadraat Er kan gebruik gemaakt worden van één enkele tabel waarin alles staat | volvo | S60 | Diesel | 1998 | volvo | S70 | Benzine | 1995 | volvo | S80 | LPG | 1997 De script werken met een globale variabele waar telkens stukjes code in worden gedropt naarmaate php de code verwerkt. De code is gemakkelijk te converteren voor andere toepassingen om dat het geheel is opgezet als functie. Vul boven in de database, username, password etc in. Vul vervolgens onderaan in het script dual_select('naam_kolom1', 'naam_kolom2' etc etc) en het script is al werkend.
Download alle bestanden als ZIP
dynamic-linked-dropdown-menu-phpmysqljavascript
CODE VOOR DUAL-SELECT
[code]<?
// Database connection
$db_database = 'databasename';
$db_host = 'localhost';
$db_user = 'username';
$db_pass = 'password';
mysql_connect($db_host,$db_user,$db_pass) or die("Could not connect to MySQL (Main connection)");
mysql_select_db($db_database) or die("Could not connect to database (Main connection)");


function dual_linked_select(
$table,			// Table to create linked selects from
$table_column_01,		// Root category
$table_column_02,		// Subcategory of the root category
$group_02_default_option_text	// Default option text for the group 2 select
)
{

// Define globals
	global $javascript;
	global $group_01_options;
	// Define variables
	$javascript = null; // Hold
	$group_01_options = null;//Hold

// Assembly of Javascript starts
	$javascript .=<<<content
	/* Linked Dropdown Selects Script Start */
	function DefaultGroup2()
	{
		var x = document.getElementById("Group2");
		x.length = 0;
		VarGroup2 = document.getElementById("Group2");
		VarGroup2.options[VarGroup2.options.length] = new Option("$group_02_default_option_text","");
		document.getElementById("Group2").disabled = true;
	}

	function CheckGroup1Select()
	{
		// If no Group1 is selected clear the Group2 and set to default
		if(document.getElementById("Group1").value == "")
		{
		// Clear the Group2 select and set to default value
		DefaultGroup2();
		}

content;

// Javascript 
	$group_01_query = "SELECT ".$table_column_01." FROM ".$table." GROUP BY ".$table_column_01;
	$group_01_result = mysql_query($group_01_query) or die(mysql_error());
	while($group_01 = mysql_fetch_array($group_01_result))
	{
		$group_01_options .= '<option value="'.$group_01[$table_column_01].'">'.$group_01[$table_column_01].'</option>'."\r\n";

		$javascript .=<<<content
		else if(document.getElementById("Group1").value == "$group_01[$table_column_01]")
		{
			// Clear the Group2 and set to default value
			DefaultGroup2();
			// Set variable options for Group2 select
			VarGroup2 = document.getElementById("Group2");

content;

		$group_02_query = "SELECT ".$table_column_02." FROM ".$table." WHERE ".$table_column_01." = '".$group_01[$table_column_01]."' GROUP BY ".$table_column_02;
		$group_02_result = mysql_query($group_02_query) or die(mysql_error());
		while($group_02 = mysql_fetch_array($group_02_result))
		{
			$javascript .='			VarGroup2.options[VarGroup2.options.length] = new Option("'.$group_02[$table_column_02].'","'.$group_02[$table_column_02].'");'."\r\n";
		}

			$javascript .=<<<content
			document.getElementById("Group2").disabled = false;

		}

content;
	}
		$javascript .='	}'."\r\n";

} // End of dual_linked_selects function
?>

<?
// execute dual_linked_selects_function
dual_linked_select('tabel_name','table_column1','tablecolumn2','Select...');
?>

<html>
<head>
	<title>Dual Linked Selects with PHP and MYSQL (without page reload)</title>
<script type="text/javascript" language="JavaScript">
<? echo $javascript; ?>
</script>
</head>

<body>
<form action="">
Group1:
<br>
<select id="Group1" name="group1" onchange="CheckGroup1Select(this);">
<option value="">Select...</option>
<? echo $group_01_options; ?>
</select>

<br>
Group2:
<br>
<select id="Group2" name="group2"></select>

</form>
</body>

</html>[/code]

CODE VOOR TRIPLE SELECT

[code]<?
// Database connection
$db_database = 'databasename';
$db_host = 'localhost';
$db_user = 'username';
$db_pass = 'password';
mysql_connect($db_host,$db_user,$db_pass) or die("Could not connect to MySQL (Main connection)");
mysql_select_db($db_database) or die("Could not connect to database (Main connection)");

// Start of triple_linked_selects function
function triple_linked_selects(
$table,		// Table to create linked selects from
$table_column_01,	// Root category
$table_column_02,	// Subcategory of the root category
$table_column_03,	// Available items in subcategory of the root category
$group_02_default_option_text,	// Default option text for the group 2 select
$group_03_default_option_text)	// Default option text for the group 3 select

{
// Define globals
	global $javascript;
	global $group_01_options;
// Define variables
	$javascript = null; // Holds all the generated javascript
	$group_01_options = null; // Holds all the select options for group 1

// Static Javascript
	$javascript .=<<<content
	/* Linked Dropdown Selects Script Start */
	function DefaultGroup2()
	{
		var x = document.getElementById("Group2");
		x.length = 0;
		VarGroup2 = document.getElementById("Group2");
		VarGroup2.options[VarGroup2.options.length] = new Option("$group_02_default_option_text","");
		document.getElementById("Group2").disabled = true;
	}

	function DefaultGroup3()
	{
		var x = document.getElementById("Group3");
		x.length = 0;
		VarGroup3 = document.getElementById("Group3");
		VarGroup3.options[VarGroup3.options.length] = new Option("$group_03_default_option_text","");
		document.getElementById("Group3").disabled = true;
	}

	function ClearGroup3()
	{
		var x = document.getElementById("Group3");
		x.length = 0;
		document.getElementById("Group3").disabled = false;
	}

	function CheckGroup1Select()
	{
		// If no Group1 is selected clear the Group2 and Group3 selects and set them to their default values
		if(document.getElementById("Group1").value == "")
		{
			// Clear the Group2 select and set to default value
			DefaultGroup2();
			// Clear the Group3 select and set to default value
			DefaultGroup3();
		}

content;

// Groups 1 & 2 Javascript
	$group_01_query = "SELECT ".$table_column_01." FROM ".$table." GROUP BY ".$table_column_01;
	$group_01_result = mysql_query($group_01_query) or die(mysql_error());
	while($group_01 = mysql_fetch_array($group_01_result))
	{
		$group_01_options .= '<option value="'.$group_01[$table_column_01].'">'.$group_01[$table_column_01].'</option>'."\r\n";

		$javascript .=<<<content
		else if(document.getElementById("Group1").value == "$group_01[$table_column_01]")
		{
			// Clear the Group2 select and set to default value
			DefaultGroup2();
			// Set dynamic options for Group2 select
			VarGroup2 = document.getElementById("Group2");

content;

		$group_02_query = "SELECT ".$table_column_02." FROM ".$table." WHERE ".$table_column_01." = '".$group_01[$table_column_01]."' GROUP BY ".$table_column_02;
		$group_02_result = mysql_query($group_02_query) or die(mysql_error());
		while($group_02 = mysql_fetch_array($group_02_result))
		{
			$javascript .='			VarGroup2.options[VarGroup2.options.length] = new Option("'.$group_02[$table_column_02].'","'.$group_02[$table_column_02].'");'."\r\n";
		}

			$javascript .=<<<content
			document.getElementById("Group2").disabled = false;
			// Clear the Group3 select and set to default value
			DefaultGroup3();
		}

content;
	}
		$javascript .='	}'."\r\n";

// Group 3 Javascript 

	$javascript .=<<<content
	function CheckGroup2Select()
	{

content;

	$group_01_query = "SELECT ".$table_column_01." FROM ".$table." GROUP BY ".$table_column_01;
	$group_01_result = mysql_query($group_01_query) or die(mysql_error());
	while($group_01 = mysql_fetch_array($group_01_result))
	{

	$javascript .=<<<content
		if((document.getElementById("Group1").value == "$group_01[$table_column_01]") && (!document.getElementById("Group2").value))
		{
			// Clear the Group3 select and set to default value
			DefaultGroup3();
		}

content;

		$group_02_query = "SELECT ".$table_column_02." FROM ".$table." WHERE ".$table_column_01." = '".$group_01[$table_column_01]."' GROUP BY ".$table_column_01.",".$table_column_02;
		$group_02_result = mysql_query($group_02_query) or die(mysql_error());
		while($group_02 = mysql_fetch_array($group_02_result))
		{

		$javascript .=<<<content
		else if((document.getElementById("Group1").value == "$group_01[$table_column_01]") && (document.getElementById("Group2").value == "$group_02[$table_column_02]"))
		{
			ClearGroup3();
			VarGroup3 = document.getElementById("Group3");

content;

			$group_03_query = "SELECT * FROM ".$table." WHERE ".$table_column_02." = '".$group_02[$table_column_02]."' AND ".$table_column_01." = '".$group_01[$table_column_01]."'  GROUP BY ".$table_column_03;
			$group_03_result = mysql_query($group_03_query) or die(mysql_error());
			while($group_03 = mysql_fetch_array($group_03_result))
			{
				$javascript .='			VarGroup3.options[VarGroup3.options.length] = new Option("'.$group_03[$table_column_03].'","'.$group_03[$table_column_03].'");'."\r\n";
			}
	$javascript .='		}'."\r\n";
		}
	}

	$javascript .=<<<content
	}

	window.onload = CheckGroup1Select;
	/* Linked Dropdown Selects Script End */

content;

} // End of triple_linked_selects function
?>

<?
// Call the triple_linked_selects function
triple_linked_selects('tabel_name','table_column1','tablecolumn2','tablecolumn3','Select...','------------');
?>

<html>
<head>
	<title>Triple Linked Selects</title>
<script type="text/javascript" language="JavaScript">
<? echo $javascript; ?>
</script>
</head>

<body>
<form action="">
Group1:
<br>
<select id="Group1" name="Group1" onchange="CheckGroup1Select(this);">
<option value="">Select...</option>
<? echo $group_01_options; ?>
</select>

<br>
Group2:
<br>
<select id="Group2" name="Group2" onchange="CheckGroup2Select(this);"></select>

<br>
Group3:
<br>
<select id="Group3" name="Group3"></select>
</form>
</body>
</html>[/code]

CODE VOOR QUAD SELECT

[code]<?
// Database connection
$db_database = 'databasename';
$db_host = 'localhost';
$db_user = 'username';
$db_pass = 'password';
mysql_connect($db_host,$db_user,$db_pass) or die("Could not connect to MySQL (Main connection)");
mysql_select_db($db_database) or die("Could not connect to database (Main connection)");

// Start of quad_linked_selects function
function quad_linked_selects(
$table,		// Table name
$table_column_01,	// Root category
$table_column_02,	// Available items in sub category
$table_column_03,	// Available items in sub-sub category
$table_column_04,	// Available items in sub-sub-sub category
$group_02_default_option_text,	// Default option text for the group 2 select
$group_03_default_option_text,	// Default option text for the group 3 select
$group_04_default_option_text)	// Default option text for the group 4 select
{
// Define globals
	global $javascript;
	global $group_01_options;
// Define variables
	$javascript = null; // Holds all the generated javascript
	$group_01_options = null; // Holds all the select options for group 1

// Static Javascript
	$javascript .=<<<content
	/* Linked Dropdown Selects Script Start */
	function DefaultGroup2()
	{
		var x = document.getElementById("Group2");
		x.length = 0;
		VarGroup2 = document.getElementById("Group2");
		VarGroup2.options[VarGroup2.options.length] = new Option("$group_02_default_option_text","");
		document.getElementById("Group2").disabled = true;
	}

	function DefaultGroup3()
	{
		var x = document.getElementById("Group3");
		x.length = 0;
		VarGroup3 = document.getElementById("Group3");
		VarGroup3.options[VarGroup3.options.length] = new Option("$group_03_default_option_text","");
		document.getElementById("Group3").disabled = true;
	}

	function DefaultGroup4()
	{
		var x = document.getElementById("Group4");
		x.length = 0;
		VarGroup4 = document.getElementById("Group4");
		VarGroup4.options[VarGroup4.options.length] = new Option("$group_04_default_option_text","");
		document.getElementById("Group4").disabled = true;
	}

	function ClearGroup3()
	{
		var x = document.getElementById("Group3");
		x.length = 0;
		document.getElementById("Group3").disabled = false;
	}

	function ClearGroup4()
	{
		var x = document.getElementById("Group4");
		x.length = 0;
		document.getElementById("Group4").disabled = false;
	}

	function CheckGroup1Select()
	{
		// If no Group1 is selected clear the Group2 and Group3 selects and set them to their default values
		if(document.getElementById("Group1").value == "")
		{
			// Clear the Group2 select and set to default value
			DefaultGroup2();
			// Clear the Group3 select and set to default value
			DefaultGroup3();
			// Clear the Group4 select and set to default value
			DefaultGroup4();
		}

content;

// Groups 1 & 2 Javascript
	$group_01_query = "SELECT ".$table_column_01." FROM ".$table." GROUP BY ".$table_column_01;
	$group_01_result = mysql_query($group_01_query) or die(mysql_error());
	while($group_01 = mysql_fetch_array($group_01_result))
	{
		$group_01_options .= '<option value="'.$group_01[$table_column_01].'">'.$group_01[$table_column_01].'</option>'."\r\n";

		$javascript .=<<<content
		else if(document.getElementById("Group1").value == "$group_01[$table_column_01]")
		{
			// Clear the Group2 select and set to default value
			DefaultGroup2();
			// Set dynamic options for Group2 select
			VarGroup2 = document.getElementById("Group2");

content;

		$group_02_query = "SELECT ".$table_column_02." FROM ".$table." WHERE ".$table_column_01." = '".$group_01[$table_column_01]."' GROUP BY ".$table_column_02;
		$group_02_result = mysql_query($group_02_query) or die(mysql_error());
		while($group_02 = mysql_fetch_array($group_02_result))
		{
			$javascript .='			VarGroup2.options[VarGroup2.options.length] = new Option("'.$group_02[$table_column_02].'","'.$group_02[$table_column_02].'");'."\r\n";
		}

			$javascript .=<<<content
			document.getElementById("Group2").disabled = false;
			// Clear the Group3 select and set to default value
			DefaultGroup3();
			DefaultGroup4();
		}

content;
	}
		$javascript .='	}'."\r\n";

// Group 3 Javascript 
	$javascript .=<<<content
	function CheckGroup2Select()
	{

content;

	$group_01_query = "SELECT ".$table_column_01." FROM ".$table." GROUP BY ".$table_column_01;
	$group_01_result = mysql_query($group_01_query) or die(mysql_error());
	while($group_01 = mysql_fetch_array($group_01_result))
	{

	$javascript .=<<<content
		if((document.getElementById("Group1").value == "$group_01[$table_column_01]") && (!document.getElementById("Group2").value))
		{
			// Clear the Group3&4 select and set to default value
			DefaultGroup3();
			DefaultGroup4();
		}

content;

		$group_02_query = "SELECT ".$table_column_02." FROM ".$table." WHERE ".$table_column_01." = '".$group_01[$table_column_01]."' GROUP BY ".$table_column_01.",".$table_column_02;
		$group_02_result = mysql_query($group_02_query) or die(mysql_error());
		while($group_02 = mysql_fetch_array($group_02_result))
		{

		$javascript .=<<<content
		else if((document.getElementById("Group1").value == "$group_01[$table_column_01]") && (document.getElementById("Group2").value == "$group_02[$table_column_02]"))
		{
			DefaultGroup3();
			VarGroup3 = document.getElementById("Group3");

content;
			$group_03_query = "SELECT * FROM ".$table." WHERE ".$table_column_02." = '".$group_02[$table_column_02]."' AND ".$table_column_01." = '".$group_01[$table_column_01]."'  GROUP BY ".$table_column_03;
			$group_03_result = mysql_query($group_03_query) or die(mysql_error());
			while($group_03 = mysql_fetch_array($group_03_result))
			{
				$javascript .='			VarGroup3.options[VarGroup3.options.length] = new Option("'.$group_03[$table_column_03].'","'.$group_03[$table_column_03].'");'."\r\n"			;
			}
	$javascript .='			document.getElementById("Group3").disabled = false;
			DefaultGroup4();
		}'."\r\n";
		}
	}

	$javascript .=<<<content
	}
	

content;


// Group 4 Javascript 
	$javascript .=<<<content
	function CheckGroup3Select()
	{

content;

	$group_01_query = "SELECT ".$table_column_01.", ".$table_column_02." FROM ".$table." GROUP BY ".$table_column_02;
	$group_01_result = mysql_query($group_01_query) or die(mysql_error());
	while($group_01 = mysql_fetch_array($group_01_result))
	{

	$javascript .=<<<content
		if((document.getElementById("Group2").value == "$group_01[$table_column_02]") && (document.getElementById("Group3").value == "$group_01[$table_column_03]") && (!document.getElementById("Group4").value))
		{
			// Clear the Group4 select and set to default value
			DefaultGroup4();
		}

content;

		$group_02_query = "SELECT ".$table_column_02.", ".$table_column_03." FROM ".$table." WHERE ".$table_column_02." = '".$group_01[$table_column_02]."' GROUP BY ".$table_column_02.",".$table_column_03;
		$group_02_result = mysql_query($group_02_query) or die(mysql_error());
		while($group_02 = mysql_fetch_array($group_02_result))
		{

		$javascript .=<<<content
		else if((document.getElementById("Group2").value == "$group_01[$table_column_02]") && (document.getElementById("Group3").value == "$group_02[$table_column_03]"))
		{
			DefaultGroup4();
			VarGroup4 = document.getElementById("Group4");

content;

			$group_03_query = "SELECT * FROM ".$table." WHERE ".$table_column_03." = '".$group_02[$table_column_03]."' AND ".$table_column_02." = '".$group_01[$table_column_02]."'  GROUP BY ".$table_column_04;
			$group_03_result = mysql_query($group_03_query) or die(mysql_error());
			while($group_03 = mysql_fetch_array($group_03_result))
			{
				$javascript .='			VarGroup4.options[VarGroup4.options.length] = new Option("'.$group_03[$table_column_04].'","'.$group_03[$table_column_04].'");'."\r\n";
			}
	$javascript .='			document.getElementById("Group4").disabled = false;

						}'."\r\n";
		}
	}

	$javascript .=<<<content
	}

	window.onload = CheckGroup1Select;
	/* Linked Dropdown Selects Script End */

content;



} // End of quad_linked_selects function
?>

<?
// Call the quad_linked_selects function
quad_linked_selects('tablename','tablecolumn1','tablecolumn2','tablecolumn3','tablecolumn4','Select...','------------','------------');
?>


<html>
<head>
	<title>Triple Linked Selects</title>
<script type="text/javascript" language="JavaScript">
<? echo $javascript; ?>
</script>
</head>

<body>
<form action="">
Group1:
<br>
<select id="Group1" name="Group1" onchange="CheckGroup1Select(this);">
<option value="">Select...</option>
<? echo $group_01_options; ?>
</select>

<br>
Group2:
<br>
<select id="Group2" name="Group2" onchange="CheckGroup2Select(this);"></select>

<br>
Group3:
<br>
<select id="Group3" name="Group3" onchange="CheckGroup3Select(this);"></select>

<br>
Group4:
<br>
<select id="Group4" name="Group4"></select>

</form>
</body>
</html>[/code]