Posted on by

Using Dropdown Values in the List Search

In this tutorial, I’ll explain how to modify the Participants Database list search so that instead of typing values into a text box, the user will select values from a dropdown. The values of this dropdown will be gathered from the defined values of the field being searched. The search will take place on that field alone.

This requires setting up a custom plugin template. You will probably need some familiarity with PHP and HTML to make this all work, although you won’t need to write any code if you don’t want to.

For an explanation of how to set up a custom template, take a look at this article:

Using Participants Database Custom Templates

For this example, we will be starting with the list template “pdb-list-responsive.php” found in the plugin’s templates directory. You can use the default template of you want a table layout, the process is the same. Copy this template to your theme “templates” directory (as explained in the article above) and edit that copy.

Be sure to put the name of the template in the list shortcode so your custom template will be used. Also, of course, the shortcode must have “search=true” so the search form will be shown.

Our modifications go at the top of the template where the search form is generated. Here is the code for the search form in the template before we make any midifications (starting on line 21 of the template):

<?php $this->search_sort_form_top( false, 'form-horizontal' ); ?>

<?php if ( $filter_mode == 'filter' || $filter_mode == 'both' ) : ?>

  <div class="control-group">
    <label class="control-label"><?php _e('Search', 'participants-database' )?>:</label>
    <div class="controls">
    
    <?php
      /* 
       * you can replace "false" with your own text for the "all columns" value
       * for more info on using the column_selector method, see pdb-list-detailed.php
       */
      $this->column_selector( false );
    ?>
  
      <?php $this->search_form() ?>
    </div>
    
  </div>
<?php endif ?>

First, we replace the field selector with a hidden field so that the search will always be performed on that field. In the default fieild set provided with the plugin install, there is a dropdown/other field named “interests.” We will use that for our example here, so the field selector is replaced with a hidden field named “search_field.” The value of the hidden field names the field we will be searching on. In this case, that will be “interests.” You will use the name of the field you want the search to be preformed on.

Here is what the code looks like with the field selector replaced with the hidden field:

<div class="control-group">
  <label class="control-label"><?php _e('Search', 'participants-database' )?>:</label>
  <div class="controls">
    <!-- this selectd the field to search on -->
    <input type="hidden" name="search_field" value="interests" />

    <?php $this->search_form() ?>

  </div>
</div>

In order to place our custom dropdown, we need to build the rest of the search form as separate pieces. We do this by replacing the template function $this->search_form() which generates the rest of the search form, with the pieces we’ll need: the hidden field that provides that search operator and a function that displays the submit buttons:

<div class="control-group">
  <label class="control-label"><?php _e('Search', 'participants-database' )?>:</label>
  <div class="controls">
    <!-- this selectd the field to search on -->
    <input type="hidden" name="search_field" value="interests" />
    <!-- this provides the search operator -->
    <input name="operator" type="hidden" class="search-item" value="LIKE" />

    <?php echo $this->search_submit_buttons(); ?>
  </div>
  
</div>

OK, at this point we have the search form ready to have the dropdown inserted. An HTML dropdown control needs a list of values to present in the dropdown. In Participants Database, s dropdown field has a defined set of values (this is what is in the “values” space for the field definition on the Manage Database Fields page in the plugin admin) that the user can select when they are filling out a form. We need to get those values and make them into our dropdown search control.

First, to get those values, we get the set of defined properties for our field from the plugin configuration. Let’s write a simple function to get that done, and we can put our function at the top of the template. I’m not going to explain each step we take in this function, but what it does is this: given the name of a dropdown field, it provides an array of the defined values for that field.

/**
 * provides an array of dropdown values
 * @param string $name of the field
 * @return array
 */
function pdb_dropdown_values($name) {
  $field = Participants_Db::$fields[$name];
  if ($field) {
    $values = $field->options();
  }
  if (is_array($values)) {
    return $values;
  } else {
    return '';
  }
}

Now, we need to build our dropdown field. The plugin can do that for us, we just need to use the built-in function that it uses to print form elements. We give that function our values, such as the name of the field, what type of field it is and the values to use for the dropdown. Here’s what that looks like. Notice we’re giving it the result of our dropdown value function so it will display those values in the dropdown.

<?php
PDb_FormElement::print_element(
  array(
    'type' => 'dropdown',
    'name' => 'value',
    'options' => pdb_dropdown_values('interests'),
    'class' => 'search-item',
    'value' => filter_input(INPUT_POST, 'value', FILTER_SANITIZE_STRING)
    )
  );
