Web-based membership/transaction database. Continue reading
Still in progress.
Since I had not really described in much derail any of the software projects that I designed and implemented over my long career as a cowboy coder, it seemed appropriate to provide quite a few details about my approach to this one, the first that I had attempted after the pandemic. My apologies if the entry became a little wonky at times.
The Hartford Bridge Club (HBC) has been recognized as the oldest such group in the American Contract Bridge League (ACBL), the governing agency for competitive bridge in North America. The club boasted more than five hundred members before the pandemic. Unlike most bridge clubs, it was owned by its members, who paid dues every year for the privilege of playing there. The club had a manager, but her contract specified no salary.1 The decision makers consisted of the usual four officers—president, vice-president, secretary, and treasurer—and six “trustees” who usually served three-year terms. Nominees for both officers and trustees were selected by a committee and elected at the club’s annual meeting in the fall. Additional nominations were possible, but usually the entire slate was elected.
In practice, of course, a lot of the responsibility was handed off by the Board of Trustees to the club manager.
In the fall of 2021 I was asked to serve on the HBC’s board. My three years on that board have been chronicled here. One of the most frustrating experiences for me and for many others else was the fact that the club seemed to have no definite notion of which people were actually members. The count done by the treasurer, Trevor Reeves, was significantly less than the “official” number reported by the club’s manager, Donna Feir. Both were well short of the pre-pandemic standard of over five hundred. No one seemed able to reconcile the two in a systematic manner.
Donna kept track of membership using the ACBLscore program provided by IBM. Its primary purposes were to set up different types of games and tabulate results, but it also kept track of who had played at the club. The software was written decades earlier in dBase or something similar that ran in DOS or windows. It did not employ a relational database. The files were in a proprietary format. The screens were antiquated. It performed many functions admirably, but there were limitations.
The discrepancies between membership lists persisted throughout the next two years. Near the end of 2023 I decided to try to address this situation. I interviewed most of the major players. I did not interview Donna because my experience was that she seemed very nervous about anything that might upset the club’s day-to-day activities. I learned that the club had several lists of members. No automated method connected them:
- The ACBLscore list that Donna maintained could be used to print phone lists and masterpoint lists if those fields were kept up to date. Other reports were also feasible. Files with comma-separated values3 (csv) could be created for exporting to software. Many programs, including spreadsheets, could read these files
- The club’s gmail account had a contact file associated with it.
- The club used its MailChimp account for mailings to all members. Names and email addresses of members were stored in an “audience” from which files could be imported and exported.
- The financial people on or associated with the board had at least one list.
- Other ad hoc lists sometimes were created.
I determined that a relatively risk-free system composed of a membership file and a transaction file stored on relational database that everyone who needed it could access would probably address many of these issues without requiring a great deal of work. In addition to the main files, I envisioned three background tables:
- A very small table to discriminate between memberships, contributions, and any other basic type of transaction. There were not many of the latter, but better to plan than neglect.
- The table for transaction types contained the a description, fiscal year2, and a code designating the main type.The last two were required fields.
- A user table for security.
I envisioned only one very flexible output program that could simply count of the number of items selected, produce a list on the screen, or create a csv file to be downloaded. If that proved insufficient, I felt confident of being able to add more options later without a great deal of difficulty..
I also saw an opportunity to integrate data from the players table that I downloaded monthly from the ACBL roster. So, users could easily compare what was on the new system’s members table with what the ACBL had: address, phone numbers, email address, masterpoints, etc.
In my heyday I could have designed and coded this sort of project on an AS/400 in less than a week4. However, I had done almost no programming at all since the beginning of the pandemic and precious little in the previous four or five years. Furthermore, I would be working in a less forgiving environment than what I had been accustomed to at the turn of the century. I would not have the AS/400’s tools and IBM’s support. This project required an Internet-based system for others to use that ran on php scripts, a MySQL database, and an Apache server on a computer on which I rented space. I had only designed one web-based project, and it did not use the php programming environment. It also did not help that I was in my mid-seventies and becoming more senile every day.
In early 2022 I had undertaken a project to provide Ben Bishop, the president of the HBC, with breakdowns of the number of active club members in various masterpoint ranges. I had asked for a csv file that contained one record for each active5 member of the club. Someone extracted the data from the ACBLscore program that the club used, and I received it in early February. I then used MySQL to populate the “HBC?” field on the players table in the database that I had created for District 25. This information allowed me to provide the club with accurate and timely data.
After I had decided on the fields in the files and tables6 for the membership database project and used MySQL to create empty versions of each table, I wrote sets of php scripts for recording, updating, and deleting main types and transaction types. Since I modeled these scripts on ones that I had written previously, I was able to produce them relatively quickly. It was at this time that I decided to save all of the scripts in the same folder as the other php work that I had already done. This allowed me to have easy access to dozens of scripts that I had used repeatedly.
I wrote and tested the scripts on Asus, my local desktop system. I planned to copy it to my wavada.org account on the iPower system when enough had been completed that I could demonstrate its functionality to others at the HBC.
I then entered two main types and several transaction types for the 2022 and 2023 fiscal years.
The csv file from ACBLscore had columns for first name, last name, ACBL number, and email address. I loaded it into a spreadsheet. Nine records had no ACBL number. I assigned numbers 1-9 to them. This was safe because the lowest number used by the ACBL was 9999999. I also changed the first character for the Life Masters to the numerical equivalent. I imported the file to my local MySQL database and created one record in the members file and one transaction record for membership in 2022 and another one for 2023..
Next came the script for maintaining the member table. There was nothing remarkable about this program except for the fact that it showed a history of all of the transactions for the selected member and extracted and displayed pertinent information from the players table in the database that I created for District 25.
All of the fields from the original ACBLscore file—first name, last name, email address—as well as five fields that I added in anticipation of future needs—phone number, photo link, emergency contact name and phone, and concatenated name and town—could be changed.
I deliberately made no provision for deleting a member. I could not foresee any reason for deletion, and if someone did it accidentally, it could cause significant problems.
Coding the main program that users would use for data entry was the next target. Entering a transaction would probably be perceived as an extra step. Therefore, its design must emphasize speed and simplicity so that it did not seem onerous. Here is a screenshot:
Transaction types were ordered in the selection window so that those for the latest fiscal year were on top and obsolete ones were excluded. Members were selected from an alphabetical (by last name) list. The current date appeared as the default for both the Transaction Date and the Deposit Date. The Deposit Reference # and the Note were optional. It should be possible to record any transaction in under a minute.
When the transaction entry program was finished, I was familiar enough with the way that the system flowed that I could design the security. This was new for me. IBM provided security on the midrange programs that I had worked, and I had been the only user for my previous work for the district on wavada.org. I created a table of authorized users that had only two fields, user ID and password. I created a few records using MySQL. I then wrote scripts for a routine to check whether the user has provided the user ID and password or not. If not, it forced them to select the former from a list and enter the latter. The script was inserted at the beginning of every program on the menu.
I did not provide a way for anyone the HBC to maintain the user table. I figured that I might need to do that at some point, but I felt that I should control creation of user IDs at least during the installation and testing phase.
Before I coded the scripts for producing the output I designed and executed a very simple menu. It contained three sets of buttons.
The buttons on the left were for maintenance of the three tables. The middle group originally contained only the program to record transactions. The one on the right was for output.
I used the <BUTTON> HTML tag for the buttons. I had never used it before, but I had little difficulty in adapting to its syntax.
I decided that I needed two more sets of scripts for transactions. I had decided not to let users delete or edit them, but they certainly required a method of correcting mistakes. I decided to let them reverse the erroneous transactions and enter new ones. They also might need a way to edit fields that did not affect auditing such as the notes. If so, I will provide a way to do that.
The other item in the second column of the menu, “Change Member Numbers”, was designed to handle the situation in which a person who was not an ACBL member joined the club and was assigned a low number. If that person later became an ACBL member, it was important that everything associated with the low number be changed to reflect the new number. After that the old number could be reused.
When TSI was writing programs for its customers we would usually provide the output in the same format that the users were accustomed to or what they wished that they could have. Then we would design the selection screen so that they could get that output rapidly.
The HBC directors occasionally got reports or exports from ACBLscore. They first selected the format of the output. The next steps involved a very flexible method of selecting. It began with the window at the right that allowed them to specify which fields that they wanted to use for selections. This was a fairly sophisticated approach7, but most of the users were terrified by it.
The space bar key was used for selection. Depending on which field was selected, a new set of windows allowed them to specify the details of the “restriction”. A user who designated two or three restrictions might need to navigate six to ten additional windows to get to the next step. If “Cancel” was selected anywhere in the process, control reverted to the format selection window that preceded the one displayed here. When all restrictions had been specified, a new window to list the columns on the report appeared.
Once again the space bar key was used to indicate the fields. In this case the indicated fields would appear as columns on the report or the file exported.
There was no way to designate a set of fields that was commonly used. The user either needed to start with a blank slate and select the needed columns or select all first and then designate the columns that were not needed.
There was a lot to like in this approach. Virtually anything could be extracted from the master files. However, the process was quite involved and was subject to time-consuming mistakes and corrections.
When I had to use the field screen for a project to upload tournament results to the District 25 database that I designed, I had to memorize the selection routine, which was something like “Press the space bar key eleven times, press the down arrow six times, one space bar, five down arrows, one space bar, four down arrows, one space bar, OK.”
I had the advantage of not needing to provide a method as comprehensive as what the users struggled with. For example, close to 100 percent of the people in the club’s database resided in District 25. In any case the unit or district in which they resided was seldom useful to the directors or anyone else in the club’s administration. Many of the other fields were of no use at all to directors or anyone else at the club.
I decided to try to provide sufficient selection and reporting flexibility on only one screen. Here is what I eventually came up with8:
The screen had four sections. The upper left section was used for determining which transactions would be selected. Starting and ending values could be specified for the first five fields. The Masterpoints and YTD Masterpoints fields were the values at the time that the program was run, not the time of the transactions.
Only valid Type Codes and Main Types were allowed. The Fiscal Year was a two-digit number. Only transactions with a type code for which that year had been specified were selected.
If the User ID, which referred to the person who recorded the transactions, was specified, it was validated against the user table.
Reversed transaction and transactions for deceased members were ordinarily excluded. A user who wanted to include either of them could check the appropriate box.
If all of the defaults in the fields for the selection criteria were accepted, all living individuals who were members for the specified fiscal year would be selected.
The upper right section was for determining which columns should appear on the report. The member’s name and town always appeared. The two columns for masterpoints came from the roster file and reflected the most recent ACBL roster, not the one at the time of the transaction. The phone number came from the member record.
Up to three sort fields could be selected by clicking on the appropriate radio button. The same field could not be selected twice.
There were three possible kinds of output—the number of records selected, the number selected plus a list of the transactions, or a csv file downloaded to the user’s computer. This iappeared when the transaction type was set to M14, and otherwise the defaults were selected:
When I showed the system to Ben Bishop for the first time during the late summer of 2024 the screen listed all three types of reports, but the one to produce and download the csv file did not work.
I researched on the web how to execute the download of the csv file in php. I found several articles with useful samples. I had expected that I could do it by defining a function that was invoked when the user specified the radio button for “Download a csv file” and then clicked on the Submit button. I was wrong. It worked when the URL for the script with SQL statement embedded in the code was executed directly in the browser, but it did not work when the SQL statement was passed to the same script through an argument for a function arguments. It just displayed the contents of the csv file on the screen.
I beat my head against this wall for an embarrassingly long time. I eventually realized that the problem was that nothing could be displayed on the screen when the csv script began. Everything must be initialized as it was when the URL was executed directly in the browser. I considered creating a file in the database that was only used to hold the SQL statement, but if two people were downloading at the same time, confusion might result. I finally figured out that I needed a JavaScript routine that passed the variables. The key statement was f.action = \”$Bin\” + \”HBC_CSV.php?sq=$sq\”.
- The backslashes before the quotation marks indicated that the quotation marks were to be preserved. They did not indicate the beginning or end of a string constant.
- The $Bin php variable delineated whether the script was running on the local server or iPower. HBC_CSV.php was the name of the script that executed the SQL statement, put it in csv format, and downloaded it.
- The question mark after php indicated that a list of variables and values followed.
- The $sq variable contained the MySQL statement.
I was quite excited when I finally got this to work.
The members table that I showed to Ben did not have a way of flagging records for deceased members. I added a field for that and a checkbox to the output selection screen to allow inclusion or exclusion.
At the first presentation the output screen did not have the list of columns to be included. Providing this flexibility proved to be much more time-consuming than I expected. It was difficult enough to put conditions in front of every statement that referenced any of the fields. It was very easy to get the syntax slightly wrong or to leave out a brace, bracket, parenthesis, or semicolon. When that happened, the syntax error that was thrown returned a message that something was “unexpected” in line 123 (or whatever the line number was). The cause of the error was usually something missing either before or after that line. My seventy-six year old eyes—never that good since the third grade—tired of looking for likely suspects quite easily.
The worst was the use of a dollar sign as the first character in every variable name. I had spent three decades placing dollar signs as the last character in names of string variables. I discovered that I had not broken myself of that habit. The very last syntax errors that I corrected were two occasions when I overcorrected for my BASIC programming habits and placed two dollar signs at the beginning of a variable name in the csv script.
On January 18 I went into the club an hour before the Saturday afternoon game. I seated myself at the “old” directors’ computer in the back room. A search program that I had never seen was in the middle of the screen. I closed it down and deleted the “run-time” messages. I then was able to see the icon on the desktop for ACBLscore. It was a little different from what I was used to, but I was able to find the program to create a csv file.
I had brought a flash drive with me. I tried to insert it into one of the USB ports on the box on the floor next to me. I had difficulty fitting it in. I had to turn it around so that it was going in with the logo facing to the right.
I ran the exporting program. When I went to select a group I was surprised to find over a hundred choices. I first looked at the ones that started with D. None of them looked like they might stand for deceased.
In the H’s I discovered what I was looking for. H00 had only one record for Marsha Futterman. It must have been a test. H01 through H26 had hundreds of records. There were also mysterious groups for H, H2, and H35.
I selected H01 and specified six fields: first and last names, ACBL numbers, the two phone numbers, and the email addresses. I saved the file onto my flash drive as H01.csv. I then repeated the process for H02 and so on.
At about the time that I was working on H05 a group of novices from the beginner class took seats in the back room and peppered Bob Hughes with questions. I tried to ignore them, and I wished that I had brought my earplugs, but I forgot them. The only thing that required much concentration was choosing a name for the file that matched the group selected. I made a few mistakes that I noticed almost immediately, but I feared that I may have saved the same group under two different names at least once or twice. I finished with H25 at about 12:45, fifteen minutes before game time.
After the game, in which I played pretty well, I drove home and immediately downloaded the twenty-six files onto Asus. I loaded them one-by-one so that I could count the records. I found the following anomalies: H02 had a small number of records that included players (Sue and I) who were definitely not members in 2002. H09 was missing entirely. H11 had only 306 records with no Wavadas. H15, H16, and H17 all had 508 records. H22 and H23 both had 430 record.
Since I was also playing on Sunday, I resolved to come in a little early and redo those files. In that session I was careful to upload H09, H10, H11, H15, H16, H17, H22, and H23 into the correct files. After those files were processed, the only file that seemed really erroneous to me was H02.
On Tuesday I saved H02 again. When I restored it, I was satisfied with the look of the data. I noticed that two new players were entered twice in H23, and one of them had been entered twice again in H24.
I started the upload process by creating database files named h01 through h25. Each contained the same fields as the associated Hxx file in the same order.
The biggest difficulty I that encountered in writing the script that used the Hxx files to add records for new members and transactions for everyone involved the players who did not have ACBL numbers. The program that I had written a few years earlier to update the “HBC?” field on the player table skipped players without ACBL numbers. I started with that program, but I was stumped by the step of automatically assigning low numbers to the ACBL number field because there was no completely reliable way to tell if the member had already been added to the database with a low number. In retrospect I think that I should have been able to figure it out, but I decided to assign numbers to the Hxx file and save it as HxxA.
At first I started with 1 and incremented by 1 or, if I could not remember the last one I used, I skipped to a number that I was certain that I had not used and incremented from there. After the first few years, I decided to start each file with xx times 100. So, the first number used in processing H24 was 2400. I saved the result as HxxA.
To make sure that I was not assigning two different numbers to the same player I wrote a SQL statement to list on the screen all the existing members with low numbers in the same order as the HxxA file. When I came to a member with a blank ACBL number or NM in the field, I would scroll down to the appropriate part of the SQL list and, if I found the matching name, I entered the previously assigned number. If not, I entered the next available number as explained above. A few of the records had other entries that I had to fix by hand. At least one started with a # sign.
When I reached the bottom of the HxxA file, I scanned for blank records that I missed and searched for NMs that I had skipped.
I kept track of my progress on an 8½x11 sheet of paper. There were four steps. The first step, “N” on the sheet, stood for the process of inserting fake ACBL numbers into the spreadsheet. “I” stood for the process of importing the HxxA.cvs file into the MySQL file, hxx. The only trick to this was to remember to use the TRUNCATE command to empty the file if this was not the first time.
“C” stood for running the script that I wrote to create the transaction records and, for new members, the member records. This crashed about half the time, but I seldom had difficulty ascertaining what the source of the problem was. It was usually that one of the values in the ACBL number field was not valid. I had to fix the spreadsheet and go back to the “N” step.
“T” stood for comparing the the number of records reportedly written on the file with the number of records on HxxA.csv. After the first few files these always matched.
The last step, “L”, required that I run the list program on the menu with the default parameters to make sure that it produced the same number of records as the file contained. I was shocked to find a discrepancy when I ran it on the second or third file. After a little research I discovered that the “JOIN” clauses were coded incorrectly. I needed to “LEFT JOIN” members to the trans file and the players file independently. I was happy that I discovered this before anyone tried to use it.
At the end of the migration process I discovered that I had assigned two separate bogus ACBL numbers to a handful of members. If I had added a step to check for this, I would have saved myself some grief. I could have easily fixed the erroneous records using SQL statements, but I remembered that the ACBLscore program had an option to combine two players’ records into one. I decided to add a new option to the transaction section of the menu to do the same thing.
The program to combine the records for two members was added as the fourth option on the center section of the member. The user had to designate two members—the erroneous one and the one to be used henceforward.
1. An annual “honorarium” was voted annually to the manager and other officials.
2. The club’s fiscal year began on November 1. Thus fiscal 2025 ran from 11/01/24 to 10/31/25.
3. A file with the extension “.csv” contains rows that are formatted identically. Fields (columns) are separated by a comma or another delimiter. Spreadsheet programs such as Excel can read these files.
4. I could code extremely rapidly in the native environment on the AS/400, but the resulting data entry screens, although equally functional, would have been much less attractive. When the company closed in 2014 there was no way to export a csv file on a user’s computer. That would take several steps using third-party software. The AS/400 could serve as a file server for php programs, but if that method were used for a project like this, development would be much more tedious and time-consuming.
5. Some club members were not required to pay the prescribed annual dues in order to participate as members. I assumed that every person on the file had paid the dues, but there 6ere doubtless some who were not required to.
6. There is no fundamental difference between a table and a file. I have always used the word “table” to delineate files that are established at the beginning have a relatively small number of records, and are primarily used to maintain consistency. The other two files in this system can be maintained at will and are much larger.
7. Why “unit” and “gender” were capitalized on this window remainder a mystery throughout my investigation.
