Posted on by

Checking Multiple Fields for a Match

Note: November 9, 2019 This tutorial is now outdated and will not work with Participants Database 1.9.4 or later. Please go to Matching Multiple Fields with CSV Import or Signup Submission for the current tutorial.

When a Participants Database signup form is submitted, it can be checked against the database for an existing record that matches the new submission. The plugin is set up to check one field for a match, and this would typically be the “email” field. This makes a good unique identifier for a record, since most of the time only one record per email is desirable…you can’t do a private link recovery unless the email is unique to one record, for instance.

Checking Multiple Fields

If you need to check multiple fields for a match because you don’t have a single field that uniquely identifies a record, here’s how you do that.

There is a filter in the plugin called ‘pdb-incoming_record_match’ that returns a simple true or false if the incoming record matches an existing record. The filter gets two arguments, the true/false value and an array of the submitted data. The true/false value will tell you if the record already tested as a match using the plugin’s normal match test. We’re going to ignore this information and just check the database for a match.

An easy way to set up a custom filter in WordPress is with a simple plugin file. In order to set this up, you’ll need to create the plugin file, then upload it to the plugins folder in your WP site. Once that’s done, you need to activate it on the plugins page in the admin.

Custom filters in WordPress require that you create a function to handle the filtering, then register the function using the add_filter() function. A plugin file must have a header, so the first few lines of text take care of that:

/**
 * Plugin Name: PDB Multiple Field Match Check
 * Description: checks a new Participants Database submissions against 
 *              the database for a matching record using multiple fields
 */
add_filter( 'pdb-incoming_record_match', 'xnau_check_for_match', 10, 2 );

The add_action() function registers the name of the callback (the function that is triggered by the filter), the priority (10) and the number of arguments it uses…in this case that’s 2: the initial check result and the data array.

Now for our function. First, we define the fields that will be used to check for a match. Then we set a couple of empty arrays that will be used to set up the database query.

$check_fields = array( 'first_name', 'last_name' ); // change this to the names of the fields you want to check
$where = array();
$values = array();

You should change that first array to name the fields you want to check for a match.

foreach ( $check_fields as $field ) {
 if ( isset( $post[$field] ) ) {
  $where[] = '`' . $field . '` LIKE "%s"';
  $values[] = trim( $post[$field] );
 }
}

Here, we are using the check fields array to put together a list of “where clauses” so when we query the database those values will be checked for a match. We only do that for fields that are present in the submission, so it’s very important that the fields you want to check for a match are required fields in the signup form. The $values array holds the actual values, which we keep in a separate array for security reasons.

Next, we count the where clauses to see if there is anything to check…if not, we return a “false” meaning there was no match.

if ( count( $where ) === 0 ) {
 //nothing to check
 return false;
}

Now, we set up the database query and see if we find any records matching all our where clauses:

$sql = 'SELECT COUNT(*) FROM ' . Participants_Db::$participants_table . ' WHERE ' . implode( ' AND ', $where );
global $wpdb;
$result = $wpdb->get_var( $wpdb->prepare( $sql, $values ) );

This is a standard bit of code using the WordPress $wpdb object to interact with the database. The $where array is converted into a series of statements joined by an “AND” so that all have to be true to make a match. The values are added into the query using the $wpdb->prepare() method which is a necessary step to secure the query against malicious code. The resulting database query will look something like this:

SELECT COUNT(*) FROM wp_participants_database WHERE `first_name` LIKE 'John' AND `last_name` LIKE 'James'

The result of this operation will simply be the number of records that match the query. Anything more than zero here means there is a matching record in the database. The return value of the function is simply the true/false answer to checking if the result is more than zero.

return $result > 0;

To create the plugin, create a blank PHP file in the text editor of your choice. The name doesn’t matter, I named it pdb-check-multiple.php in my example. Paste in the code, upload it to the plugins folder in your WP site, activate, then test it out.

Here is the complete plugin code:

Update 7/18: I added code to prevent the match from occurring if the record is getting updated: this keeps it from showing a matching field error when updating an existing record.

