Populate 2nd select_single based on input from 1st select_single

[DebbieS]

Hello fellow DaDaBIKers!

I'm working on a solution for one of my latest installs where they want a dual dependent drop-down selection for input. Here is the scoop:

select_single1 has options driven from one of the database tables

select_single2 should show options from another database table -- but only the options which match the selection in the first select_single

Has anyone worked on something like this in the past? I am currently working on trying to find a solution so perhaps if someone else requires this functionality, we can pool our brain cells and perhaps come up with a collective solution to help everyone.

Thanks!

 

[nobby95]

Hi DebbieS

Did you ever get to the bottom of this??

Cheers

Nobby95

 

[DebbieS]

Funny that you should post today ... I just finished successfully getting a chained select (for 3 fields) working. I'll have to take some time to retrace the steps I took to outline it here, so please be patient ... I hope to have time within the next week to get it all written up.

 

[DebbieS]

OK ... here goes. There is a lot of info here, but once you get into it, you can see how all the information flows.

For the most part, I've tried to list file names in italics and places where you have to change things in bold. To avoid too much horizontal scrolling, I've inserted a few line breaks into some of the code parts (noted with --NOBR--).

Good luck!


/include/internal_table.php
Add two field types noted in bold:

[pre]$int_fields_ar[2][0] = "Field type:";
$int_fields_ar[2][1] = "type_field";
$int_fields_ar[2][2] = "select_custom";
$int_fields_ar[2][3] = "text/textarea/rich_editor/password/insert_date/update_date/date/select_single –NOBR--
/chain1/chain2/generic_file/image_file/ID_user/unique_ID";[/pre]

NOTES:
-chain1 is used for the first select box of a chain where the selection in box 1 determines the available options in the second select box.
-chain2 is used for the second select box of a chain where the selection in box 2 determines the available options in the third select box.
-if you only need two chained selects, you will only use chain1, if you need more than three, you can just add more (follow along in the code to add the appropriate information)


/include/business_logic.php
Look for case “select_single”: inside the build_form function section (on or about line 1148).
Copy the ENTIRE case for select_single (up to the break on or about line 1262) and copy it 2X either before or after the existing case. Change one to read case “chain1”: and the other to read case “chain2”:.

In case “chain1”:, add the text in bold:
[pre]case "chain1":
$form .= "<td class=\"td_input_form\">".$select_type_select." –NOBR--
<select name=\"".$field_name_temp."\" onchange=\"getMaincodeList(this)\">";
// first part of the second coloumn of the form[/pre]

In case “chain2”:, add the text in bold:
[pre]case "chain2":
$form .= "<td class=\"td_input_form\">".$select_type_select." –NOBR--
<select name=\"".$field_name_temp."\" onchange=\"getSubcodeList(this)\">";
// first part of the second coloumn of the form [/pre]

There may be better ways of doing this, but I took the quick and dirty way of copying/pasting.

Replace all other instances of case “select_single”: (EXCEPT THE ONE IN build_form above) with:
[pre]
case "select_single":
case "chain1":
case "chain2":
[/pre]

On or about line 3899, in the “function create_internal_table” section, add the following bold part:

[pre] type_field ENUM('text','textarea','rich_editor','password','insert_date','update_date', –NOBR--
'date','select_single','chain1','chain2','generic_file','image_file','ID_user','unique_ID') –NOBR—
NOT NULL default 'text', [/pre]


Obviously, your database table(s) must be set up in such a way that will work in a chained select situation with the appropriate references. I’m not providing instructions on this as there are too many variables on how this could be set up and each individual will need to create their own for each situation.


Get the Ajax Chained Select code
The actual code for the chained select was dl from http://www.dhtmlgoodies.com/index.html?whichScript=ajax_chained_select and then modified for my use. In addition to the files outlined below, you will also need the actual ajax.js file/code. This is available for download from the dhtmlgoodies site linked above (in the section titled Download Javascript file).

Create a file named ajax_chain.js in the same directory as your index.php file using the following code (the bold words are the actual database field names which you must change – any of the other variables, you can name then whatever you want as long as everything still matches):

[pre]//START
/************************************************************************************************************
Ajax chained select
Copyright (C) 2006 DTHMLGoodies.com, Alf Magne Kalleland

This library is free software; you can redistribute it and/or
modify it under the terms of the GNU Lesser General Public
License as published by the Free Software Foundation; either
version 2.1 of the License, or (at your option) any later version.

This library 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
Lesser General Public License for more details.

You should have received a copy of the GNU Lesser General Public
License along with this library; if not, write to the Free Software
Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA

Dhtmlgoodies.com., hereby disclaims all copyright interest in this script
written by Alf Magne Kalleland.

Alf Magne Kalleland, 2006
Owner of DHTMLgoodies.com


************************************************************************************************************/
var ajax = new sack();

