I don't normally write about work, because it's generally both boring and privileged. However, I think this warrants an exception. Enjoy.
The ticket came in as follows:
The following member is in The Netherlands, but is coming up in the Utah state search.
C----- P------
owner, garden designer
Z-------- 94
hoogland, UT 3828GE Netherlands
View Member Profile
A quick glance told me all I needed to know. The text-based abbreviation match was being too greedy. It was my chance to redo the member search the Right Way™.
The website having this problem is built around the CiviCRM platform, which manages all of its members and handles billing. CiviCRM was chosen, mainly, because it is free. However, after being forced to work on it, I think the cost is still too great.
That being said, a search for all members in a state should be simple. CiviCRM provides an API for accessing its data. In addition to that, it provides a developer tool to generate and preview queries against that API. Armed with these, I set to work.
Members are stored as Contacts, so I opened up the API explorer to see what was available. I wanted to make a list of all states and associated countries that had members. Fortunately, each state has a unique numeric ID, so I could use that instead of matching abbreviations like the current search did.
I checked the explorer and was delighted to find that it provided state_province_id, state_province_name, and country for Contacts. This wouldn't be as hard as I had expected. I created a query to search for those fields on all Contacts who were in the Searchable group. Unfortunately, the explorer generated an invalid query using the group's name instead of its ID, so when I ran the query it exploded. I had run into this issue before and already knew the ID, so I copied the query into the search code and replaced the group name with the ID.
I wrote a loop to go through the returned Contacts and make a list of countries with their associated states, then used that list in a drop-down in the search form. I saved my changes and loaded the form. The drop-down showed countries, but there were no states.
Perplexed, I dumped the list of countries I had made, hoping to find my mistake, but it only had countries too. I dumped the result I had gotten from the API. Notably missing from the results were state_province_id and state_province_name. I went to the API explorer, removed the Group, and ran my query on all Contacts. There, as well, the fields I had requested were missing. I undid my changes and started from scratch.
I noticed that while the query did not return the fields I had asked for, it did return one I had not: address_id. I reasoned that I could use this field, along with a MySQL query, to find the states' names and IDs. I created a new API query asking for the address_id of Contacts. Having learned my lesson from the last time, I ran the query in the explorer without a group. The results did not include address_id.
Okay, I had seen address_id in my results before, so I knew it could be returned. It must only appear as a side-effect of one of the other fields, I reasoned. I created queries asking for country, state_province_id, and state_province_name, but address_id was not returned for any of them. Finally, I made a query asking for all three together. address_id appeared in the results. I counted it as a victory.
I made a MySQL query using these IDs in a massive IN clause. The query selected state name, ID, and country. Using the results of that query, I re-created my list of countries and added them to the drop-down input.
Of course, that was only half of the equation. The drop-down showed all of the states, but I needed to find all members that matched the chosen state. Fortunately, the API allowed me to limit queries based on state_province_id. I verified it in the API explorer before writing the code. I passed it the selected state ID and …