EDIT: 08 Jan 2007 ::A More up to date technique using AJAX can also be found at Dynamic/Chained Selects using Ajax with Prototype
The requirement of creating two select boxes which are interlinked has come up a few times on the forum. This tutorial builds two select boxes from data within a Postgres Database. The two select boxes are "Company" and "Employee". When the page is loaded, the company is set to "All" and the Employee select box contains all employees for all companies. If the client
changes the company box however only those employees from within that company are shown. The method could also be used for things like countries and states, manufacturers and car makes etc.
This tutorial has been tested on Mozilla Firefox and Opera 4.03. It is built using PHP 4.3.4.
Example HTML Result
You may want to cut and paste the following code which has been built with the code to try it out (submit will not do anything).
Code: Select all
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/HTML4.01/loose.dtd">
<html>
<head>
<title>Dynamic Select Using PHP</title>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-15">
<script language="Javascript">
<!--
function emptySelect()
{
var selectbox2=document.myform.employee;
for (i=selectbox2.length;i>=0;i--) {
selectbox2.options[i]=null;
}
}
function fillSelect()
{
emptySelect();
var selectbox1=document.myform.company;
var selectbox2=document.myform.employee;
var use_index=selectbox1.selectedIndex;
if (use_index == 0) {
// Need all of selectbox2 possibilties
for(i=1;i<companies.length;i++) {
my_index=companies?i];
for (ia=0;ia<employee_name?my_index].length;ia++) {
myOption = new Option(employee_name?my_index]?ia],employee_value?my_index]?ia])
optionPos = selectbox2.options.length
selectbox2.options?optionPos]=myOption
}
}
} else {
var my_index=selectbox1.options?use_index].value;
for (i=0;i<employee_name?my_index].length;i++) {
myOption = new Option(employee_name?my_index]?i],employee_value?my_index]?i])
optionPos = selectbox2.options.length
selectbox2.options?optionPos]=myOption
}
}
}
var employee_name=Array();
var employee_value=Array();
var companies=new Array(0,10,20,30,40,50);
employee_name?10]=new Array("Anna","Andrew","Andrea","Annakin");
employee_name?20]=new Array("Bill","Bob","Bernie");
employee_name?30]=new Array("Charles","Camila","Connie","Constance");
employee_name?40]=new Array("Daniel","Darla","Danny");
employee_name?50]=new Array("Edmund","Edgar","Elisabeth","Eugene","Emma","Emily");
employee_value?10]=new Array(11,12,15,16);
employee_value?20]=new Array(21,22,23);
employee_value?30]=new Array(35,36,37,38);
employee_value?40]=new Array(902,204,748);
employee_value?50]=new Array(18,199,200,284,987,6);
//-->
</script>
</head>
<body>
<p>The code used is free to use for any purpose but has no guarantees.
If you find it useful consider donating something to PHP development forums.
</p>
<form method="POST" name="myform">
<input type="hidden" name="tid" value="0">
<table>
<tr>
<td>Employee</td>
<td>
<select name="company" onchange="javascript:fillSelect();">
<option value="0">All</option>
<option value="10" >Alpha Co.</option>
<option value="20" >Beta Co.</option>
<option value="30" >Gamma Co.</option>
<option value="40" >Delta Co.</option>
<option value="50" >Epsilon Co.</option>
</select>
...
<select name="employee">
<option value="11">Anna</option>
<option value="12">Andrew</option>
<option value="15">Andrea</option>
<option value="16">Annakin</option>
<option value="21">Bill</option>
<option value="22">Bob</option>
<option value="23">Bernie</option>
<option value="35">Charles</option>
<option value="36">Camila</option>
<option value="37">Connie</option>
<option value="38">Constance</option>
<option value="902">Daniel</option>
<option value="204">Darla</option>
<option value="748">Danny</option>
<option value="18">Edmund</option>
<option value="199">Edgar</option>
<option value="200">Elisabeth</option>
<option value="284">Eugene</option>
<option value="987">Emma</option>
<option value="6">Emily</option>
</select>
</td>
</tr>
<tr>
<td>Task:</td>
<td><input type="text" name="task_title" value="" size=80 maxlength=64></td>
</tr>
<tr>
<td colspan=2><input type="submit" name="save" value="Create"></td>
</tr>
</table>
</form>
</body>
</html>The javascript is the key to the system.
Code: Select all
function emptySelect()
{
var selectbox2=document.myform.employee;
for (i=selectbox2.length;i>=0;i--) {
selectbox2.options[i]=null;
}
}growing everytime the user changes the company select box.
Code: Select all
function fillSelect()
{
emptySelect();
var selectbox1=document.myform.company;
var selectbox2=document.myform.employee;
var use_index=selectbox1.selectedIndex;
if (use_index == 0) {
// Need all of selectbox2 possibilties
for(i=1;i<companies.length;i++) {
my_index=companies?i];
for (ia=0;ia<employee_name?my_index].length;ia++) {
myOption = new Option(employee_name?my_index]?ia],employee_value?my_index]?ia])
optionPos = selectbox2.options.length
selectbox2.options?optionPos]=myOption
}
}
} else {
var my_index=selectbox1.options?use_index].value;
for (i=0;i<employee_name?my_index].length;i++) {
myOption = new Option(employee_name?my_index]?i],employee_value?my_index]?i])
optionPos = selectbox2.options.length
selectbox2.options?optionPos]=myOption
}
}
}The first thing it does it clear the employee box. The if builds the list of all employees (use_index == 0), or a limited list.
It uses the array indexes of employee_name and employee_value to identify which company it is using.
This function relies on three arrays built as strings by PHP and placed in the <head><script> section of the HTML.
Code: Select all
var employee_name=new Array();
var employee_value=new Array();
var companies=new Array(0,10,20,30,40,50);
employee_name?10]=new Array("Anna","Andrew","Andrea","Annakin");
employee_name?20]=new Array("Bill","Bob","Bernie");
employee_name?30]=new Array("Charles","Camila","Connie","Constance");
employee_name?40]=new Array("Daniel","Darla","Danny");
employee_name?50]=new Array("Edmund","Edgar","Elisabeth","Eugene","Emma","Emily");
employee_value?10]=new Array(11,12,15,16);
employee_value?20]=new Array(21,22,23);
employee_value?30]=new Array(35,36,37,38);
employee_value?40]=new Array(902,204,748);
employee_value?50]=new Array(18,199,200,284,987,6);The important detail is that the name and value arrays are an array (indexed by the company id) of arrays. The company id is the primary key for the company table.
The companies array is used to build the complete employee list (The first if section).
Example Page Building
Rather than just build portions of dummy code I will use this tutorial to build a complete php page which covers a task creation and modification. This is in three steps, the database, a database wrapper class and the actual code itself.
If you have the system working and wish to modify a task you would need to pass a valid/existing tid into the url
Database Table Creation
Code: Select all
CREATE SEQUENCE "company_seq";
CREATE SEQUENCE "employee_seq";
CREATE SEQUENCE "task_seq";
CREATE TABLE "companies" (
"cid" int4 DEFAULT nextval('company_seq'::text) NOT NULL,
"co_name" varchar(128) NOT NULL,
CONSTRAINT "companies_pkey" PRIMARY KEY ("cid")
);
CREATE TABLE "employees" (
"eid" int4 DEFAULT nextval('employee_seq'::text) NOT NULL,
"emp_name" varchar(128) NOT NULL,
"company_ref" int4 NOT NULL,
CONSTRAINT "employees_pkey" PRIMARY KEY ("eid")
);
CREATE TABLE "tasks" (
"tid" int4 DEFAULT nextval('task_seq'::text) NOT NULL,
"employee_ref" int4 NOT NULL,
"task_title" varchar(64) NOT NULL,
CONSTRAINT "tasks_pkey" PRIMARY KEY ("tid")
);Code: Select all
INSERT INTO "companies" ("cid", "co_name") VALUES(10, 'Alpha Co.');
INSERT INTO "companies" ("cid", "co_name") VALUES(20, 'Beta Co.');
INSERT INTO "companies" ("cid", "co_name") VALUES(30, 'Gamma Co.');
INSERT INTO "companies" ("cid", "co_name") VALUES(40, 'Delta Co.');
INSERT INTO "companies" ("cid", "co_name") VALUES(50, 'Epsilon Co.');
INSERT INTO "employees" ("eid", "emp_name", "company_ref") VALUES(11, 'Anna', 10);
INSERT INTO "employees" ("eid", "emp_name", "company_ref") VALUES(12, 'Andrew', 10);
INSERT INTO "employees" ("eid", "emp_name", "company_ref") VALUES(15, 'Andrea', 10);
INSERT INTO "employees" ("eid", "emp_name", "company_ref") VALUES(16, 'Annakin', 10);
INSERT INTO "employees" ("eid", "emp_name", "company_ref") VALUES(21, 'Bill', 20);
INSERT INTO "employees" ("eid", "emp_name", "company_ref") VALUES(22, 'Bob', 20);
INSERT INTO "employees" ("eid", "emp_name", "company_ref") VALUES(23, 'Bernie', 20);
INSERT INTO "employees" ("eid", "emp_name", "company_ref") VALUES(35, 'Charles', 30);
INSERT INTO "employees" ("eid", "emp_name", "company_ref") VALUES(36, 'Camila', 30);
INSERT INTO "employees" ("eid", "emp_name", "company_ref") VALUES(37, 'Connie', 30);
INSERT INTO "employees" ("eid", "emp_name", "company_ref") VALUES(38, 'Constance', 30);
INSERT INTO "employees" ("eid", "emp_name", "company_ref") VALUES(902, 'Daniel', 40);
INSERT INTO "employees" ("eid", "emp_name", "company_ref") VALUES(204, 'Darla', 40);
INSERT INTO "employees" ("eid", "emp_name", "company_ref") VALUES(748, 'Danny', 40);
INSERT INTO "employees" ("eid", "emp_name", "company_ref") VALUES(18, 'Edmund', 50);
INSERT INTO "employees" ("eid", "emp_name", "company_ref") VALUES(199, 'Edgar', 50);
INSERT INTO "employees" ("eid", "emp_name", "company_ref") VALUES(200, 'Elisabeth', 50);
INSERT INTO "employees" ("eid", "emp_name", "company_ref") VALUES(284, 'Eugene', 50);
INSERT INTO "employees" ("eid", "emp_name", "company_ref") VALUES(987, 'Emma', 50);
INSERT INTO "employees" ("eid", "emp_name", "company_ref") VALUES(6, 'Emily', 50);task for an employee.
A Database wrapper class
Now we have the database setup we need to be able to access it. I use a wrapper so I could potentially
change my database type (to MySQL) relatively easily. I would just need to change the wrapper. There are plenty
of database wrappers in existance. The one below is only a demonstration, but works with the PHP. The initial
variables need to be changed for your system.
Code: Select all
<?php
class DB
{
var $database="";
var $user="defaultuser";
var $password="whatever";
var $host="serverhost";
var $port="5432";
var $persistent=false;
// Connection handle
var $conn=NULL;
// Query Result
var $result=false;
var $row=0;
function DB($database,$user=false,$password=false,$host=false,$port=false,$persistent=false)
{
$this->database = $database;
if ($host) $this->host = $host;
if ($user) $this->user = $user;
if ($password) $this->password = $password;
if ($port) $this->port = $port;
$this->persistent = $persistent;
}
function open()
{
// Choose the appropriate open function
if ($this->persistent) {
$funct="pg_pconnect";
} else {
$funct="pg_connect";
}
// Build the connection string
if (!empty($this->host)) $connstr.="host=".$this->host." ";
if (!empty($this->port)) $connstr.="port=".$this->port." ";
if (!empty($this->user)) $connstr.="user=".$this->user." ";
if (!empty($this->password)) $connstr.="password=".$this->password." ";
if (!empty($this->database)) $connstr.="dbname=".$this->database;
// Try connection
$this->conn = $funct($connstr);
if (!$this->conn) {
return false;
}
return true;
}
function close()
{
return (pg_close($this->conn));
}
function error()
{
return (pg_last_error());
}
function query($sql="")
{
$this->result = pg_exec($this->conn, $sql);
$this->row=0;
return ($this->result!=false);
}
function affectedRows()
{
return(pg_affected_rows($this->result));
}
function numRows()
{
return(pg_num_rows($this->result));
}
function fetchAssoc()
{
if ($this->row >= pg_num_rows($this->result)) return false;
return (pg_fetch_array($this->result,$this->row++,PGSQL_ASSOC));
}
function freeResult()
{
$this->row=0;
return (pg_free_result($this->result));
}
}
?>Now we have the wrapper and the database we can build the actual page. I prefer to build PHP variables which are output at the end of the page imbedded within HTML rather than mix the two. This is a matter of personal preference.
Many of you will want the code just to build the select statement. This is at line 194.
Code: Select all
<?php
// Our database wrapper.
include ("db.class.php");
/*
*
* Initialisation
*
*/
// Javascript strings
$company_array='';
$names_array='';
$values_array=='';
// Register Globals set to off so set defaults here
$tid=0;
$task_title='';
$employee=0;
$company=0;
// Variable form variables including the company and employee select boxes
$selbox1='';
$selbox2='';
$save_type='New';
// Error handling
$msg='';
$err=array();
/*
*
* Create a new database connection.
* YOU WILL NEED TO CHANGE THE 'tutorial' TO BE YOUR DB NAME !!
*
*/
$db=new DB('tutorial');
if (!$db->open()) {
die('Cannot connect to database !');
}
/*
*
* Process basic information, TID is the task Id for edit or 0 for new
*
*/
if (isset($_REQUEST['tid'])) {
$tid=floor($_REQUEST['tid']);
} else {
$tid=0;
}
/*
*
* Has a new task been created, if so we need a message
*
*/
if (isset($_GET['cid'])) {
if ($_GET['cid'] == 1) {
$msg='<font color="green">Created task ['.$_GET['cid'].'] Successfully</font>';
}
}
/*
*
* Is this page the result of the form ? (We know the form is send data as POST)
*
*/
if (count($_POST)>0) {
//
// Perform validation
//
// Hack tests (POST array missing values).
// SQL injection is a threat you should consider when coding. These tests are not exhaustive.
if (!isset($_POST['task_title'])) {
die('Hack Attempt !');
} else {
$task_title=$_POST['task_title'];
}
if (!isset($_POST['employee'])) {
die('Hack Attempt !');
} else {
$employee=floor($_POST['employee']);
if (!$employee) {
die('Hack attempt');
}
}
if (!isset($_POST['company'])) {
die('Hack Attempt !');
} else {
$company=floor($_POST['company']);
}
// task validation
$length=strlen($task_title);
if ($length == 0) {
$err[]='Task must have content !';
} elseif ($length>64) {
// with maxlength set this should not be possible.
die('Hack Attempt !');
}
// ; character can be used for SQL injection. Avoid it.
if (stristr($task_title,';')) {
$err[]='Task is not allowed to contain the character ";"';
$task_title=str_replace(';',$task_title);
}
// Build error messages
if (count($err) == 1) {
$msg='<font color="red">Error:</font>'.$err[0];
} elseif (count($err)>0) {
$msg='<font color="red">Error:</font><ul><li>'.implode('</li><li>',$err).'</li></ul>';
} else {
// assume we can save
if ($tid) {
// We are updating
if (!$db->query("UPDATE tasks SET employee_ref=$employee,task_title='$task_title' WHERE tid=$tid")) {
$msg.='<font color="red">Error:</font> failed to update task';
} else {
$msg.='<font color="green">Saved Task</font>';
}
} else {
// We are creating
// Postgres requires you to get the next id rather that it being supplied by the insert
// which I believe is the case with MySQL. This code would need to be changed
// accordingly.
$db->query("SELECT nextval('task_seq')");
$row=$db->fetchAssoc();
$tid=$row['nextval'];
if (!$db->query("INSERT INTO tasks VALUES ($tid,$employee,'$task_title')")) {
$msg='<font color="red">Error:</font> Failed to insert task';
} else {
/*
*
* To avoid duplication if the "back" button is pressed on the browser
* I tend to relocate the page. For this tutorial I simply reload the page
* as though it is being edited, rather than creation. The cid is there to
* just allow the code to recognise that the task is newly created. In a full
* system this could be a session variable with a meaningful message and the page
* could be relocated anywhere.
*
*/
// reload page (could use $_SERVER['PHP_SELF']) rather than basename
header('Location:'.basename(__FILE__).'?tid='.$tid.'&cid=1');
exit;
}
}
}
}
$save_type="Create";
/*
*
* Get existing information from database if needed. We use a database join
* as we need to fill in the employee selectbox with only the data of the company
* already filled in.
*
*/
if ((!$err) AND ($tid)) {
// Get information from database
if (!$db->query("SELECT tasks.*,company_ref FROM tasks,employees WHERE tid=$tid AND employee_ref=eid")) {
die('Task load failure');
}
if ($row=$db->fetchAssoc()) {
$task_title=$row['task_title'];
$employee=$row['employee_ref'];
$company=$row['company_ref'];
$save_type="Update";
}
}
/*
*
* Need employee and company data in two select boxes. To start we get all the possibilities.
* We then start building the select boxes as two different string variables.
* While building $done[company_primary_key] is used to tell if the company
* has already been processed. If not we need to add it.
* $emp_names holds all the employee names, $emp_values the values.
* $add_flag determines if we need the employee to be added to the second select box.
* $sel_flag1 and $sel_flag2 hold the string ' selected' if the company or employee are the
* current values.
*
* NOTE: We need the select boxes provided by the server, not simply called on an onload
* event to ensure that the selected values are correct.
*
*
*/
if (!$db->query("SELECT * FROM companies,employees WHERE company_ref=cid")) {
die('No company or employee data !');
} else {
$num_rows=$db->numRows();
if ($num_rows == 0) {
die('No company or employee data !');
} elseif ($numrows == 1) {
// select boxes meaningless as only one.
$selbox1='<input type="hidden" name="company" value="'.$row['cid'].'">'.$row['co_name'];
$selbox2='<input type="hidden" name="employee" value="'.$row['eid'].'">'.$row['emp_name'];
} else {
// Need to build our select boxes
$done=array();
$selbox1='<select name="company" onchange="javascript:fillSelect();"><option value="0">All</option>';
$selbox2='<select name="employee">';
$emp_names=array();
$emp_values=array();
$addflag=1;
// Loop through the results
while ($row=$db->fetchAssoc()) {
// Store javascript requirements (Note the index for later handling)
$emp_name[$row['cid']][]=$row['emp_name'];
$emp_value[$row['cid']][]=$row['eid'];
$sel_flag1='';
$sel_flag2='';
// Set if we need to add the employee and set the company selected status.
if ($company) {
if ($company == $row['cid']) {
$sel_flag1=' selected';
$addflag=1;
} else {
$addflag=0;
}
} else {
$addflag=1;
}
// Set the employee selected status. If the employee is selected the company must also be.
if ($employee) {
if ($employee == $row['eid']) {
$sel_flag2=' selected';
$sel_flag1=' selected';
}
}
// Only add the company if not already processed or overwrite if selected.
if ((!isset($done[$row['cid']])) OR ($sel_flag1)) {
$done[$row['cid']]='<option value="'.$row['cid'].'" '.$sel_flag1.'>'.$row['co_name'].'</option>';
}
// Add the employee to the appropriate select box.
if ($addflag) {
$selbox2.='<option value="'.$row['eid'].'"'.$sel_flag2.'>'.$row['emp_name'].'</option>';
}
} // Close While
// Finish html select boxes
$selbox1.=implode('',$done).'</select>';
$selbox2.='</select>';
// Finish javascript arrays
$company_array='var companies=new Array(0,'.implode(',',array_keys($done)).');';
foreach ($done as $key=>$value) {
$names_array.="\n".'employee_name['.$key.']=new Array("'.implode('","',$emp_name[$key]).'");';
$values_array.="\n".'employee_value['.$key.']=new Array('.implode(',',$emp_value[$key]).');';
}
}
}
/*
*
* We have all the variables we need so we can output the actual page. We can close php
*
*/
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/HTML4.01/loose.dtd">
<html>
<head>
<title>Dynamic Select Using PHP</title>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-15">
<script language="Javascript">
<!--
function emptySelect()
{
var selectbox2=document.myform.employee;
for (i=selectbox2.length;i>=0;i--) {
selectbox2.options[i]=null;
}
}
function fillSelect()
{
emptySelect();
var selectbox1=document.myform.company;
var selectbox2=document.myform.employee;
var use_index=selectbox1.selectedIndex;
if (use_index == 0) {
// Need all of selectbox2 possibilties
for(i=1;i<companies.length;i++) {
my_index=companies[i];
for (ia=0;ia<employee_name[my_index].length;ia++) {
myOption = new Option(employee_name[my_index][ia],employee_value[my_index][ia])
optionPos = selectbox2.options.length
selectbox2.options[optionPos]=myOption
}
}
} else {
var my_index=selectbox1.options[use_index].value;
for (i=0;i<employee_name[my_index].length;i++) {
myOption = new Option(employee_name[my_index][i],employee_value[my_index][i])
optionPos = selectbox2.options.length
selectbox2.options[optionPos]=myOption
}
}
}
var employee_name=Array();
var employee_value=Array();
<?php echo($company_array); ?>
<?php echo($names_array); ?>
<?php echo($values_array); ?>
//-->
</script>
</head>
<body>
<?php echo($msg); ?>
<p>The code used is free to use for any purpose but has no guarantees.
If you find it useful consider donating something to PHP development forums.
</p>
<form method="POST" name="myform">
<input type="hidden" name="tid" value="<?php echo($tid); ?>"
<table>
<tr>
<td>Employee</td>
<td><?php echo($selbox1); ?> ... <?php echo($selbox2); ?></td>
</tr>
<tr>
<td>Task:</td>
<td><input type="text" name="task_title" value="<?php echo(htmlspecialchars($task_title)); ?>" size=80 maxlength=64></td>
</tr>
<tr>
<td colspan=2><input type="submit" name="save" value="<?php echo($save_type); ?>"></td>
</tr>
</table>
</form>
</body>
</html>