Posted on by

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:

Example of a search query in the debugging log

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.

9 thoughts on “Understanding Search Result Issues

  1. Hi,
    I’d like to know if it is possible that the search is made with the condition “and” in the SAME FIELD. For example, if a user search for “Beethoven 9” in the field’s name, now the search will make no result, as the name is written, for instance, “Beethoven – Symphony no. 9”. But with the “and” condition in the field’s name, it should look for “Beethoven” +/and “9”., so giving the right result.
    Thank you.

    1. For an advanced search like this, you’ll need to use the Combo Multisearch plugin. This will give you a configurable search interface that can handle this kind of thing.

      1. Thank you, Roland. Excuse-me, but I don’t see it quite clear according to the Demos. Is there the possibility to try it for a few days?

        1. We don’t provide a demo or test download, but we will give you a refund if you end up not using the plugin.

  2. Hi, I have added a date field. Format is d/m/Y.
    When I try to search for any date then no records are showing although records with the date I am inputting exists.
    Even if I type in the year only e.g. search for date field containing 1999 then again no results.
    Any help will be much appreciated.

    1. To search for a date, you must type in the complete date, partial date searches don’t work on date fields because the input has to be converted to a date to find the match. Also, it can be difficult for the plugin to parse dates in the d\m\Y format. A couple of things you can do to help are:

      1. Set the global date format to this format, that provides a hint to the plugin that this is the format you want to use.
      2. Use the “Strict Date Parsing” setting and set the “Input Date Format” to your format.

      If you want partial date searches to work, you need to use a date calculation field that converts the timestamp to a searchable date string. After that, the user should search on that field instead of the date field.

    2. Thanks. I tried step 1 and 2 but no success. However the date calculation method worked. Thank you very much.

  3. Hi Roland,

    how do i make sure that the query uses “AND” rather than “OR” in the where clause?

    Thanks

    1. This is called “Filter Mode” and the setting is in the Combp Multisearch settings under the “General Settings” tab.

Leave a Reply

Your email address will not be published. Required fields are marked *

Would you like to be notified of followup comments via e-mail? You can also subscribe without commenting.

9 thoughts on “Understanding Search Result Issues

  1. Hi,
    I’d like to know if it is possible that the search is made with the condition “and” in the SAME FIELD. For example, if a user search for “Beethoven 9” in the field’s name, now the search will make no result, as the name is written, for instance, “Beethoven – Symphony no. 9”. But with the “and” condition in the field’s name, it should look for “Beethoven” +/and “9”., so giving the right result.
    Thank you.

    1. For an advanced search like this, you’ll need to use the Combo Multisearch plugin. This will give you a configurable search interface that can handle this kind of thing.

      1. Thank you, Roland. Excuse-me, but I don’t see it quite clear according to the Demos. Is there the possibility to try it for a few days?

        1. We don’t provide a demo or test download, but we will give you a refund if you end up not using the plugin.

  2. Hi, I have added a date field. Format is d/m/Y.
    When I try to search for any date then no records are showing although records with the date I am inputting exists.
    Even if I type in the year only e.g. search for date field containing 1999 then again no results.
    Any help will be much appreciated.

    1. To search for a date, you must type in the complete date, partial date searches don’t work on date fields because the input has to be converted to a date to find the match. Also, it can be difficult for the plugin to parse dates in the d\m\Y format. A couple of things you can do to help are:

      1. Set the global date format to this format, that provides a hint to the plugin that this is the format you want to use.
      2. Use the “Strict Date Parsing” setting and set the “Input Date Format” to your format.

      If you want partial date searches to work, you need to use a date calculation field that converts the timestamp to a searchable date string. After that, the user should search on that field instead of the date field.

    2. Thanks. I tried step 1 and 2 but no success. However the date calculation method worked. Thank you very much.

  3. Hi Roland,

    how do i make sure that the query uses “AND” rather than “OR” in the where clause?

    Thanks

    1. This is called “Filter Mode” and the setting is in the Combp Multisearch settings under the “General Settings” tab.

Leave a Reply

Your email address will not be published. Required fields are marked *

Would you like to be notified of followup comments via e-mail? You can also subscribe without commenting.