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.

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 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.

What Shall We Do?

Peter explained, with an eloquence he had never before possessed, how the prophecies of David1 had finally been fulfilled by his Rabbi, Jesus. The songs they sang in their homes had come true in a way no one had expected. Jesus was not only risen but exalted, and from God’s right hand he poured out his own spirit on his disciples2. The day’s great religious festival3 was forgotten as the crowd of worshipers listened eagerly to this new revelation.

Yet against the backdrop of God’s marvelous plan was the evil of the people God has chosen. They had taken their last hope, their rightful Lord, the one chosen by God, and crucified him.4 The listening crowd could not help realizing that perhaps they had destroyed what their nation had waited centuries to see. Were they to be excluded from God’s promise? They had to know. Men and women raced forward to find if they still had a place in God’s plan5.

Peter was amazed. He had seen the religious throng in the temple as an enemy, but so many were responding to the message of Jesus. His tone changed, no longer telling a hard truth to a disobedient people, but offering comfort to heartbroken sinners.

“It is not too late! Even now you can turn from your headlong flight into oblivion. Instead, be baptized into the name of Jesus, and he will forgive you and you will also have access to his spirit6.”

Peter and the disciples continued to teach the crowd for hours, and many began to follow Jesus that day.7 They would not fall under the judgment that hung over their generation.8

  1. Psalms 110:1 

  2. Acts 2:33 

  3. Acts 2:1 

  4. Acts 2:36 

  5. Acts 2:37 

  6. Acts 2:38 

  7. Acts 2:41 

  8. Acts 2:40 

Stronghold – Westtown

The Krypton Cougars arrived at their second competition with only one obstacle standing between them and total domination: themselves. The new pickup system (also known as the old shooter) had been designed without measuring if a ball sitting on the floor would touch the intake wheels. Testing revealed that the new metal base-plate would hit the ball first, knocking the ball away repeatedly. After much deliberation, and a sometimes contentious discussion about what shape the cut should be, the team decided to cut off an inch from the front.

With their robot now even more unstoppable than before, they had it weighed and inspected. Once their radio was configured to be plugged into the field, they got in line for their first practice match. Finally they reached the front of the line, eager to test their new design. “Sorry guys, that’s all the matches we have time for tonight.”

Undeterred, they returned the next morning and stood in line again. When they finally played their long-awaited match, the new arms performed as anticipated, getting hit early in the match, bending the shooter and causing significant damage to the robot. Back in the pits, the team decided that making the same mistake a second time wasn’t quite enough. They removed the angled intake bars from the aluminum arms, leaving only two short tusks in front. Since each tusk was one-inch-square, the pickup mechanism’s width was now approximately the same as the ball, making collecting a ball from the field nearly impossible. The team saw that their pickup design was good and took the robot to the practice field to see how great their shooter was.

The team stood, waiting by the field as team after team tested their robots. “Where are we on the list?”


“Yeah, the list where you sign up for a time slot to test your robot. You did sign up for that?”

“Oh.” The team reserved for a time slot and eventually tested their shooter. They scored two high goals, out of dozens of attempts, and their joy was beyond containing as they shouted for joy while the other teams stood silently for the national anthem.

Practice time was over and the main event began. In their first match, the Krypton Cougars got off to a great start, easily clearing the defense in front of them, and slamming full-speed into their opponent’s wall, an effective, if unplanned, intimidation technique. Delighted, the team scurried into line for their second match.

The second match was equally successful, with another intimidating autonomous, until a head-on collision with an opposing robot sheered through two aluminum brackets like they were tissue paper, leaving the shooter hanging by its wires. Without enough time to fix the brackets before their next match, the team used bungee cords to strap the shooter to the robot and went back on the field. Unfortunately, they neglected to strap down the duct-taped-on limit switch, and left it laying on the field like a deceased orange long-tailed rat.

