A More up to date technique using AJAX can also be found at Dynamic/Chained Selects using Ajax with Prototype
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:
<!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,3
;
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
The javascript is the key to the system.
Code:
function emptySelect()
{
var selectbox2=document.myform.employee;
for (i=selectbox2.length;i>=0;i--) {
selectbox2.options?i]=null;
}
}
This function is used to empty the employee select box. Without it you would get your select list
growing everytime the user changes the company select box.
Code:
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
}
}
}
This is the main code. It is simple javascript "for loops" which step through arrays to build options for the select boxes.
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:
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,3;
employee_value?40]=new Array(902,204,748);
employee_value?50]=new Array(18,199,200,284,987,6);
If you take an option as <option value="employee_value">employee_name</option> you should be able to work out what is happening.
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:
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")
);
Default Test Information
Code:
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);
The tasks table is not required for the actual select box, it is used for this tutorial to save an assigned
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.
php:
<?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));
}
}
?>
The full code
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.
php:
<?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>
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:
<!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,3
;
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
The javascript is the key to the system.
Code:
function emptySelect()
{
var selectbox2=document.myform.employee;
for (i=selectbox2.length;i>=0;i--) {
selectbox2.options?i]=null;
}
}
This function is used to empty the employee select box. Without it you would get your select list
growing everytime the user changes the company select box.
Code:
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
}
}
}
This is the main code. It is simple javascript "for loops" which step through arrays to build options for the select boxes.
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:
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,3
;
employee_value?40]=new Array(902,204,748);
employee_value?50]=new Array(18,199,200,284,987,6);
If you take an option as <option value="employee_value">employee_name</option> you should be able to work out what is happening.
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:
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")
);
Default Test Information
Code:
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);
The tasks table is not required for the actual select box, it is used for this tutorial to save an assigned
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.
php:
<?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));
}
}
?>
The full code
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.
php:
<?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>
