 |
|
|
|
 |
||
|
||
|
|
|
|
Revisiting the Database Applicationby Brent MichalskiOct. 23, 1998 We completed our database application a couple of weeks
ago, but after completing the project, I identified a few changes which
would make it even better. I found that although the application could
do many things, it couldn't sort. The data can be hard to read if it is
not in some type of order. This week we will add code to sort the database,
and produce a more complete database application.
Sorting and tweakingThe code we will look at in this article adds the ability to sort records on whatever field the user chooses.I have also made a few tweaks to the database application, to make it more portable and easier to install. I felt that it was rather annoying to require a complete database.cgi file for each different database that you want. So, I made some modifications and now all you need to do is create a 3 line configuration file and then use that file as an option to the call to the database (database.cgi?config.txt). I am not going to cover the changes made to accomodate the new configuration file in this article. I put comments in the configuration file that explain what each line does. If you've been following along with our progress on this project, the changes from the previous version should be pretty obvious when you run the application and view the complete program code. We have listed all the previous pieces of this project in the sidebar at right. Diving inI struggled with the sorting routine for quite a while before I got it to work correctly. If I hadn't had such a hard time with it, I would have implemented it in a previous column. Although it doesn't take a lot of code to do our sorting, it is quite complex and very picky.I want to thank Tom Christiansen and Nathan Torkington for writing the excellent Perl Cookbook. The Perl Cookbook pointed me in the right direction to get the sorting code working. If you haven't seen a copy of it, find it at your local bookstore or library and read it. It is a great book! Since the program has grown again, I am presenting the code a bit differently this week to save space. I am only going to show and comment on the lines that have changed or been added for our sorting routine. The complete database script code is available in a separate file. I have numbered the lines of code, the line numbers are not part of the program. The line numbers simply make it easier for me to talk about the program. ...
19: $sort = $q->param(sort_on);
...
40,55 & 87: &sort_db;
...
313: print<<HTML;
314: </FONT></TD></TR><TR>
315: <TD><FONT FACE="ARIAL" SIZE=2><B>
Sort On:</B></FONT></TD>
316: <TD><FONT FACE="ARIAL" SIZE=2>
317: HTML
318: $x=0;
319: $CH = "CHECKED";
320: foreach $field (@fields){
321: print "<INPUT TYPE=radio NAME=sort_on
VALUE=$x $CH>\u$field";
322: $x++;
323: $CH="";
324: }
...
363: sub sort_db{
364: foreach $curr (@results){
365: ($key,@rest) = split(/\|/, $curr);
366: $max = @fields;
367: $code='$record{$key} = { key => "$key", ';
368: for($x=0;$x<$max;$x++){
369: $code .= "\$fields[$x] => \"\$rest[$x]\",\n";
370: } # End of for
371: $code .= '};';
372: eval $code;
373: } # End of foreach
374: $sort_on = "$fields[$sort]";
375: @results=();
376: foreach $rp (sort { $a->{$sort_on} cmp
$b->{$sort_on} } values %record){
377: $new_rec = $rp->{key};
378: for($x=0;$x<$max;$x++){
379: $new_rec .= "\|$rp->{$fields[$x]}";
380: } # End of for
381: push @results, $new_rec;
382: } # End of foreach
383: } # End of sub sort_db
Line-by-line explanationLine 19: We added this line to get the field the user wants to sort on from the calling web page. It is passed from the new radio button added to the form in lines 313-317.Lines 40,55 & 87: These lines all call the sort_db subroutine. The first 2 call it from within our modify subroutines and the last one calls it from our search subroutine. Lines 313-317: These lines are a here document that prints out the code to add the extra row to our user input page. Line 318: Initializes our $x variable to 0. Line 319: Initializes our $CH variable to the value CHECKED. We do this so that when the web page is displayed, the first item in the radio button group is automatically selected. Line 320: Begins a foreach loop that goes through each of the items in @fields and stores them in the variable $field. Line 321: Prints the HTML for the radio button group, one radio button at a time. Each time through the loop, a new radio button is created. Line 322: Increments our $x variable. We use the $x variable as the value of the radio button. This value is then passed to the program in line 19. Line 323: Sets our $CH variable to nothing. Since we only wanted the first item in our radio button group to be selected, we set this value to nothing and for the remaining passes through the loop, the radio buttons are not selected. Line 324: Ends our foreach loop. Line 363: Begins our sort_db subroutine. Line 364: Begins a foreach loop that goes through each of the items in @results and stores the value in $curr. Remember, the array @results is created by our search_database subroutine, it contains all of the matching records. Line 365: Splits the current record into a variable called $key and an array called @rest. We now have the record key stored safely in a variable and the fields of data stored in an array. Line 366: Creates a variable called $max and sets its value to the number of elements in our @fields array. The @fields array is created in our new configuration file. Line 367: Begins the creation of a block of code which becomes our record. This record is very similar to a struct in C. Line 368: Begins a for loop> that goes through each of our fields and creates another line in our code block. Line 369: Appends data onto the end of our $code variable. Line 370: Ends our for loop. Line 371: Finishes off our $code variable. We now have a variable called $code that looks something like this: $record{$key} = {
key => "$key",
$fields[0] => "$rest[0]",
$fields[1] => "$rest[1]",
$fields[2] => "$rest[2]",
$fields[3] => "$rest[3]",
$fields[4] => "$rest[4]",
};
Line 372: evals the code in the variable
$code.
eval takes the code passed to it, and executes it as if it were
a tiny Perl program. We create the code this way and then
eval
it in order to take an array of almost any length, @fields, and
make it into a record.
Line 373: Ends our foreach loop. Line 374: Creates a variable called $sort_on. It gets the value of $fields[$sort] which is interpolated and turned into the field name of what will be sorted on. For example, if $field[2] contains Last Name, and $sort equals 2, then once we execute this line of code, the value stored in $sort_on would be Last Name. Line 375: Empties our @results array. This is the same array that was filled by our search_database subroutine. We have to empty it, otherwise we will get duplicates of every item when we push values onto it below. Line 376: Begins a foreach loop that goes through each of the values in %record and sorts them on the field we passed in the variable $sort_on. A record pointer, $rp, is created each time through the foreach loop. We then access the data via the record pointer. Line 377: Creates a new variable called $new_rec. This variable becomes the new record once we are done with it. We take each of the fields and reconstruct the record into the format that our database expects it. Remember that we split the record above, here we are un-splitting it and initially setting it to the key value of the current record. Line 378: Begins a for loop that creates our new record. Line 379: Appends the next field onto the end of our $new_rec variable. Notice that we put a pipe symbol |, before each value. We do this because we chose to use the pipe symbol as our field delimiter in the database. Line 380: Ends our for loop. Line 381: pushes the new record onto the @results array. This reconstructs the @results array and places the items in the array in the order we wanted them sorted. Line 382: Closes the foreach loop. Line 383: Closes the sort_db subroutine. Wrapping it upWe've once again finished our database application, now with sort features! This program will work on either UNIX or NT. Just remember that if you are on NT, you need to put complete paths with drive letters in the configuration file.When programming, be persistent! I knew exactly what I wanted this sorting subroutine to do, but it didn't work the way I thought it should. Persistence pays off. If I had given up, the database application wouldn't be complete. Even programmers with a lot of experience can struggle to get code working, but by struggling you learn much more than you can if someone simply tells you how to do it and not why it works. No pain, no gain :-) See you next week!
Source Code for Revisiting
the Database Application
|
|
|
|
|
Web Techniques and Web Design and Development copyright © 1995-99 Miller Freeman, Inc. ALL RIGHTS RESERVED |
|