Their next match wasn’t until after lunch. They worked feverishly to make new brackets (They hadn’t brought replacement parts). Far too quickly lunch was over, and matches resumed. Their match approached and the team continued reattaching the parts they had disconnected. The match before theirs began, and the team still hadn’t finished. Duct tape and zip ties flew as the team strapped down every loose piece they could find. It was time for their match to start, and the team deployed code to the robot as it rolled towards the field.

When officials finally made them stop working on the robot, it drove well and the shooter worked, mostly. After the match, the team found that the limit switch had come unsoldered again. They re-soldered it, but then the arm would only move down, until it reached the bottom switch, at which point it wouldn’t move at all. The team checked the top limit switch, but it wasn’t closed. Figuring it had short-circuited, they removed it entirely, but the arm still didn’t move. Closer inspection revealed solder between the solder pads, creating a tiny short-circuit. The team melted the solder off the pads, but didn’t have time to attach the limit switch before the next match.

Having now unsuccessfully soldered the limit switch three times, the team decided to rotate the gearbox so they could see what they were doing. After loosening the belts and disconnecting the output shafts, they rotated the gearbox until the solder pads were on top. Now able to actually see what they were soldering, the limit switch attachment was successful.

The team celebrated with cookies, which they took to the track field behind the high school. There was a large pole-vaulting mat on the field, with yellow warning labels on every side indicating it should not be stood on while covered. The team jumped on it happily and talked about how there was nothing that could be done to make their robot any more perfect than it already was.

In its next match the robot did not cross the defense in autonomous, and the drivers received a yellow card for being on the field when it wasn’t safe. Back in the pits,  the team realized they had no electricity. The battery charger was not charging batteries and the laptops were going to sleep. While event staff were summoned, the drivers complained that they had lost connectivity with the field, and hadn’t been able to tell the robot which autonomous program to run. They decided to reboot the driver’s station in the hopes it would correct the flaw.

It did not. After some deliberation, the programmers decided that rebooting right before a match would be better. That seemed to work, which was unfortunate, because the autonomous mode they chose was untested and the robot slammed its shooter into the low bar at full speed, rendering the shooter inoperable for the rest of the match. The team immediately took the robot to the practice field and tried to replicate that failure. Fortunately, mentors intervened and suggested that it might be worth testing without the obstacle until the issue was fixed. The programmers tweaked the code as their laptop batteries died, and deployed a fix for autonomous mode just before forced hibernation.

The robot performed well in its final match of the day. The team brought it back to the pits, excited at the great day they’d had. That’s when someone pointed out that the top limit switch was beside the shooter instead of above it, negating its protective abilities, and allowing the shooter to slam repeatedly into the top of the robot.

The next morning the team returned to the field, ready to cap off their glorious performance. In the first match, the robot sat quietly during autonomous. Once the driver took over, he could only turn right. Halfway through the match he threw up his hands and walked away, leaving the robot on the field without an operator. A programmer took over and moved the arms up and down a few times, but the robot remained jammed between the field wall and a defense.

Fortunately, a friendly judge noticed their plight and helped them properly connect the driver station to their robot. In the next match the robot behaved in a more pleasing manner and left the field without any complications.

As the last match of the competition neared, the team decided they needed a grand finale to cement their position in the minds of other teams. They wished to leave no doubt that they should be chosen for the playoffs. Fortunately, they had just the thing. They prepped the robot to shoot a high goal. Considering that they weren’t reliably hitting the goal on the ground it was a bit ambitious, but the team knew they needed a big finish. They started the match with the ball and drove down the field, positioning themselves eight feet in front of the goal. The carefully lined up the shooter, moving it up and down, and rotate the robot carefully into position. When they were sure it was perfect, they launched the ball.

With the tap of a button, the launcher sprang into action. The wheels spun and the ball flew upwards, spinning majestically under the fluorescent lights. It bounced harmless off the side of the goal and rolled away. The team parked their robot by the tower, with more than forty-five seconds remaining, and waited for the end. No one picked them for the playoffs.