?>

The “value” parameter is using a filter function to get the last selected value of the dropdown. The filter is necessary to secure the form submission against malicious use. This replaces the old not-secure method for using values from the POST array: $_POST[['value']] Careful coders generally don’t do it that way anymore.

So, this gives us our completed custom template, which I am providing here in it’s entirety:

Note: this post was updated August 20, 2019 to use the current Participants_Db::$fields value.

35 thoughts on “Using Dropdown Values in the List Search

  1. This is a great plugin and support site, thank you!

    I followed all your instructions below, and when I try putting in my own field called “display_name” instead of “interests,” in the 2 places you have “interests,” and it’s throwing this error:

    PHP Fatal error: Uncaught Error: Call to undefined method PDb_Form_Field_Def::values() in /nas/content/live//wp-content/plugins/participants-database/templates/pdb-list-isp.php:19…..

    My custom template is named pdb-list-isp.php. I’m not sure what I’m getting wrong. I’d like a dropdown at the top that’s populated with all the values for the field display_name, and then users can filter the list based on that dropdown. Thanks for any help!

    1. Thanks for pointing this out, it looks like the code in that tutorial is outdated. The method you should use instead is $field->options()

  2. Hello,
    How to do that in version 1.9.3.14 ?

    1. To do that I change function dropdown_values to this :

      function dropdown_values($name) {
        $field = new PDb_FormElement($name);
        global $wpdb;
        $query = ‘
          SELECT DISTINCT ‘ . $name . ‘
          FROM ‘ . $wpdb->prefix . ‘participants_database 
        ‘;
        
        // now execute the query and get the results
        $raw_names = $wpdb->get_results( $query );
        
        /*
         * now expand the result array into an array for the options 
         * property of the dropdown
         */
        $options = $field->options;
        foreach ( $raw_names as $record ) {
          
          // this is the value we’ll be considering
          $new_value = $record->{$name};
         
          /*
           * check the value against the defined options so we only 
           * add options that are not already in there
           */
          if ( ! in_array( $new_value, $options ) ) {
            // it’s a new value, so add it to the dropdown options
            $options[$new_value] = $new_value;
          }
        }
      
        return $options;
      }

      1. Thanks, Pierre, there is some pretty old code in this article, thanks for the update!

        I updated the body of the article to work with the current version of Participants Database.

  3. Wonderful.,
    just some problem left,
    how to change .contol-label class value “Search” and search button text to my language?
    also how to remove clear button as well?
    Thanks :)

    1. There are two ways to change the text, the first is to use the translation files. I have an article here that explains how that can be done.

      The other way to do this is to use a custom template. If you use the pdb-list-detailed.php template as your starting point, you can determine the text in the template.

      To hide the clear button, use can use some CSS like this:

      input.search-form-clear {
          display: none;
      }

      you can put that in the plugin settings “Custom CSS”

      1. Thanks for the reply, I would like to contribute my translation to id_ID after i finished it. it’s really long indeed :D
        could you take a look at my current project please, it works perfectly before. but after another user input more data, wp update, plugin update, theme update (i’m not sure what cause it) it seems not working quite right now.

      2. the page here

        i’m using short code for first page
        [pdb_search template=custom search_fields=provinsi target_page=Data-Peruqyah-Result]
        and then redirecting to another page
        [pdb_list template=customresponsive search=true display_count=true list_limit=10 fields=nama,alamat,telepon]

        please help.

        1. Make sure you are getting the correct “target_instance” value. This page explains that:

          Using the Search Shortcode

        2. still not working.,

          i’m putting “target_instance” in both page short code and still not solve the problem.

          i’m using custom template drop down values in the search list. the first search result showing no problem, but after i select another values in the list, it showing no result and sometimes redirecting to error page. so i remove search in my target page.

          also, next, previous, first and last navigation button in limited list showing error. but the direct navigation button works perfectly fine.

          even i’m using [pdb list] without custom template, problem still persist.

          before this customization i tested there’s no problem. i clear the cache and cookie., first attempt working, but the second the problem still persist.,

          please help.

        3. It sounds like the cache is interfering with the page functionality. I’d suggest turning the cache off for these pages and see how it works.

  4. Hi, I’m using pdb_list search=true. Whatever column I configure to display in the search result table, it will display in the search drop down list.
    Is it possible I have different set of column names in search drop down list and search result table? For example , I have 7 columns in the search result table but only have 3 values in the search drop down list where the 3 values are the same as the column name.

    1. There are two ways to do this. Probably the easiest is to look at the “sortable” checkbox in the field definition (Manage Database Fields page). Only fields that are marked as “sortable” will show up in the sort selector.

      The second way to do this is with a custom template where you can independently define which fields are available in the sort.

      1. Thanks Roland for your prompt reply. My question is more on search functionality than sort functionality. My understanding is that if I configure the fields to be display column, the searching field in the drop down list will be the same as search result column. Any simple way that I can use rather than using a custom template?

        1. Yes, you can use the the search_fields attribute of the shortcode to determine which fields are available in the search dropdown. For example:

          [pdb_list search=true search_fields="last_name,city,state"]

        2. I have followed your suggestions but it is not working. It is still showing the full list

        3. [pdb_list search=true search_fields="file_name"]

          “file_name” is the column name in wp_participants_database table but I can still see the full list appear in the search drop down list.

        4. This does work, I just tested it. Make sure the name of the column is exactly correct? Look at the shortcode in text view to make sure the syntax is correct. It looks correct in your post.

          If you have only one field defined for your search fields, there should be no dropdown to select the field, just the text box for the search term.

        5. I suspect it might due to version issue. I’m using Participants Database version 1.5.4.9.

        6. Yes, you should update the plugin.

  5. My second project with this system. I’ve got the drop down working but is there a way to restrict the results to match the exact search option (from the dropdown values) selected? My search values are strings of letters rather than words (choir voices – soprano, alto, tenor, bass – which are listed in combinations SA, SATB, SSATB, SSATBB , SATBB etc) , so if someone searches ‘SATB’ it also returns the the options containing ‘SATB’ but with additional letters either side. Hope that makes sense!

  6. This works a treat but is there a way to restrict the results to match the exact drop down search option selected? My search values are strings of letters rather than words (choir voices – soprano, alto, tenor, bass – which are listed in combinations SA, SATB, SSATB, SSATBB , SATBB etc) , so if someone searches ‘SATB’ it also returns the the options containing ‘SATB’ but with additional letters either side. Hope that makes sense!

    1. I suggest you use the “strict user searching” setting in the plugin settings under the “list display” tab.

      1. Thanks, perfect! Got the Combo Multi Search plugin now which makes this even better.

  7. Hi there. Thanks for a great plugin. The dropdown search works like a pro. But I was wondering, can you have a text line search with a dropdown search? For example I want to be able to search via doctor specialty in a dropdown, OR a doctor surname in a text field? Will the Combo Multisearch Add-On assist with that option? Thanks in advance.

    1. Hi Michelle,

      You are correct, the Combo Multisearch add-on for Participants Database will give you the ability to easily set that up.

  8. How difficult would it be to populate the dropdown list with the unique values from a column in the DB? Specifically, if I wanted to fill the dropdown with the cities that my participants come from, how would I perform that query from within the dropdown_values function?

    1. I’ve got a plugin add-on that will do that…not free, but it will save you the trouble.

      Combo Multisearch Add-On

      1. Great! I purchased the add-on and got the “Use DB Values in Dropdowns” functionality working!

        Is there a relatively painless way to remove either the search or sorting portion of the multisearch template like there is with the default templates? For example, on pdb_list I only want to show the cities dropdown I created without showing the multisearch field. And on pdb_search, I only want the multisearch without the dropdown.

        1. If you know a little PHP, it’s not hard, you’ll need to use custom templates for both shortcodes. If you look at the code in the multisearch templates, you’ll see that the combo search (the one that is a single text field) is placed separately from the multi search (the one where you get a separate control for every field) so you can take out the part you don’t what showing.

  9. Hello Roland!
    what a wonderful Plug in!
    I need to ask you a question:
    i’m trying to create a dropdown list at this link: http://www.ciics.it/testiamo/
    if you try to search for example “Brescia” or everithing else, the system gives me all results.

    can i contact you via email?

    1. It looks like there is a problem with your template. You should use the “pdb-list-detailed.php” template as your starting point for your custom template. Don’t use the [pdb_search] shortcode, you should use the [pdb_list] shortcode.

      1. Thank You!
        and another question, is it possible to have the page of the single participant less schematic?
        for example:
        Name: Lorenzo
        Surname: Manchi
        email: [email protected]….
        Phone: 3210…. ?

        1. To really get the most out of the single record display, you need to create a custom template so you can lay it out exactly as you want. The default template is mostly just a starting point to designing the display. One easy thing to try is to use the “bootstrap” template, which uses a bootstrap-compatible HTML and may get you closer to what you want. [pdb_single template=bootstrap]

          Using Custom Templates
          Single Record Custom Template Tutorial

