Search by State the CiviCRM Way

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.

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 the API spit back all relevant Contacts.

I added the code to the search and tested with a few states. Everything worked perfectly. I was on the verge of declaring the search fixed when I tried "Tiranë, Albania". The result was a blank page. Oh no, a dreaded encoding error. But how? I was only passing numbers. The umlaut couldn't be responsible. Was it countries other than the US? I tried some provinces of Canada, but they worked correctly. Could it be countries that only have one province listed? (Albania was the only one like that.) I searched for "London, United Kingdom." Another blank screen.

I enabled PHP's error messages, and refreshed. "CiviCRM_API3_Exception: 1537 is not a valid value for state_province_id" I resisted the urge to jump out the window. The ID was correct. I had gotten it from the API only seconds earlier. But some internal filter was rejecting it. I sat and planned how I would get around this ridiculous situation.

Could I search by country and then match the state abbreviation? That should be unique, right? I tried a search on country, and noticed that it included state_province_id, even though I wasn't asking for it. An idea crept into my head. I added a try/catch block around the first API query, and inside the catch block I ran a second query that searched on country_id instead of state_province_id. I then looped over those Contacts and collected any whose state_province_id matched the search value. Unfortunately, my search drop-down was not giving me country IDs, because state_province_id was already unique. I updated my select to give values that included both country and state ID.

I tested the trouble states again, and this time they returned sensible results instead of a blank screen. The state search was working perfectly, and any internet user who happened to want to search for members by state would never know the pain and misery hidden behind that "Go" button.