function getMaincodeList(sel)
{
var groupbox = sel.options[sel.selectedIndex].value;
document.getElementById('cause').options.length = 0; // Empty city select box
if(groupbox.length>0){
ajax.requestFile = 'getCause.php?groupbox='+groupbox; // Specifying which file to get
ajax.onCompletion = createMaincodes; // Specify function that will be executed after file has been found
ajax.runAJAX(); // Execute AJAX function
}
}

function createMaincodes()
{
var obj = document.getElementById('cause');
eval(ajax.response); // Executing the response from Ajax as Javascript code
}

function getSubcodeList(sel)
{
var codebox = sel.options[sel.selectedIndex].value;
document.getElementById('subcause').options.length = 0; // Empty component select box
if(codebox.length>0){
ajax.requestFile = 'getsubCause.php?codebox='+codebox; // Specifying which file to get
ajax.onCompletion = createSubcodes; // Specify function that will be executed after file has been found
ajax.runAJAX(); // Execute AJAX function
}
}

function createSubcodes()
{
var obj = document.getElementById('subcause');
eval(ajax.response); // Executing the response from Ajax as Javascript code
}
//END
[/pre]

Be sure to reference the files containing the DB and PHP code (getCause.php and getSubcause.php) to the actual location you will be placing them.


Below is the code for my DB/PHP files – references must be changed accordingly to match your installation (bold items). Note that I neglected to use the DaDaBIK db_functions_adodb.php codes within this as I was under the gun to get this working so this is a quick and dirty connect to db and get data. If anyone want to get these two files set up to work within the DaDaBIK framework, be my guest. I will continue to plug away at this, but for now, this is what I’ve got.

getCause.php:
[pre]<?php

if(isset($_GET['groupbox'])) {

$DatabaseLink = mysql_connect('localhost', 'dbuser', 'dbpassword');
if(!$DatabaseLink)
{
print("Unable to connect to database!\n");
exit();
}

//select test database
if(!(mysql_select_db($_GET['db_name'], $DatabaseLink)))
{
print("Unable to use the test database!\n");
exit();
}
$query = "SELECT maincodename,maincodeID FROM maincodes WHERE repArea = ".$_GET['groupbox'];

$res = mysql_query($query) or die(mysql_error());
echo "obj.options[obj.options.length] = new Option('Select:','');\n";
while($row = mysql_fetch_array($res)){
echo "obj.options[obj.options.length] = new Option('".$row["maincodename"]."','".$row["maincodeID"]."');\n";
}
}
?> [/pre]

getSubcause.php:
[pre]<?php

if(isset($_GET['codebox'])) {

$DatabaseLink = mysql_connect('localhost', 'dbuser', 'dbpassword');
if(!$DatabaseLink)
{
print("Unable to connect to database!\n");
exit();
}

//select test database
if(!(mysql_select_db($_GET['db_name'], $DatabaseLink)))
{
print("Unable to use the test database!\n");
exit();
}
$query = "SELECT subcodename,subcodeID FROM subcodes WHERE maincoderef = ".$_GET['codebox'];

$res = mysql_query($query) or die(mysql_error());
echo "obj.options[obj.options.length] = new Option('Select:','');\n";
while($row = mysql_fetch_array($res)){
echo "obj.options[obj.options.length] = new Option('".$row["subcodename"]."','".$row["subcodeID"]."');\n";
}
}
?> [/pre]


***** /include/header.php
Just before the closing </head> in the header.php file, add the following two lines:

[pre]<script type="text/javascript">
var db_name = "<? echo $db_name; ?>"; // transfers the db_name variable to JS so it can be used win ajax code
</script>
<script type="text/javascript" src="/dir/path/to/ajax.js"></script>
<script type="text/javascript" src="/dir/path/to/ajax_chain.js?db_name=<? echo $db_name; ?>"></script>[/pre]


Through admin.php interface configurator for the table where you want the chained select, make the following changes:

For the purpose of this example, we will call the first select box for the chain SEL1, the second SEL2 and the third SEL3.

Change the field type for SEL1 to chain1.
Change the field type for SEL2 to chain2.
Leave the field type for your LAST one (SEL3) as select_single.
Ensure the correct data is linked for each of these fields – same as usual.

 

[cotp]

Hello DebbieS
and first of all thanks for taking the time of this explaination.

If your (or I) get the time, I think it could be a great idea to transform this into a "MOD" as I do with my phpbb forum.

Putting things in order for people to install for example with

*** OPEN ***
*** FIND***
*** AFTER ADD **
instruction types...

since I find it a bit confusing right now (the lines don't match for example...)

Now I still am confused in some changes I need to make to make it work.

Here are my questions/doubts:

1) When you say "replace all other instances of case “select_single”: (EXCEPT THE ONE IN build_form above)" not sure what to do

