Using OnChange with PHP
NOTE: This code has only been tested with Mozilla and Firefox under Linux based systems!
Introduction
I had a surprisingly hard time trying to find any information about this seemingly tame and uninteresting idea. Consider this: I am writing a Knowledge Base application using a PHP frontend with a PostgreSQL backend. On a data-entry form, I want two drop-down lists. Change the first drop-down list (the "category" of the article), and a look-up is performed, populating the second drop-down list with "sub-categories", which are obviously dependent on the main category. "I know", I thought, "I'll use a <select> within a <form> and then link OnChange to some piece of PHP or JavaScript". Not so easy!
Even when I did find an article pointing me in the right-direction, it still didn't work for me, and I ended up brewing a PHP/JavaScript solution that works very well indeed, and involves re-submitting the page to itself when the drop-down list value is changed. Then, a button is clicked when the data is finally ready for addition to the database, and a different submission routine is called, committing the data to the database.
Structure
Before I begin discussing my solution, it would be helpful to highlight a little about the design of the Knowledge Base application, and the underlying database structure, itself. The actual database structure is very simple indeed - three tables, category, sub_category and article provide all the functionality I need (at the moment!). Note: the formatting here is none-too-good. Download the PDF version or the OOo document for nicely formatted text!
Table: category Field Type Constraints/Notes id int8 primary key not null category varchar unique not null description varchar
Table: sub_category Field Type Constraints/Notes id int8 primary key not null sub_category varchar unique not null category varchar foreign key category.category description varchar
Table: article Field Type Constraints/Notes id int8 primary key not null category varchar foreign key category.category sub_category varchar foreign key sub_category.sub_category title varchar short_description varchar description varchar url varchar date varchar see_also varchar
There are various other constraints and sequences set up in the database. However, most of this in neither here nor there and will be irrelevant for most of this discussion, although you should pay attention to the category and sub_category tables, as these contain the data that we want to have fill our drop-down lists.
Each "form" of the Knowledge Base application is a PHP/HTML file, and they are all processed by a central file named (imaginatively...) process.php. Each individual form contains a hidden input field of the form <input type="hidden" name="action" value="add_category">. This enables us to have one core PHP file process each form (by using the $_POST[ 'action' ] value to determine which request was made).
The Problem
Setting up forms to add categories and subcategories to the database was as you'd imagine very easy to do. The problem came when setting up a form to add an actual Article to the Knowledge Base. I wanted to have the user select a category from a drop-down <select> list, which would then populate the subcategory drop-down list dynamically. Thusly if the user changed his/her mind and modified the selection in the category list, the subcategory list would then update itself with new subcategories based upon the category selection. I thought this would be relatively easy too ? and once I found out how, it actually was!
The Solution
Here is the code (trimmed) for add_article.php
1 <html>
2 <head>
3 <title>ZB Knowledge Base: Add Article</title>
4 </head>
5 <body>
6 <?php
7 $connection = pg_pconnect( "dbname=knowledge_base user=kevin host=localhost" );
8
9 if ( ! $connection ) {
10 print( "Cannot connect to database" );
11 exit;
12 }
13
14 $value = "";
15 if ( isset( $_POST[ 'category' ] ) ) {
16 $value = $_POST[ 'category' ];
17 }
18 ?>
19. <script language="JavaScript">
20 function resubmit()
21 {
22 document.myform.action="add_article.php";
23 document.myform.submit();
24 }
25 function process()
26 {
27 document.myform.action="process.php";
28 document.myform.submit();
29 }
30 </script>
31 <b>Add New KB Article</b><br /><br />
32
33 <form name="myform" method="post" action="process.php">
34 <input type="hidden" name="action" value="add_article" />
35
36 Category: <select name="category" onchange="resubmit()">
37 <?php
38 $query_result = pg_exec( $connection, "SELECT * FROM category" );
39
40 for ( $count = 0; $count < pg_numrows( $query_result ); $count++ )
41 {
42 $category = pg_result( $query_result, $count, 1 );
43 if ( $count == 0 )
44 {
45 $init_cat = $category;
46 }
47 if ( $category == $value )
48 {
49 print( "<option selected>$category</option>\n" );
50 $selected = $category;
51 }
52 else
53 {
54 print( "<option>$category</option>\n" );
55 }
56 }
57 ?>
58 </select>
59
60 Sub Category: <select name="subcat">
61 <?php
62 if ( $value == "" )
63 {
64 $query_result = pg_exec( $connection,
65 "SELECT * FROM sub_category WHERE category = '$init_cat'" );
66
67 for ( $count = 0; $count < pg_numrows( $query_result ); $count++ )
68 {
69 $subcat = pg_result( $query_result, $count, 3 );
70 print( "<option>$subcat</option>\n" );
71 }
72 } else {
73 $query_result = pg_exec( $connection,
74 "SELECT * FROM sub_category WHERE category = '$selected'" );
75
76 for ( $count = 0; $count < pg_numrows( $query_result ); $count++ )
77 {
78 $subcat = pg_result( $query_result, $count, 3 );
79 print( "<option>$subcat</option>\n" );
80 }
81 }
82 ?>
83 </select>
84 <br /><br />
85 <!-- OTHER FORM ITEMS HERE -->
86 <input type="button" name="post_data" value="Add It!" onclick="process()" />
87 </form>
88 </body>
89 </html>
As you can see from the above code, there is only a small amount of JavaScript required to perform the two different submissions that are needed for this script to function.
Allow me to explain a little about the logic of the above script. When the form is first loaded, the first drop-down (category) is populated. As this is the first time that the form is loaded, we use the (quite certain) assumption that the user has not yet selected a category. Therefore, the initial category is set to the first category returned from the query to the category table. Then, the script goes on to populate the second drop-down (the subcategory) list. If this is the first time that the script is run, the subcategories are selected based on the intial category.
When the category is changed, the "onchange" event is fired. This calls the JavaScript resubmit() function, which POSTs the script to itself. When the script then reloads, the value of the previously selected "category" is remembered, and the category and subcategory lists are populated accordingly.
Hopefully this will become a lot clearer if I walk through the code!
Lines 6 through 18 contain the first block of PHP code. The initial connection to the database is made with the usual pg_pconnect() call. If a connection cannot be established, an error is printed and we exit the block. If this is the first time that the form has been loaded, then $_POST[ 'category' ] will NOT be set. Therefore, $value is NOT assigned a value in line 16 and is left blank (see line 14).
Lines 19-30 define two JavaScript functions, resubmit() and process(). The former is called when the category <select>'s onchange event is fired, and posts the page to itself. This causes the above code in lines 6-18 to check the previously set value of the category <select>, set the corresponding <option selected> and then populate the subcategory <select> in later code. The latter function ( process() ) is called when the button at the end of the form is clicked (via its onclick event) and causes the forms data to be posted to process.php for processing and committing to the database.
Lines 36-58 are the meat of the category <select>. On line 43, the if clause sets the initial category (init_cat) to the first category value returned from the query in line 38. This is used during the forms first load by the subcategory code in order to populate it. The logic here is that the first category value will be the "visible" <option> of the category <select> when the form is loaded for the very first time. If this is a subsequent invocation of the form, then the if clause on line 47 is true (as $value would have been set on line 16), and the <option> is set selected, and variable $selected is set to the value of $category (which is used by the subcateogy <select>). Otherwise, the <option> is printed normally, and no special action is taken.
Lines 60-83 contain the code for the subcategory <select> to be rendered. On line 62, we check to see if $value is blank. If it is, we know that this is the forms initial invocation, and therefore we can use the value of $init_cat in the query on line 64. The <select> is then populated accordingly on lines 67-71. Otherwise, we know that the form has been posted, and we therefore know that $selected will contain the value of the currently selected category <option>. This value is used in the query on line 73, and the subcategory <select> is populated according to this selected value.
Line 86 is our forms "submit" button, which calls the process() JavaScript function on line 25, which posts our forms data to process.php for processing and committing to the database.
And we're done!
Conclusion
A little "hackish" in places, this script is totally fine for my non-mission-critical Knowledge Base application. For other uses, however, some refinement would be necessary! Having said that, this code works very reliably and efficiently for me, and has shown me a few handy tips and tricks which I will no doubt employ in further projects.
Thank you for reading, I hope it's been interesting and useful!
Visit me on the web at http://www.zazzybob.com for all things Linux/UNIX
