Fandom

PHP Wiki

Layered database dropdowns

51pages on
this wiki
Add New Page
Talk0 Share

This set of scripts is a correction of that built in this tutorial. Improvements include:

  • Unobtrusive JavaScript (Users with JavaScript disabled can still use the application.);
  • Modular PHP and JavaScript (They aren't intertwined, so the PHP could be used with, for example, VBScript.);
  • Separation of logic and layout (No HTML event-handlers are used.);
  • Scalability (Any number of database/dropdown pairs may be used without significant modification.);
  • Reliable URLs (They may be freely exchanged between users with and without JavaScript enabled and the page will still work.).

However, the fundamental and arguably flawed design is retained: Categories are represented as ("flat") field values rather than tables in the database. An alternative implementation with one table per category should be available soon.

The JavaScript module is available on the JavaScript Wiki.

connection.phpEdit

connection.php remains essentially unchanged from the tutorial, except that it also provides your table names to the other scripts. (license)
<?php
 
$servername='localhost';
$dbusername='servername_dbname;
$dbpassword='password';
$dbname='database name;
 
connecttodb($servername,$dbname,$dbusername,$dbpassword);
function connecttodb($servername,$dbname,$dbuser,$dbpassword){
    global $link;
    $link=mysql_connect ("$servername","$dbuser","$dbpassword");
    if(!$link)
        die("Could not connect to MySQL");
    mysql_select_db("$dbname",$link) or die ("could not open db".mysql_error());
}
 
$tables = array('Main' => 'group', 'Category' => 'category', 'SubCat' => 'subcategory');
 
?>

index.phpEdit

The form and result page are now unified as index.php; the second HTML document is the result page. Also, you'll notice that not one but two JavaScript files are linked. The first allows JavaScript to receive JSON-encoded data from PHP. (license)
<?php
require "connection.php";
 
$gets = array_keys($tables);
$hidden = array();
for($i = 0; $i < count($gets) && isset($_GET[$gets[$i]]); $i++) // Find the most specific GET value.
	$hidden[$gets[$i]] = $_GET[$gets[$i]]; // Transfer all GET values to hidden fields.
 
if($i < count($gets)){
	$selection = '';
	if($i > 1)
		$selection = "`{$tables[$gets[$i - 1]]}`, ";
	$selection .= "`{$tables[$gets[$i]]}`";
 
	$query = mysql_query("select $selection from `{$tables[$gets[$i]]}`");
	echo mysql_error();
	$options = array();
	while($result = mysql_fetch_array($query))
		array_push($options, $result[$tables[$gets[$i]]]);
	?>
<html>
	<head>
		<title>Javascript Form Updater</title>
		<script type="text/javascript" src="json2.js"></script>
		<script type="text/javascript" src="layeredDbSelects.js.php"></script>
	</head>
	<body>
		<form name="drop_list" action="<?php echo $_SERVER['PHP_SELF']; ?>" method="get">
			<?
			foreach($hidden as $name => $value)
				echo "<input type=\"hidden\" name=\"$name\" value=\"$value\">";
			?>
			<select name="<?php echo $gets[$i]; ?>">
				<option value=""><?php echo $gets[$i]; ?></option>
				<?
				foreach($options as $option)
					echo "<option value=\"$option\">$option</option>";
				?>
			</select>
			<input type="submit" value="Submit">
		</form>
	</body>
</html>
	<?php }else{ ?>
<html>
	<head>
		<title>Javascript Form Updater</title>
	</head>
	<body>
 
	</body>
</html>
	<?php
}
?>

layers.phpEdit

layers.php prepares the data to be sent to the JavaScript module via JSON. (license)
<?php
require "connection.php";
 
$keys = array_keys($tables);
 
/**
 * $levels is a series of "flat" (1-dimensional) arrays to accomodate our flat,
 * conglomerate DB tables.  By assigning stuff by reference, we can build the
 * lower levels up along with the higher ones so they're not flat anymore. We
 * give the whole thing to JavaScript.
 */
$levels = array();
 
for($i = 0; $i < count($keys); $i++){
	$self = array();
	$selection = '';
	if($i > 0){
		$previous = $tables[$keys[$i - 1]];
		$selection = "`$previous`, ";
	}
	$current = $tables[$keys[$i]];
	$selection .= "`$current`";
 
	$query = mysql_query("select $selection from `{$tables[$keys[$i]]}`");
	echo mysql_error();
	while($result = mysql_fetch_array($query)){
		if($i > 0)
			$levels[$keys[$i - 1]][$result[$previous]][$result[$current]] = $self;
		$levels[$keys[$i]][$result[$current]] = $self;
	}
}
?>

LicensesEdit

DocumentationEdit

This file is licensed under the GFDL. Permission is granted to copy, distribute and/or modify it under the terms of the GNU Free Documentation License, Version 1.2 or any later version published by the Free Software Foundation; with no Invariant Sections, no Front-Cover Texts, and no Back-Cover Texts. Subject to Wikia disclaimers.


Source codeEdit

This file is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version. This work is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

Ad blocker interference detected!


Wikia is a free-to-use site that makes money from advertising. We have a modified experience for viewers using ad blockers

Wikia is not accessible if you’ve made further modifications. Remove the custom ad blocker rule(s) and the page will load as expected.