Understanding Search Result Issues

It sometimes happens that a search performed on your database doesn’t give the expected results. This can happen with the search functionality that is built-in to Participants Database or when using the Combo Multisearch add-on.

Both of these search functions have basic configuration settings that should be properly set in order to get the results you want. If these configuration settings are set the way you think they should be then there are ways to “look under the hood” and see exactly what’s going on.

Built-in Search Configuration Settings

The simple search function that comes built-in to Participants Database has 3 main settings that affect how it works. These settings are found in the Participants Database settings under the List Display tab.

Allow Empty Search – Use this setting if you want your users to be able to find records that are missing a piece of data. This is an unusual setting to use, but how it works is this: When the user submits a search that has nothing in the search words input, instead of assuming that the user needs to input a search term, a search is performed looking for records that have a blank value for the field.

Strict User Searching – This means that instead of matching part of the value of a field, the search must match the complete value. For example, if the field is a city name, and the user types in “San” they will get a lot of matches probably. If this setting is checked, they won’t get any matches unless they type in the whole name, like “San Diego”

Split Search Phrases – This setting changes how the search handles an input that is two or more words separated by a space. If this is unchecked, it will try to find the whole phrase that the user typed in. If it is checked, the search will look for any of the words that were typed in.

The most common issue with these settings is the use of the “Strict Searching” setting, which makes it much harder to find a match. Only use this if you really require that the user match something exactly. Most of the time, you just want them to find something close to what they typed in, and this is generally what the user expects.

Combo Multisearch Settings

There’s a lot of complexity in these settings, so I’m only going to go into the settings that seem to cause problems for people when setting up the search.

Enable Filter Mode – This setting can cause some confusion because it will allow the user to do something like fill in or select a single search item, and get a lot of results. If you want to force them to make a minimum number of selections, you should use the required attribute to force them to make multiple selections before getting their results.

Whole Word Match Only – This forces the query to look for a match to the whole word that was entered. For example, of this is checked and the user enters a word like “sun” it would not match a word that has that word in it, like “sunshine.” This can sometimes give unexpected results, but the main reason to use this is to cut down on irrelevant results.

Analyzing Search Results

So, if all the settings are correct, and you’re still not getting the expected results, it’s helpful to take a detailed look at what’s going on with the search.

A search on the Participants Database database is just showing the results of a database query. A database query is a request for information that can be very prescise in what it wants. The search control determines what terms are used to filter the results, and you can see this in the query. The query will show you exactly what is being searched for, and this can help you understand why you’re getting a certain result.

Seeing the Database Query

To see your database queries, you use the Participants Database Debugging Log. The linked article goes into detail explaining how to set up and use the debugging log.

When a search is performed, the query that is used is written to the log, and it will look something like this:

The part of the query that determines which records will be included happens after the “WHERE” keyword. The word “LIKE” is used to make a partial match, while “=” looks for an exact match.

One thing to notice in the query is the value for the “club_role” field, it’s looking for “1”. When the user set the search parameters, they were choosing the word “staff” but in the configuration of the club_role selector field, “staff” is saved as “1” in the database. The word “staff” won’t be found in the database, so unless you know that the word “staff” is represented by “1” in the database, you may think that the query is incorrect.

Another way that unexpected results can happen is if the data for a field in the database doesn’t match the configuration of the Participants Database field. Using the above example, if the “club_role” value for a record is “staff” it will never match because the search control is looking for “1”. This can happen if the database was imported, or if the field configuration was changed while there was data from the previous configuration in the database.

In order to understand how the query is working with the database, you need to know the exact contents of the database. When Participants Database displays records, for many field types, what you see on the screen is not exactly what is in the database. The database values are used to produce the displayed value and in many cases, they are different.

Taking a Direct Look at the Data

In order to see the raw data that is stored, you need to open the database directly. The usual way this is done is by using an application called phpMyAdmin. This application is typically made available in your hosting control panel.

If you’re not familiar with phpMyAdmin, it can be a bit intimidating. Here are the simple instructions for looking at your data:

• Open phpMyAdmin
• Find the database that your WordPress application is using in the column on the far left, click on it
• Now you will be looking at the tables in the database, find the table named wp_participants_database (the “wp_” prefix might be different in your case)
• This is the main Participants Database storage table, click on the table to select it
• If it isn’t already selected, select the “Browse” tab at the top of the page
• Find the name of the field you want to inspect in one of the columns, scroll to the right if you don’t see it

Now you’re looking at the raw values stored in the database, and these are the values the query attempts to match. By comparing the database query with these values, you should be able to see what the problem is.