24 thoughts on “Checking Multiple Fields for a Match

  1. The condition becomes false if a user enters a record where last name is entered in first name and vice a versa. There should be a condition that checks if the user enters a reverse data. It still is a duplicate record but not getting matched. Any solution?

    Obviously I have different fields than first name and last name in my use case.

    1. You can add to the code so that if it fails checking the fields normally, it checks them again with the fields reversed. It will be easier if you set up your second check using a literal query, rather than use the variable assignments that are using for the first check.

  2. is this working in the last update?
    It seems the plugin is failing to read the array, it only works when the array has one single element on it, not with multiple, and also if it has only one item the error message still states that there is a duplicated email, not taking the new field name.

    1. What is the code you want to use to define your array?

      One of the problems with this script, it’s very simple, it does not change the error message, so it’s showing the error message from the default configuration. You need to use a “Duplicate Record Error Message” setting that is more general.

      1. Not getting any error message at all. Just a confirmation of the new record being created. Only gets the error message if the array contains only one element.
        this is the code I used to define the array:

        $check_fields = array(’email’,’afiliacion’);
        where email and afiliacion are the names of two of my fields.

        1. what kind of field is “afiliacion’?

        2. afiliacion is a text line, is there a restriction on the type of element?

        3. No restrictions, but some fields need to be treated differently. Text line is no problem with the code here.

          So, when checking for a duplicate record, if one of the fields is blank in the record and blank in the new submission, it will match if the other field also matches. You may not be expecting that.

        4. afiliacion is also “required” and it is checked on the register and CSV checkboxes. Not checked on the rest.

        5. still cannot make it work. Fields are not blank, just erased the whole database to start from 0 and check against one record, and still get the confirmation that the new record has been created using the same number of “afiliacion” any way I can share with you the code? or credentials? you might wanna take a look.

        6. You can email it to me at support@xnau.com

  3. Great plugin!
    I’m having a problem, I have three primary fields and this plugin checks them correctly when I add a new participant but when I’m trying to edit a participant’s information from the frontend but it appears the error message for duplicate field when a click on “Save changes” button. I used the post “Adding an Edit Record Link to the Frontend List” to add the edit option. Thank you.

    1. Thanks for this, Antonio!

      I updated the code here to add a check for the same record if it is getting updated. This will prevent it from matching itself and giving you that error. Check the article above for the updated code.

  4. Is there a was to redirect/display a specific page based on the $result value (i.e. ccust field) ? It is giving me the following error:
    Warning: Cannot modify header information – headers already sent by (output started……

    If the result is not 0, I echo / output a meta statement with http-equiv=refresh… I can not put code here because I get an error when trying to post.

    1. Scott, you cannot place redirects in templates, by the time the template is processed, the page is already getting sent to the user, hence the error.

      1. Roland,

        Thanks for the feedback. I did get it to work. I am checking the result and based on that using “echo script location.href” command and it displays my custom html page.

  5. Is it possible not to “update” the record fields that are not on the sign up page when they sign up and the record already exists?

    1. Ok. I found that by removing the “hidden” field from displaying on the sign-up page, it did not get blanked out.

    2. Scott, do you mean to show an error if the data in the signup form matches an existing record? If so, you just need to set that preference in the settings under the “signup form” tab.

  6. Hi Roland, I tried this, with the intent of preventing a parent from registering the same student for the same class twice. I edited the array to include my fields: ‘student_first_name’, ‘student_last_name’, ‘choice’, ‘parent_last_name’ (where ‘choice’ is a dropdown field with a list of classes the parent can choose from). The plugin installs fine, but appears to do nothing. I can still enter the same student for the same class twice. Any thoughts on what I may have done wrong?

    1. I’ts not really possible for me to say without seeing your code and your data. When you start to get into more complex matches, it is really essential that you know the exact contents of your database record. Open your database in phpMyAdmin (usually this is in your hosting control panel) then find the main plugin table. Under the content tab for that table, you should be able to see the record that you are trying to match against. If you inspect the contents of the fields you are looking for a match in, that will help you understand how the match needs to work.

      Also, setting up your php error log and then dumping your query to the log will give you a way to make sure you’re getting the query right. If you put this statement right before the last return line of the function, it will show you the query that was used:

      error_log( __FUNCTION__ . ' query: ' . $wpdb->last_query );
      1. Thanks as always for your help, Roland.

  7. Great Plugin for a great plugin!!!

    I have one question. Is it possible to add a message if the field match an already record in the database?

    Thanks again!

    1. Yes, check the “Duplicate Record Error Message” setting in the plugin settings under the “signup form” 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.

24 thoughts on “Checking Multiple Fields for a Match

  1. The condition becomes false if a user enters a record where last name is entered in first name and vice a versa. There should be a condition that checks if the user enters a reverse data. It still is a duplicate record but not getting matched. Any solution?

    Obviously I have different fields than first name and last name in my use case.

    1. You can add to the code so that if it fails checking the fields normally, it checks them again with the fields reversed. It will be easier if you set up your second check using a literal query, rather than use the variable assignments that are using for the first check.

  2. is this working in the last update?
    It seems the plugin is failing to read the array, it only works when the array has one single element on it, not with multiple, and also if it has only one item the error message still states that there is a duplicated email, not taking the new field name.

    1. What is the code you want to use to define your array?

      One of the problems with this script, it’s very simple, it does not change the error message, so it’s showing the error message from the default configuration. You need to use a “Duplicate Record Error Message” setting that is more general.

      1. Not getting any error message at all. Just a confirmation of the new record being created. Only gets the error message if the array contains only one element.
        this is the code I used to define the array:

        $check_fields = array(’email’,’afiliacion’);
        where email and afiliacion are the names of two of my fields.

        1. what kind of field is “afiliacion’?

        2. afiliacion is a text line, is there a restriction on the type of element?

        3. No restrictions, but some fields need to be treated differently. Text line is no problem with the code here.

          So, when checking for a duplicate record, if one of the fields is blank in the record and blank in the new submission, it will match if the other field also matches. You may not be expecting that.

        4. afiliacion is also “required” and it is checked on the register and CSV checkboxes. Not checked on the rest.

        5. still cannot make it work. Fields are not blank, just erased the whole database to start from 0 and check against one record, and still get the confirmation that the new record has been created using the same number of “afiliacion” any way I can share with you the code? or credentials? you might wanna take a look.

        6. You can email it to me at support@xnau.com

  3. Great plugin!
    I’m having a problem, I have three primary fields and this plugin checks them correctly when I add a new participant but when I’m trying to edit a participant’s information from the frontend but it appears the error message for duplicate field when a click on “Save changes” button. I used the post “Adding an Edit Record Link to the Frontend List” to add the edit option. Thank you.

    1. Thanks for this, Antonio!

      I updated the code here to add a check for the same record if it is getting updated. This will prevent it from matching itself and giving you that error. Check the article above for the updated code.

  4. Is there a was to redirect/display a specific page based on the $result value (i.e. ccust field) ? It is giving me the following error:
    Warning: Cannot modify header information – headers already sent by (output started……

    If the result is not 0, I echo / output a meta statement with http-equiv=refresh… I can not put code here because I get an error when trying to post.

    1. Scott, you cannot place redirects in templates, by the time the template is processed, the page is already getting sent to the user, hence the error.

      1. Roland,

        Thanks for the feedback. I did get it to work. I am checking the result and based on that using “echo script location.href” command and it displays my custom html page.

  5. Is it possible not to “update” the record fields that are not on the sign up page when they sign up and the record already exists?

    1. Ok. I found that by removing the “hidden” field from displaying on the sign-up page, it did not get blanked out.

    2. Scott, do you mean to show an error if the data in the signup form matches an existing record? If so, you just need to set that preference in the settings under the “signup form” tab.

  6. Hi Roland, I tried this, with the intent of preventing a parent from registering the same student for the same class twice. I edited the array to include my fields: ‘student_first_name’, ‘student_last_name’, ‘choice’, ‘parent_last_name’ (where ‘choice’ is a dropdown field with a list of classes the parent can choose from). The plugin installs fine, but appears to do nothing. I can still enter the same student for the same class twice. Any thoughts on what I may have done wrong?

    1. I’ts not really possible for me to say without seeing your code and your data. When you start to get into more complex matches, it is really essential that you know the exact contents of your database record. Open your database in phpMyAdmin (usually this is in your hosting control panel) then find the main plugin table. Under the content tab for that table, you should be able to see the record that you are trying to match against. If you inspect the contents of the fields you are looking for a match in, that will help you understand how the match needs to work.

      Also, setting up your php error log and then dumping your query to the log will give you a way to make sure you’re getting the query right. If you put this statement right before the last return line of the function, it will show you the query that was used:

      error_log( __FUNCTION__ . ' query: ' . $wpdb->last_query );
      1. Thanks as always for your help, Roland.

  7. Great Plugin for a great plugin!!!

    I have one question. Is it possible to add a message if the field match an already record in the database?

    Thanks again!

    1. Yes, check the “Duplicate Record Error Message” setting in the plugin settings under the “signup form” 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.