Document Home
Previous Managing Teams and Players
Previous Developing a Generic Editing Table Subroutine
Previous Managing Team Records
Previous Managing Player Records
Samples for this section
Associating Players with Teams
Now that I have a way to manage both team and player records, I obviously need to develop a way to associate the two. There is a little more involved with this than it would seem at first blush. When I sketched out the design of the underlying database some time ago, I thought to maximize the flexibility of the system by allowing any player to be assigned to any team for any given game. Obviously, this has to be the case. Even in the most static teams, players get injured, cannot continue, and are replaced by others. On the other hand, leagues invariably operate with fixed rosters that can be modified only according to a pre-defined set of rules. As a result, requiring that players be assigned to teams for each game would impose a maintenance burden that would be both onerous and unnecessary.
Therefor, a reasonable compromise involves the provision of the capacity to both assign players to and remove them from teams, and track the date and type (addition, removal) of each change. A more detailed implementation would provide the capability to annotate each of these changes, but at this point I am not going to worry about that. As long as the structure I create is capable of accomodating that it can be added at a later date if I so desire.
The best location at which to add a column representing team assignment is in the participant table, which now includes a column to hold the team_id of the assigned team. I have also added the part_change table to the database, with the following structure.
(part_id char(3),
team_id char(3),
type char(1),
date date,
notes text),
user_id char(5))
The roles of the columns in this table, including the team and participant id's, the type of change (addition, removal), the date of the change, and any associated notes, should be self-evident. An alternative formulation of the team assignment structure might limit itself to a variant of this table, with the most recent record representing the player's current team association. This would be the fully-normalized version of this aspect of the database structure. From the standpoint of the subject domain, however, the primary focus is on an individual's current team association rather than on all of the associations between an individual and teams. The same information is present in each structure, but the one I will implement can be much more readily queried for the current team association, which will be the basis for many of the reports and summaries that will be generated by the system. (I will leave the construction of SQL queries that construct the current association between teams and players from the fully-normalized database structure to the user as an exercise.) The cost of the structure I will employ is three bytes for each participant. To put this in perspective, major league baseball currently has 30 teams, each of which has a 25-man roster (for most of the season). Implementation of this structure for the entire 750 man active roster of major league baseball would impose a storage premium of just over two Mb., a trivial amount.
While I have added the team column to the participant table, I do not want to use the page developed in the last chapter to maintain that association, simply because it would be an awkward manner in which to maintain that information and, because it is awkward, prone to error. After a bit of reflection, I decided on the general layout to the right as the basis for the interface. As this indicates, what I have in mind is displaying the players who have already been associated with a given team on the left-hand side of the page, and the players who have not been assigned to any team displayed on the right-hand side. Each line will have an associated radio widget that will allow players listed on the left-hand side to be removed from the team and players listed on the right to be added to the selected team. Obviously, in circumstances in which the participant table has been fully-populated with perhaps 200 records and few, if any, participants have been assigned to teams, the page generated here will be fairly long as all of those unassigned participants are listed down the right-hand side of the page. If the potential number of records to be listed in this fashion were to extend into the thousands I would probably incorporate some sort of alphabetic selection mechanism to force the selection of smaller sets of data, but inasmuch as the result set displayed here will be alphabetically-ordered it is my opinion that the incorporation of such a mechanism would serve only to create a much more confusing interface.
One problem that I do see with this general inteface is that there is no straightforward way to track down the team with which a given individual is associated once that individual has been associated with a team, short of pulling up each team and looking through the list of the individuals assigned. While it would be possible to implement a search mechanism here, now that I have added the team column to the participant table it makes more sense simply to display the associated team in the player management table, which is also alphabetically-ordered. Given that the implementation of a search mechanism would require that the user jump through about as many hoops as simply going to the player management screen when appropriate to determine that association, this course seems preferable. At this point, however, I have not yet decided whether I want to give the user the ability to change that association from that display, or to simply view what is stored there. I will probably address that modification at the end of this chapter.
As before, the process that I will follow here will first involve putting together the visual structure of the page, then placing within it the various components that implement the desired functionality. The first component I implemented was the team selection widget, which is very similar to the month and year selection widget that heads the calendar drawn as the initial page in the schedule maintenance process.
##if there is a team parameter, assign it to the $team scalar. then draw a single element table in which a
##select mechanism allowing team selection is displayed
$team=$$params{'team'}[0];
$$r->print("<form action='/bb_app/league/la-team-associate' method='post'>");
$$r->print("<br><center><table width='25%' border='2' $colors><tr><td><center><h2>Select Team</h2><br>");
sel_box($select[6],$r,'team','','',$team);
$$r->print("<br><input type='submit' name='button' value='Select'>");
$$r->print("</center></td></tr></table></center></form>");
This chunk of code implements a select box widget within a centered, one element table to frame the widget. The table itself is within a form which specifies the same URI as the menu selection that leads to the generation of this page. Selection of a team and clicking on the submit button will therefore lead to the re-generation of this page in response to a request that will now include a parameter named "team". If this parameter is present, it is read into the $team scalar. 
The displayed widget will therefore look like the illustration at left on the initial generation, and the illustration at right on successive generations.
Printing the table itself, of course, is no big deal.
$$r->print("<center><table width='96%' border='1' $colors><tr><td width='48%'>");
$$r->print("<center><b>Players Currently Associated with Team</b></center><br>");
$$r->print("</td><td width='48%'>");
$$r->print("<center><b>Players Not Currently Associated with Any Team</b></center><br>");
$$r->print("</tr></td></table></center>");
This snippet produces the following:
The table width chosen, 96%, was intended to use virtually all of the width of the page with a small amount left over to provide some degree of visual separation from the page background. The 48% element widths are used to split the resultant table precisely in half.
It is within the element delimiters that I will include the code that will display the player listings on either side of the table. Before I can do that, however, I have to get the pertinent records from the table. The following lines are therefore now part of BB_STACKED, preparing the database statements that will retrieve the relevant sets of records.
##statements involved with the team association procedure
##select the players associated with a given team
my $part_team=$dbh->prepare("select part_fname,part_lname from participant where team=?");
##select the players not associated with a team
my $no_team=$dbh->prepare("select participant_id,part_fname,part_lname from participant
where team is null order by part_lname,part_fname");
Note the where clause in the second statement. Participant records for which a team value has not been entered do not have whitespace, or blanks, in that column, but rather an entry of nothing, or null. Therefore the pertinent select statement must explicitly take that into account. References to these statement handles are then stored in %db_hash.
participants_on_team=>\$part_team,
not_associated_with_team=>\$no_team)
As I do not want theose statements to execute if a team has not yet been selected, within the subroutine that generates the page, now named associate(), I make the execution of these statements contingent upon a defined value in the $team scalar.
##if the $team scalar is defined, execute the database queries to return players associated with that team and players
##not associated with any team.
my ($team_sth,$noteam_sth);
my ($team_ary,$noteam_ary);
my ($team_rec,$noteam_rec);
if ($team) {
$team_sth=$$db{'participants_on_team'};
$$team_sth->execute($team);
$team_ary=$$team_sth->fetchall_arrayref;
$noteam_sth=$$db{'not_associated_with_team'};
$$noteam_sth->execute;
$noteam_ary=$$noteam_sth->fetchall_arrayref;
Here I have defined the scalars that hold references to the prepared statement handles with a scalar name that includes the string "sth", reflecting the standard convention under which statement handles themselves are named. I do this kind of thing just to keep readers on their toes.
Three possible states determine what is printed within the boxes defined by these table columns on any generation of the page. First, I do not want anything printed if no team has been selected. My aim here is to avoid user confusion: an interface that requires user input before output is displayed should look appropriately empty if no such input has been provided. Second, if a team has been selected and one of the two result sets is empty, I want appropriate text to be displayed in the column to which the empty set pertains. The third state, of course, involves a request with both a selected team and populated result sets.
The first state is easily trapped by testing for the presence of the team parameter, in this case one step removed because at the point at which I determine what to print I am using a scalar to which I assigned that value if it existed. I determine the requisite information to trap the second state by evaluating the arrays returned by the fetchall_arrayref method in scalar context, as a result storing the number of elements in the arrays to the relevant scalars.
$team_rec=@$team_ary;
$noteam_rec=@$noteam_ary;
Therefore, the values in these scalars can be used in concert with the presence or absence of the $team scalar to determine what should be printed, if anything.
$$r->print("<center><table width='96%' border='1' $colors><tr><td width='48%'>");
$$r->print("<center><b>Players Currently Associated with Team</b></center><br>");
if (! $team_rec and $team) {
$$r->print("No players have yet been assigned to this team");
}
elsif ($team_rec) {
foreach my $rec(@$team_ary) {
$$r->print(@$rec->[0],@$rec->[1]);
}
}
The snippet above will produce output for the left-hand side of the page. The first condition will evaluate as true only if there are no elements in the array returned from the query for the players associated with a given team and a team has previously been selected, thus preventing the "No Players ... " message from being spuriously displayed when no team has been selected. If $team_rec contains a non-zero value, the code that displays pertinent values is executed. (As in previous similar circumstances, at this point I am simply printing a couple of elements for each record in the pertinent result set to confirm that the select statements and page generation logic are operating appropriately.) As the $team_rec scalar will only be populated if the $team scalar is defined and not blank (it is possible for a user to submit the default blank value from the team selection widget), it is not necessary to check for the presence of that scalar under this condition.
The code that produces the right-hand side of the table is essentially the same as that which produces the left.
$$r->print("</td><td width='48%'>");
$$r->print("<center><b>Players Not Currently Associated with Any Team</b></center><br>");
if (! $noteam_rec and $team) {
$$r->print("All players have been assigned");
}
elsif ($noteam_rec) {
foreach my $rec(@$noteam_ary) {
$$r->print(@$rec->[0],@$rec->[1]);
}
}
The only differences between the two versions lie in the fact that the operation of the left-hand side is based on the characteristics of the result set returned from the query for participants associated with a given team, while the right-hand side operates on the set of participants with no team value stored.
This produces a page that looks like this
In the current state of this system, this page will be generated regardless of which team is selected, because no players have as yet been associated with any team.
Now I want to put into place a mechanism that will allow records to be selected for the pertinent action on either side of the page. As I have previously, the capability to select records will be provided by radio widgets that are named iteratively as the page is generated. I briefly considered calling edit_table() here to generate the output to be displayed in these columns, but ultimately decided that such would be overkill. My goal is to print enough to identify records, but the selection mechanism itself is the only item on each of these rows for which user input must be possible. I therefore decided to enhance the relatively simple element listings used above to serve this purpose.
While I could have used the same index scalar to generate the names of the radio widgets on either side of the page, as long as I reset it to 0 in between the two while() loops, I decided to use seperate scalars to avoid confusion.
my ($i,$j)=(0,0);
The foreach() loops that iterate over the arrays of references to the rows of the result sets now include lines printing the radio widgets as well as a line printing the participant_id for the given record from the result set.
$$r->print("<b><i>Select to delete from this team</i></b><br>");
foreach my $rec(@$team_ary) {
my $delete_sel='delete_'.$i;
$$r->print("Yes<input type='radio' name=$delete_sel value='yes'>
No<input type='radio' name=$delete_sel value='no' checked>");
$$r->print(@$rec->[1],@$rec->[2]);
$$r->print("<br>");
$$r->print("<input type='hidden' name='exist' value=@$rec->[0]>");
$i++;
}
and
$$r->print("<b><i>Select to associate with this team</i></b><br>");
foreach my $rec(@$noteam_ary) {
my $insert_sel='insert_'.$j;
$$r->print("Yes<input type='radio' name=$insert_sel value='yes'>
No<input type='radio' name=$insert_sel value='no' checked>");
$$r->print(@$rec->[1],@$rec->[2]);
$$r->print("<br>");
$$r->print("<input type='hidden' name='unassign' value=@$rec->[0]>");
$j++;
}
As previously, the logic in these two snippets is exactly the same, they just operate on different structures that are identical in structure but contain different values, and produce page elements with different names.
The first time I went through the initial steps of the association process with this code in place, the page generated looked like this
Once I saw that, I realized that I had neglected to adjust the select statement to exclude the blank record in the table that provides the default blank at the start of any select box. (There is, of course, another way I could have gone about this, by explicitly adding a blank slice to any hash created by make_hash(). Ultimately, each strategy would have its drawbacks, but I feel that it is much more straightforward to simply filter out that record when appropriate than to create a version of make_hash() that includes the capability to generate a hash with an empty slice when the subroutine is called with an appropriate argument. But then again, I may change my mind at some point.) I changed the appropriate line in BB_STACKED to
my $no_team=$dbh->prepare("select participant_id,part_fname,part_lname from participant where team is null
and participant_id <> ' ' order by part_lname,part_fname");
and finished the generation of the page by wrapping the generated table within a form, starting with this line
$$r->print("<form action='/bb_app/league/la-store' method='post'>");
just before the beginning of the table and these three
$$r->print("<input type='hidden' name='task' value='associate'><input type='hidden' name='team' value=$team>");
$$r->print("<br><center><input type='submit' name='button' value='Submit'></center>") if $team;
$$r->print("</form>");
just after it ends. Note that I do not print the submit button unless the $team scalar is present, in effect disabling the form unless it is in a condition appropriate for submittal. In this snippet I also define the hidden fields named "task" and "team". The first will be used to appropriately direct the processing of the request object upon submittal of this form, and the second will provide the team id that should be stored in the participant table for any participants selected on the right-hand side of the page. After adding a page title, the generated page now looks like this:
Now it is time to start putting together a subroutine to process the submitted page. This is, in fact, much easier than processing the pages I developed in previous chapters, primarily because I am not incorporating as many different types of actions onto the submitting page. Inasmuch as I am using different naming schemes for the two sets of widgets that flag records for actions, all I have to do is iterate over the resultant sets of parameters, check for those flagged, and perform the appropriate action when I find one flagged.
The environment necessary to support the actions that will be executed for flagged records must of course include appropriately defined statement handles, and there must also be a condition under the store condition of the league() subroutine in BB_STACKED to direct the processing of the submitted request. The sql statements to be used here are simple, involving modification of only one column in the participant table.
##remove the associated team value for a specified participant_id in participant
my $rem_team=$dbh->prepare("update participant set team=null where participant_id=?");
##add a team value to the participant table for a given participant_id
my $add_team=$dbh->prepare("update participant set team=? where participant_id=?");
While it might appear that I could simply use a single statement here, given that other than the specification of what should be stored in the team column the statements are identical, it is not possible to pass "null" as a value to the placeholder. In this specific instance the error message received indicates that the value is longer than the column width, but this in itself indicates that the statement is not being executed appropriately, trying to stick the string "null" into the column rather than an empty value. In general, DBI documents indicate that it is not possible to bind null values to placeholders. Hence the two statements. While I could cut back to one statement if I were to insert a scalar holding the relevant string ("team=null" or "team=@{$$params{'team'}}[0]"), I would not be able to prepare() that statement and hence would lose the performance advantages that technique affords.
Descriptive names for these two statements are then associated with references to the statements in %db_hash.
remove_team_association=>\$rem_team,
associate_team=>\$add_team
The added condition under the store condition is also straightforward.
elsif ($$params{'task'}[0] =~ /associate/) {
apply_associate(@pass);
}
While I do not need everything I previously rolled into the @pass array, it was very simple to use it here as it does include everything I do need.
At its core, the structure of apply_associate() itself is simple, involving iterating over the range of submitted widgets from either side of the page, firing off the relevant statement whenever a selected record is encountered. The implementation of that logic in code is only slightly more complex than the last sentence.
sub apply_associate {
my ($r,$params,$num_forms,$user_name,$colors,$db,$rev_team)=@_;
my ($i,$j) = (0,0);
while ($i < @{$$params{'removes'}}[0]) {
if (@{$$params{'delete_'.$i}}[0] =~ 'yes') {
my $sth=$$db{'remove_team_association'};
$$sth->execute(@{$$params{'exist'}}[$i]);
}
$i++;
}
while ($j < @{$$params{'additions'}}[0]) {
if (@{$$params{'insert_'.$j}}[0] =~ 'yes') {
my $sth=$$db{'associate_team'};
$$sth->execute(@{$$params{'team'}}[0],@{$$params{'unassign'}}[$j]);
}
$j++;
}
}
After reading in the passed arguments and initializing the $i and $j scalars to 0, the subroutine iterates over the range of integers from 0 to one more than number of items that were displayed on the left-hand side of the page. That number is accessible through its storage in the "removes" hidden field on the submitting page. (Here again the submitting page is generated with loops that increment counters in the final statement within the loop, which means that the stored value is actually one more than the maximum index value assigned to the parameters to which it applies. As I do not currently anticipate generalizing this particular subroutine to a wider context, however, I have simply made sure that the logic here reflects the characteristics of the request object it is processing.) Within the loop, the parameter named with the concantenation of the string "delete_" with the current value of the $i counter is checked for the string "yes". If that condition is met, the reference to the statement handle keyed to the string "remove_team_association" in the hash referenced by $db (%db_hash) is retrieved and executed with the value stored in the $ith element of the parameter array that is keyed to the string "exist" in the %params hash, which represents the pertinent participant_id. This is the same structure I have used previously, with different parameter names. The second loop operates in exactly the same fashion with the names assigned to the widgets generated for the right-hand side. Here the statement handle keyed to "associate_team" also requires the parameter value resulting from the printing of the id for the subject team as a hidden field on the submitting page to have the appropriate team_id to store.
This is all that is required to process the submitted page. Participants can now be associated with a team, and associations removed. I do, however, want to generate the same kind of feedback page I have used previously. Having constructed similar pages, this should seem like old hat to those who have read those previous chapters, so my discussion here will be brief. I have added the creation of a set of scalars to track both the number of times each database statement is executed and the number of sucessful executions.
##initialize counters to track statement executions and successful statement executions
my ($all_deletes,$del_res,$all_associate,$assoc_res)=(0,0,0,0);
Within the loops, the counters are incremented and the accumulator scalars are updated with the $res scalar, which captures the return value from statement execution.
$all_deletes++;
$del_res=$del_res + $res;
and
$all_associate++;
$assoc_res=$assoc_res + $res;
Finally, I print a summary page similar to those I have used previously, making sure that I return the pertinent team id as a hidden field.
$$r->print("<br><br><form action='/bb_app/league/la-team-associate' method='post'><center><table width='50%' border='1'$colors><tr><td><center>");
if ($all_deletes == $del_res) {
$$r->print("<font color='green'>$all_deletes association(s) removed<br></font>");
}
elsif ($all_deletes > $del_res) {
$$r->print("<font color='red'>All specified associations not removed.<br>Contact System Administrator<br></font>");
}
if ($all_associate == $assoc_res) {
$$r->print("<font color='green'>$all_associate association(s) added<br></font>");
}
elsif ($all_associate > $assoc_res) {
$$r->print("<font color='red'>All specified associations not made.<br>Contact System Administrator<br></font>");
}
$$r->print("</center></td></tr></table></center>");
$$r->print("<input type='hidden' name='team' value=@{$$params{'team'}}[0]>");
$$r->print("<br><br>");
$$r->print("<center><input type='submit' value='Re-Display Team'></center></form>");
This generates a page that looks like this:
Since I have printed a hidden field with the name "team" on this page, clicking on the submit button will generate a new page that reflects the subject team's current composition.
While I now have the structure in place to process a submitted page, I have not yet constructed the capability to keep a log of those transactions. Such a log is particularly appropriate in this context, because many leagues have difficulties with teams bring in "ringers" to boost their talent level, and tracking personnel changes would be of prime interest to a league attempting to combat the use of such ringers. To restate, the log will have the following structure:
part_id char(3),
team_id char(3),
type char(1),
date date,
time time,
notes text,
user_id char(5)
which I discussed briefly at the beginning of this chapter. Beyond the issues that I raised at that point, I should point out that the date and time stored here will be the date and time the change was recorded, not the effective date of the change. In some instances the latter might be preferred, and appropriate scroll boxes incorporated into the team association page. Such elaboration could extend to creating more detail in this record, and the functionality to maintain it could be incorporated into this overall structure with reasonable ease. For those so inclined, this would represent a reasonably ambitious homework assignment. <grin> My intent here, however, is to accentuate speed and ease of use. (The new table can be created in the database either by piping into psql the part_change.script included in the current samples file "psql -h ralphzilla-raider -U baseball baseball < part_change.script" or by running the bbdb_recreate.sh shell script.)
In any event, the information required to create a record is either in the request object or part of the context in which the team association action is being executed, so the modifications required to incorporate the log are not extensive. First, of course, I must prepare the relevant database statement and store a reference to it in the hash of such references that I move around.
##add a change record to the part_change table
my $change=$dbh->prepare("insert into part_change (part_id,team_id,type,date,time,user_id) values (?,?,?,?,?,?)");
and
log_participant_change=>\$change
Now that I have the ability to store the record, all I have to do is fire off that statement with the values appropriate to the context.
To do that, I put the following two statements immediately after the statements that remove a participant's team association.
$sth=$$db{'log_participant_change'};
$$sth->execute(@{$$params{'exist'}}[$i],@{$$params{'team'}}[0],
'r',(((localtime)[4])+1).'/'.(localtime)[3].'/'.(((localtime)[5])+1900),
(localtime)[2].':'.(localtime)[1].':'.(localtime)[0],$$r->connection->user);
and analogous statements after those that add an association. (As the only difference between the execute() statements is the source of the participant_id, @{$$params{'exist'}}[$i] v. @{$$params{'unassign'}}[$j], and the type code, 'a' or 'r', I have not included both sets of statements.) With these changes, a record of each change will be stored in the part_change table.
While I want some level of error reporting if something goes wrong with the insertion of a log record, I do not think that I need or want the same level of feedback that I provide elsewhere. Therefore, I decided to implement a structure that would simply trap the failure of any of the log insert statements and report accordingly. To this end I initialize two scalars, $log and $log_ctr, before the first of the while() loops. I set the value of $log to 0 at the beginning of each of the blocks that execute if an action has been selected for a record to make sure that the value from a previous iteration does not affect successive iterations, and use that scalar to hold the return value from the execution of the statement. If the statement failed, of course, that return value is 0, and I increment the $log_ctr scalar in that circumstance.
$log=$$sth->execute(...);
$log_ctr++ if ! $log;
After the while() loops have executed, any positive value in $log_ctr will mean that at least one statement failed. I have added a statement to the lines that print feedback that reports a problem if such is the case. $$r->print("<font color='red'>Log entries not made appropriately.<br>
Contact System Administrator<br>/font>") if $log_ctr;
I am off and rolling now, but a look at the subroutine reveals that there are some things that I could clean up to shave off a few execution cycles if more than one record has been modified. Specifically, there is no reason to retrieve the references to the relevant statement handles for each selected record. As long as I use unique names, I can retrieve those once for each submitted page. Similarly, I can construct most of the list of items to be added to the log file at the beginning of the subroutine, thus minimizing the number of calls to the internal perl localtime function that establish the date and time. (Any variations in the time recorded due to the passage of time as the subroutine executes should be disregarded anyway.) Therefore, I added a section retrieving the statement handles
##retrieved references to statement handles
my $del_sth=$$db{'remove_team'};
my $add_sth=$$db{'associate_team'};
my $log_sth=$$db{'log_participant_change'};
and one creating an array of the log values that will remain static on any given invocation of the subroutine.
##array of arguments for log statement
my @time=localtime;
my @log_args=(@{$$params{'team'}}[0],
($time[4]+1).'/'.$time[3].'/'.($time[5]+1900),
$time[2].':'.$time[1].':'.$time[0],$$r->connection->user);
The final version of the while() loops in the subroutine now looks like this:
while ($i < @{$$params{'removes'}}[0]) {
if (@{$$params{'delete_'.$i}}[0] =~ 'yes') {
$log=0;
my $res=$$del_sth->execute(@{$$params{'exist'}}[$i]);
$all_deletes++;
$del_res=$del_res + $res;
$log=$$log_sth->execute(@{$$params{'exist'}}[$i],'r',@log_args);
$log_ctr++ if ! $log;
}
$i++;
}
while ($j < @{$$params{'additions'}}[0]) {
if (@{$$params{'insert_'.$j}}[0] =~ 'yes') {
$log=0;
my $res=$$add_sth->execute(@{$$params{'team'}}[0],@{$$params{'unassign'}}[$j]);
$all_associate++;
$assoc_res=$assoc_res + $res;
$log=$$log_sth->execute(@{$$params{'unassign'}}[$j],'a',@log_args);
$log_ctr++ if ! $log;
}
$j++;
}
As a side note, I had to change the order of the columns specified in the preparation of the log insert statement for this to work. In the original order, the participant_id and type columns bracketed the team_id column, the value for which is one of the static elements I wanted to include in @log_args.
Returning to the issue I mentioned earlier regarding the display of the stored team affiliation in the player listing, I have decided that I want to do that in a manner that cannot be edited. This is going to require that I extend edit_table() to handle simply printing the contents of one of the elements occupying a given position in the arrays referenced in @rows. I can see two obvious contexts in which I might want to print something, the first being printing the actual values stored in the arrays referenced by @rows and the second in which I would want to print a value stored in a hash for which the value in the @rows structure is the key. The current situation fits into the second context, because while I will have available the team id, what I want to print is the team name.
Implementing this within edit_page() is relatively straightforward, with the addition of the following condition to the switch block.
if ($names->[$k][2] =~ /print/) {
$$r->print("<td>");
##if an array has been passed, print the value keyed to the element in the $rows structure.
## otherwise, print that element
if ($names->[$k][3]) {
$$r->print("$names->[$k][3]->{$rows->[$j][$k]}");
}
elsif (! $names->[$k][3]) {
$$r->print($rows->[$j][$k]);
}
$$r->print("</td>");
last SWITCH;
}
Within the <td></td> tags that delineate a table element, I include an if() - elsif() construct that tests for the presence of an entry in the fourth element of the array referenced in $names that describes this column. If there is an entry there, this version assumes that it is a hashref and uses that reference with the appropriate element in the $rows structure to look up the value to be printed. If that element is not there, the value in the element in the $rows structure is printed. Obviously, any further constraints on the nature of the output here would require substantial expansion of this logic, but I think that a realistic assessment of the potential set of desired outputs here is limited, quite possibly to the two I have identified here.
The edit_table() subroutine will now generate a column consisting of printed entries, given that the structures fed to it specify such. In the context of the players table, defining those structures involved adding the team column to the preparation of the relevant select statement.
my $all_part=$dbh->prepare("select participant_id,part_fname,part_lname,team
from participant where participant_id <> ' '
order by part_lname,part_fname");
and adding the specification of that column to an array that is now referenced in the $names structure.
if ($$r->path_info =~ /padd/) {
##scalars holding widget names
my $id='part_'.$table;
my $fname='fname_'.$table;
my $lname='lname_'.$table;
##descriptive arrays
my @a=('',$id,'hidden','');
my @b=('First Name',$fname,'text','');
my @c=('Last Name',$lname,'text','');
my @d=('Team','','print',select[8]);
@t_names=(\@a,\@b,\@c,\@d);
There are two things worthy of note in the snippet above. First, there no widget names are included in the array defined for this column, simply because none are required, given that I am not going to include these elements in what is submitted by the form that is being generated here. The second item has to do with the hashref being passed as the fourth element of the descriptive array. That is the hash with team_id as the key and team_name as its associated value, which I had not previously included in the @select array. I spent a couple or three hours trying to figure out what was wrong with the dereferencing scheme that I am using before I realized that the hash I was using, referenced by $team and stored in $select[6], is reversed from what I need here. <rueful grin> As I had previously defined $rev_team, which has the appropriate format, I decided to add a reference to it in @select to facilitate its use.
Selection of the players option will now generate a page that looks like this
which is what I want. To verify that the value is printed appropriately if the hash is not present in the relevant array, I removed $select[8], stopped the server, updated the modules, and restarted. The players page now looks like this
which is appropriate.
With the implementation of these components I still have a few management tasks that I may implement, such as maintaining the system codes in, for example, the event and event_result tables, but for a change I think I will move into some of the functionality that will be incorporated in the public area. To know just exactly what that will be, however, the reader will have to wait for the next chapter.