2) In ajax_chain.js what should we replace ('subcause'). and 'cause' by ?

3) In getCause.php what should we replace SELECT maincodename,maincodeID FROM maincodes WHERE repArea = ".$_GET['groupbox']; by ?

4) Same question for
getSubcause.php

5) Here is my configuration (simplified):
a)I have a table people with:
id_people
name
id_festival
id_function

b) a table festival with:
id_festival
festival name

c) a table function with:
id_function
function name

So I would like the name of festival and function appear in the first table in a drop down...

6) What do we need to do if we have other table to where we need to add such drop downs ?

Thanks in advance for your feedback and help

Cotp

 

[DebbieS]

cotp wrote:

> Here are my questions/doubts:
>
> 1) When you say "replace all other instances of case
> “select_single”: (EXCEPT THE ONE IN build_form above)" not sure
> what to do

That means that every instance of case "select_single": should be changed to

case "select_single":
case "chain1":
case "chain2":

EXCEPT for the one inside the function build_form because that is where the additional cases for chain1 and chain2 were created.


> 2) In ajax_chain.js what should we replace ('subcause'). and
> 'cause' by ?

Those are the field names from your table which you want to use the chain on. What you change them to depends on your specific field names.


> 3) In getCause.php what should we replace SELECT
> maincodename,maincodeID FROM maincodes WHERE repArea =
> ".$_GET['groupbox']; by ?
>
> 4) Same question for
> getSubcause.php

maincodename,maincodeID are the fields that I'm getting from the table named maincodes. repArea is the field which determines which set of maincodenames will be returned when the selection is made in the first chain field. What you change these to depends on what your table and field names are.


> 5) Here is my configuration (simplified):
> a)I have a table people with:
> id_people
> name
> id_festival
> id_function
>
> b) a table festival with:
> id_festival
> festival name
>
> c) a table function with:
> id_function
> function name
>
> So I would like the name of festival and function appear in the
> first table in a drop down...

Your function table would need an additional field where the id_festival value can be stored so that the chain has some reference to select specific entries from it.

id_function
festivalRef - make a select single referencing the festival table.
functionName


> 6) What do we need to do if we have other table to where we
> need to add such drop downs ?

Add more chain types I suppose or re-use field names in tables. To allow the chain to work in my subcodes table, for example, I use the same cause / subcause field names so the chain works there also.
I didn't put any thought into making it work for multiple field names as I was under the gun to get this working. I don't know if it is possible to have this work without specific field names in the code. Nor do I know when I may have time to investigate the possibility. It may not be an easy task since the field names will have to be retreived from the DB and transferred from PHP to JavaScript or vice versa (I could be wrong). This JS/AJAX/PHP combination stuff is still pretty new to me.

Hope this helps.

 

[simsalabim_new]

Hey Debbie,

I would like to have this "chain-feature" too ...

But I'm stuck at:
On or about line 3899, in the “function create_internal_table” section, add the following bold part:

My function create_internal_table looks like this:
(And as you can see, the part where I could add "type_field ENUM('text', ... " etc. is turned into comment line)

[pre]
/* 4.0 */
/*
function create_internal_table($table_internal_name)
// goal: drop (if present) the old internal table and create the new one.
// input: $table_internal_name
{
global $conn, $quote;

// drop the old table
$sql = "DROP TABLE IF EXISTS ".$quote.$table_internal_name.$quote;
$res_table = execute_db($sql, $conn);

// create the new one
$sql ="CREATE TABLE ".$quote.$table_internal_name.$quote." (
name_field varchar(50) NOT NULL default '',
label_field varchar(255) NOT NULL default '',
type_field ENUM('text','textarea','rich_editor','password','insert_date','update_date','date','select_single','generic_file','image_file','ID_user','unique_ID') NOT NULL default 'text',
content_field ENUM('alphabetic','alphanumeric','numeric','url','email','html','phone','city') NOT NULL DEFAULT 'alphanumeric',
present_search_form_field ENUM('0','1') DEFAULT '1' NOT NULL,
present_results_search_field ENUM('0','1') DEFAULT '1' NOT NULL,
present_details_form_field ENUM('0','1') DEFAULT '1' NOT NULL,
present_insert_form_field ENUM('0','1') DEFAULT '1' NOT NULL,
present_ext_update_form_field ENUM('0','1') DEFAULT '1' NOT NULL,
required_field ENUM('0','1') DEFAULT '0' NOT NULL,
check_duplicated_insert_field ENUM('0','1') DEFAULT '0' NOT NULL,
other_choices_field ENUM ('0','1') DEFAULT '0' NOT NULL,
select_options_field text NOT NULL default '',
primary_key_field_field VARCHAR(255) NOT NULL,
primary_key_table_field VARCHAR(255) NOT NULL,
primary_key_db_field VARCHAR(50) NOT NULL,
linked_fields_field TEXT NOT NULL,
linked_fields_order_by_field TEXT NOT NULL,
linked_fields_order_type_field VARCHAR(255) NOT NULL,
select_type_field varchar(100) NOT NULL default 'is_equal/contains/starts_with/ends_with/greater_than/less_then',
prefix_field TEXT NOT NULL default '',
default_value_field TEXT NOT NULL default '',
width_field VARCHAR(5) NOT NULL,
height_field VARCHAR(5) NOT NULL,
maxlength_field VARCHAR(5) NOT NULL default '100',
hint_insert_field VARCHAR(255) NOT NULL,
order_form_field smallint(6) NOT NULL,
separator_field varchar(2) NOT NULL default '~',
PRIMARY KEY (name_field)
) TYPE=MyISAM
";
$res_table = execute_db($sql, $conn);
} // end function create_internal_table
*/