Leave a Reply
You have to agree to the comment policy.

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

35 thoughts on “Using Dropdown Values in the List Search

  1. This is a great plugin and support site, thank you!

    I followed all your instructions below, and when I try putting in my own field called “display_name” instead of “interests,” in the 2 places you have “interests,” and it’s throwing this error:

    PHP Fatal error: Uncaught Error: Call to undefined method PDb_Form_Field_Def::values() in /nas/content/live//wp-content/plugins/participants-database/templates/pdb-list-isp.php:19…..

    My custom template is named pdb-list-isp.php. I’m not sure what I’m getting wrong. I’d like a dropdown at the top that’s populated with all the values for the field display_name, and then users can filter the list based on that dropdown. Thanks for any help!

    1. Thanks for pointing this out, it looks like the code in that tutorial is outdated. The method you should use instead is $field->options()

  2. Hello,
    How to do that in version 1.9.3.14 ?

    1. To do that I change function dropdown_values to this :

      function dropdown_values($name) {
        $field = new PDb_FormElement($name);
        global $wpdb;
        $query = ‘
          SELECT DISTINCT ‘ . $name . ‘
          FROM ‘ . $wpdb->prefix . ‘participants_database 
        ‘;
        
        // now execute the query and get the results
        $raw_names = $wpdb->get_results( $query );
        
        /*
         * now expand the result array into an array for the options 
         * property of the dropdown
         */
        $options = $field->options;
        foreach ( $raw_names as $record ) {
          
          // this is the value we’ll be considering
          $new_value = $record->{$name};
         
          /*
           * check the value against the defined options so we only 
           * add options that are not already in there
           */
          if ( ! in_array( $new_value, $options ) ) {
            // it’s a new value, so add it to the dropdown options
            $options[$new_value] = $new_value;
          }
        }
      
        return $options;
      }

      1. Thanks, Pierre, there is some pretty old code in this article, thanks for the update!

        I updated the body of the article to work with the current version of Participants Database.

  3. Wonderful.,
    just some problem left,
    how to change .contol-label class value “Search” and search button text to my language?
    also how to remove clear button as well?
    Thanks :)

    1. There are two ways to change the text, the first is to use the translation files. I have an article here that explains how that can be done.

      The other way to do this is to use a custom template. If you use the pdb-list-detailed.php template as your starting point, you can determine the text in the template.

      To hide the clear button, use can use some CSS like this:

      input.search-form-clear {
          display: none;
      }

      you can put that in the plugin settings “Custom CSS”

      1. Thanks for the reply, I would like to contribute my translation to id_ID after i finished it. it’s really long indeed :D
        could you take a look at my current project please, it works perfectly before. but after another user input more data, wp update, plugin update, theme update (i’m not sure what cause it) it seems not working quite right now.

      2. the page here

        i’m using short code for first page
        [pdb_search template=custom search_fields=provinsi target_page=Data-Peruqyah-Result]
        and then redirecting to another page
        [pdb_list template=customresponsive search=true display_count=true list_limit=10 fields=nama,alamat,telepon]

        please help.

        1. Make sure you are getting the correct “target_instance” value. This page explains that:

          Using the Search Shortcode

        2. still not working.,

          i’m putting “target_instance” in both page short code and still not solve the problem.

          i’m using custom template drop down values in the search list. the first search result showing no problem, but after i select another values in the list, it showing no result and sometimes redirecting to error page. so i remove search in my target page.

          also, next, previous, first and last navigation button in limited list showing error. but the direct navigation button works perfectly fine.

          even i’m using [pdb list] without custom template, problem still persist.

          before this customization i tested there’s no problem. i clear the cache and cookie., first attempt working, but the second the problem still persist.,

          please help.

        3. It sounds like the cache is interfering with the page functionality. I’d suggest turning the cache off for these pages and see how it works.

  4. Hi, I’m using pdb_list search=true. Whatever column I configure to display in the search result table, it will display in the search drop down list.
    Is it possible I have different set of column names in search drop down list and search result table? For example , I have 7 columns in the search result table but only have 3 values in the search drop down list where the 3 values are the same as the column name.

    1. There are two ways to do this. Probably the easiest is to look at the “sortable” checkbox in the field definition (Manage Database Fields page). Only fields that are marked as “sortable” will show up in the sort selector.

      The second way to do this is with a custom template where you can independently define which fields are available in the sort.

      1. Thanks Roland for your prompt reply. My question is more on search functionality than sort functionality. My understanding is that if I configure the fields to be display column, the searching field in the drop down list will be the same as search result column. Any simple way that I can use rather than using a custom template?

        1. Yes, you can use the the search_fields attribute of the shortcode to determine which fields are available in the search dropdown. For example:

          [pdb_list search=true search_fields="last_name,city,state"]

        2. I have followed your suggestions but it is not working. It is still showing the full list

        3. [pdb_list search=true search_fields="file_name"]

          “file_name” is the column name in wp_participants_database table but I can still see the full list appear in the search drop down list.

        4. This does work, I just tested it. Make sure the name of the column is exactly correct? Look at the shortcode in text view to make sure the syntax is correct. It looks correct in your post.

          If you have only one field defined for your search fields, there should be no dropdown to select the field, just the text box for the search term.

        5. I suspect it might due to version issue. I’m using Participants Database version 1.5.4.9.

        6. Yes, you should update the plugin.

  5. My second project with this system. I’ve got the drop down working but is there a way to restrict the results to match the exact search option (from the dropdown values) selected? My search values are strings of letters rather than words (choir voices – soprano, alto, tenor, bass – which are listed in combinations SA, SATB, SSATB, SSATBB , SATBB etc) , so if someone searches ‘SATB’ it also returns the the options containing ‘SATB’ but with additional letters either side. Hope that makes sense!

  6. This works a treat but is there a way to restrict the results to match the exact drop down search option selected? My search values are strings of letters rather than words (choir voices – soprano, alto, tenor, bass – which are listed in combinations SA, SATB, SSATB, SSATBB , SATBB etc) , so if someone searches ‘SATB’ it also returns the the options containing ‘SATB’ but with additional letters either side. Hope that makes sense!

    1. I suggest you use the “strict user searching” setting in the plugin settings under the “list display” tab.

      1. Thanks, perfect! Got the Combo Multi Search plugin now which makes this even better.

  7. Hi there. Thanks for a great plugin. The dropdown search works like a pro. But I was wondering, can you have a text line search with a dropdown search? For example I want to be able to search via doctor specialty in a dropdown, OR a doctor surname in a text field? Will the Combo Multisearch Add-On assist with that option? Thanks in advance.

    1. Hi Michelle,

      You are correct, the Combo Multisearch add-on for Participants Database will give you the ability to easily set that up.

  8. How difficult would it be to populate the dropdown list with the unique values from a column in the DB? Specifically, if I wanted to fill the dropdown with the cities that my participants come from, how would I perform that query from within the dropdown_values function?

    1. I’ve got a plugin add-on that will do that…not free, but it will save you the trouble.

      Combo Multisearch Add-On

      1. Great! I purchased the add-on and got the “Use DB Values in Dropdowns” functionality working!

        Is there a relatively painless way to remove either the search or sorting portion of the multisearch template like there is with the default templates? For example, on pdb_list I only want to show the cities dropdown I created without showing the multisearch field. And on pdb_search, I only want the multisearch without the dropdown.

        1. If you know a little PHP, it’s not hard, you’ll need to use custom templates for both shortcodes. If you look at the code in the multisearch templates, you’ll see that the combo search (the one that is a single text field) is placed separately from the multi search (the one where you get a separate control for every field) so you can take out the part you don’t what showing.

  9. Hello Roland!
    what a wonderful Plug in!
    I need to ask you a question:
    i’m trying to create a dropdown list at this link: http://www.ciics.it/testiamo/
    if you try to search for example “Brescia” or everithing else, the system gives me all results.

    can i contact you via email?

    1. It looks like there is a problem with your template. You should use the “pdb-list-detailed.php” template as your starting point for your custom template. Don’t use the [pdb_search] shortcode, you should use the [pdb_list] shortcode.

      1. Thank You!
        and another question, is it possible to have the page of the single participant less schematic?
        for example:
        Name: Lorenzo
        Surname: Manchi
        email: [email protected]….
        Phone: 3210…. ?

        1. To really get the most out of the single record display, you need to create a custom template so you can lay it out exactly as you want. The default template is mostly just a starting point to designing the display. One easy thing to try is to use the “bootstrap” template, which uses a bootstrap-compatible HTML and may get you closer to what you want. [pdb_single template=bootstrap]

          Using Custom Templates
          Single Record Custom Template Tutorial

Leave a Reply
You have to agree to the comment policy.

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