/* 4.0 */
function create_internal_table($table_internal_name)
// goal: drop (if present) the old internal table and create the new one.
// input: $table_internal_name
{
global $conn;

$data_dictionary = NewDataDictionary($conn);

drop_table_db($conn, $data_dictionary, $table_internal_name);

$fields = "
id_field I NOTNULL PRIMARY AUTOINCREMENT,
name_field C(50),
label_field C(255) DEFAULT '' NOTNULL,
type_field C(50) DEFAULT 'text' NOTNULL,
content_field C(50) DEFAULT 'alphanumeric' NOTNULL,
present_search_form_field C(1) DEFAULT '1' NOTNULL,
present_results_search_field C(1) DEFAULT '1' NOTNULL,
present_details_form_field C(1) DEFAULT '1' NOTNULL,
present_insert_form_field C(1) DEFAULT '1' NOTNULL,
present_ext_update_form_field C(1) DEFAULT '1' NOTNULL,
required_field C(1) DEFAULT '0' NOTNULL,
check_duplicated_insert_field C(1) DEFAULT '0' NOTNULL,
other_choices_field C(1) DEFAULT '0' NOTNULL,
select_options_field X DEFAULT '' NOTNULL,
primary_key_field_field C(255) DEFAULT '' NOTNULL,
primary_key_table_field C(255) DEFAULT '' NOTNULL,
primary_key_db_field C(50) DEFAULT '' NOTNULL,
linked_fields_field X DEFAULT '' NOTNULL,
linked_fields_order_by_field X DEFAULT '' NOTNULL,
linked_fields_order_type_field X DEFAULT '' NOTNULL,
select_type_field C(100) DEFAULT 'is_equal/contains/starts_with/ends_with/greater_than/less_then/is_null/is_empty' NOTNULL,
prefix_field X DEFAULT '' NOTNULL,
default_value_field DEFAULT '' X NOTNULL,
width_field C(5) DEFAULT '' NOTNULL,
height_field C(5) DEFAULT '' NOTNULL,
maxlength_field C(5) DEFAULT '100' NOTNULL,
hint_insert_field C(255) DEFAULT '' NOTNULL,
order_form_field I NOTNULL,
separator_field C(2) DEFAULT '~' NOTNULL
)
";

create_table_db($conn, $data_dictionary, $table_internal_name, $fields);

} // end function create_internal_table
[/pre]

Could you tell me what I need to do here ... ?

Thanks in advance !

 

[simsalabim_new]

Hey,

it's me again ...

I also have some trouble to understand what I need to replace for

'cause' 'subcause'
'maincodename,maincodeID FROM maincodes WHERE repArea'
'subcodename,subcodeID FROM subcodes WHERE maincoderef'

Sorry but I don't understand this ...

Is there maybe an easier way ... since your post from 09-07-07 ?
Maybe something somebody stupid can handle too ?

Thanks in advance !

 

[DebbieS]

cause and subcause are the field names populated with the data from the tables.

maincodename, maincodeID and repArea are field names from the first table maincodes. This is the table that drives the first drop-down/select_single box. User selects maincode and then the associated subcodes are displayed using the next bit of code.

subcodename, subcodeID and maincoderef are field names from the second table subcodes. After the user selects the main code (above), the system retreives the subcode list based on items where the maincoderef is equal to the value in the first selection box.

In my case, I have to have repArea designate which maincodes can be presented based on user login (leave this part out if you don't need it). The second table, subcodes, includes a maincoderef field which references the maincodes table. When user selects maincode item 1, the script says, ok, go get the subcodename,subcodeID list where the maincoderef EQUALS 1.

I don't really know how else to explain this. Perhaps if you post or send more detailed information about your DB and table setup, I may be able to give you better instructions. It all comes down to providing the correct field and table names for YOUR db into the spots I've indicated in bold. The instructions provided here are more than what I got when I dl the script -- a lot of table/field name trial & error to get it working.