Posted on by

Pre-load a “Chosen” Dropdown with Options from the Database

The Chosen Dropdown add-on for Participants Database gives you the ability to use javascript-powered dropdown selectors in your forms. Normally, these dropdowns are populated with a static set of values in the field definition.

This tutorial shows how to populate your Chosen dropdown with values from the database.

For our tutorial here, we’re going to do something very simple: the field (named “Specialty”) is a Chosen Dropdown Other field where users can type in an additional value that is not on the preset list. What this does is make it possible for another person to select that new option in their form, giving you an essentially user-defined list of options for the dropdown.

Note that this code will not work with “multi-select” type fields.

Getting the Values

The core of this function (happens around line 30) is the database query that gets all the values that people have saved. If you are adapting something like this for your own purposes, you will need to be able to change this part of the function to get whatever list of options you want to use. This will require some knowledge of database queries, but WordPress makes this pretty simple.

To start with, we are simply going to query the database for all the unique values that have been saved. Then we add the ones that aren’t already present in the list of options to the preset values to get our final list of options.

Setting the Field’s New Options

Setting the field options is very simple, we simply need an array of values, then we set the “options” property of the field to that array. In this case, it’s a simple indexed array, so the saved value and what you see in the dropdown are exactly the same.

Participants Database also supports “value titles” where the visible part of the option can be different from the value that gets saved. You just need to construct your array as an associative array where the title is the key and the value is the value in each array element.

Here’s the Code

I tried to make the plugin code simple and well-commented so it’s easy to understand what is going on here.

As always, go to the gist page and download the zip file if you want to install this plugin.

42 thoughts on “Pre-load a “Chosen” Dropdown with Options from the Database

  1. Hi Roland i have an elementor pro plugin , i have a worker databases that i need every day this workers submit some information .. i have an external mysql database , the database have a users table, i need a select in my elementor form with all the users names..

    can you brinme some code to do that? i read touse a webhook ? is the only way?

    thank you in advance

    1. If you need some custom code and you cannot write it yourself, you will need to find a coder to help you with that, I cannot provide custom code. This article provides you with an example, but you are expected to have the technical knowledge to adapt it to what you want to do. It is possible, but I cannot write the code for you, I’m sorry.

  2. Hi Roland,

    How do I set the value and the text of the options to be different? I’ve tried lots of things, but nothing seems to work for me. Is it possible?

    Thanks

    1. This is possible, you need to put your title in the key of the option in the options array…for example, on the line where the option is added to the array:

      $options[] = $record->first_name . ‘ ‘ . $record->last_name;

      Place your title in the key like this:

      $options[ $title ] = $record->first_name . ' ' . $record->last_name;

      You must set the value of $title such that it is different for each option. If you try to give two options the same title, you will only get the second one because it will overwrite the first one.

      1. Thanks so much, so simple!

        One other thing I’d like to be able to do is to have the dropdown list specific to the current record_id. Is it possible to get the record_id with php so that I can specify it in the WHERE clause?

        Many thanks

        1. You can use the record_id property of the $field object, like this: $field->record_id

        2. Hi Roland,

          I tried using the following but it returns an empty dataset

          WHERE record_id='. $field->record_id . '

          If I replace $field->record_id with 466 (the actual record_id of the test page), it works

        3. Take a look at the wp_participants_database table, there is no “record_id” column, you are looking for the “id” column.

          I just tested this, looks like it is working with that change.

  3. Hi again, Roland,
    I’m trying to do what your tutorial above is saying, to pre-load the chosen fields. What are people saying to install the PDB populate chosen dropdown? My chosen dropdown files don’t include a populate chosen dropdown file. Do I just create one somewhere? In the same folder as the chosen dropdown php file? and then put the function where? Or does it work automatically? I have 4 fields in the Participant Record that uses the chosen dropdown menu. Thanks!

    1. This refers to installing the plugin example code that I am providing. Check out this page if you’re unsure how to do that: Installing a WordPress Plugin from a Gist

    2. Oh, one more thing: you will definitely need php skills to get this working, especially if you want to do it to multiple fields. The code I provide is only an example, you will need to modify it to work for your purposes.

  4. I just purchased the Chosen Dropdown plugin.

    Trying to add Sates and Cities:

    Alabama::optgroup, Birmingham::X, Montgomery, Mobile, Huntsville,

    I want the user to see Birmingham in the list(list the user selects from), but see X on the frontend (Master list where all the selections are shown)

    Only showing Birmingham

    What am I doing wrong??

    1. Take a look at the docs where it is explained how to set up options for your dropdown. You probably don’t nee the “::X” part of that option.

  5. You cannot have commas in your field options, you can get around this by using the html entity instead:

    ,

    1. The values don’t have any commas. I believe the array may be adding the commas…

      This is my data input, which seems correct:

      a:2:{i:0;s:24:”Johnny Galecki (Faculty)”;i:1;s:20:”Test Dummy (Faculty)”;}

      It’s just the display of the “search-choice” li tag is not correct when I go back to update my profile. It’s not splitting the choices out into li’s….

      1. I’ve posted the whole code to the forum… maybe that’ll help. Sorry.

        1. answering the same question form the same person in two different venues is not preferred, I either don’t answer one of them or have to answer twice.

    2. I’ve got a problem.
      I’m a diletant in this field and I’d like a help, if possible. My task is to help Society of Clinical Speach Therapists with their page. They want to display a single ‘search function’ – ‘search family name’ –> who was “accredited and certified as clinical speach therapist”.
      I’ve done a lot of tries, but unfortunately unsuccessfully. Still You platform seems to be the very best for this purpose, I installed it, it seems powerful…

      Unfortunately, despite of reading almost the most of instructions, forums, etc., I could not reach the results and outcome we’d like to.

      Could you please assist me, if possible, even if its private or charged etc. I can give access to the page backend in private. If You have a time to help me, cetainly we make a donation and mention Your plugin at the EKLS web page.

      Very best and thanks in advance,
      Meris

      1. Hi Meris,

        I can’t provide you with this kind of service (it’s a matter of not having the time), but if you have a specific question about configuring the plugin, I’m happy to help.

        For example, if you want your search form to only search on the Last Name field, you can configure that in the shortcode like this:

        [pdb_list search=true search_fields=last_name]

        If you want to limit the list display to only certified providers, you can do that with a filter in the shortcode. This is explained here: List Shortcode Filters

        1. Greatest thanks! I really appreciate the time You’ve spent and apologise.

          But now I got the main, seems the biggest problem. I created search form and it displays in frontend with: [pdb_list template=flexbox search=true filter="approved=yes" search_fields="perenimi,eesnimi"] (where perenimi is last_name and eesnimi is first_name in database),

          But HOW to get the search results visible on the page?
          (if I write wrong name, it shows “Not found”, but if to write right name, it shows nothing…) :(

          (on the webpage, one of correct search fist_name (eesnimi) is Meris )

        2. Can you provide a link so I can see it?

        3. Dear Roland!
          Finally I succeed to display search results etc, it works fine now. But now I have more tricky task to solve here – I use [supress=true] to avoid list to be seen before search button was pressed, this worked fine.
          But, search works also if I insert just first letter of family name and all participants are displayed, who’s faimily mane begins with this letter. My problem is – how to make search work ONLY IF the full (precise) family name is inserted into search field, not just first letter or part of it? Do You have some hints to give me? (https://ekls/lk-otsing) – You can try letter “m” in search filed.

        4. You can try the “Whole Word Match Only” setting under the combo search tab in the Combo Multisearch settings.

        5. Thank You! Great, I installed Combo multisearch and mach whole word works well. The only problem, until now search/clear buttons and result count line were translated into Esonian, after Combo installation they are only in English. Can I change it /translate it?

        6. Actually, it seems I solved problems with translation, thank You for submitting also POT file together with plugin languages :)

        7. thanks, I was just going to explain that. If you want to send me your translation files, I’d be happy to include them in the plugin.

  6. Roland, I have this somewhat working with Chosen Multi Dropdown…. It works up until I click SAVE and when it refreshes, it’s joining my choices into one tag such as this:

    Johnny Galecki (Faculty), Test Dummy (Faculty)<a></a>

    That is originally two choices. Do you know of a fix? Thanks

  7. Hi Roland,

    I’ve got this working to an extent, but the options loading from the database have a strange format. See https://imgur.com/a/XeaknpP

    Is this because it’s a “chosen multi dropdown”? In any case I’d appreciate some guidance. Many thanks.

    1. Hi Neil,

      That is what will happen if you use a multi-select type field….because the field can have multiple values, you’re getting arrays back from the db instead of a simple value. If you want to stick with doing this with a multi-select, you’ll have to modify the code so that when it gets the value from the db, it might be a serialized array. If that’s the case it needs to be unserialized, and then each value in that array looked at for unique values. It is not a simple mod and so you will need to have some php skills to pull it off.

      I updated the tutorial to make this clear.

      1. Thanks Roland, this makes sense.

  8. hi, Roland, I have purchased and installed Chosen Dropdown Element add-on plugin but I have a problem with the search box in a dropdown field it does not exist I have changed the number in Disable Search Threshold but nothing the problem I have purchased this addon for this purpose can you help pls thank you

    1. I will really need to see this in action to help you with this. It is possible there is some other javascript or even some CSS that is interfering with the normal operation of the Chosen Selector.

      1. use this code to enter my website 2512 because is not ready to make it public but when you enter change the language i menu and click register button to see my problem in city field dropdown thank you

        1. If your site is multilingual, that may not works as expected with the chosen dropdown autosuggest.

          If you want to send me a private link you can send it to support@xnau.com.

  9. thank you roland for this awesome plugin i want to know how i can add autocomplete search box in dropdown field
    for example i have set dropdown field for cities and i have long list i need search box to allow users to use it for choosing name of city thank you

    1. You can use the Chosen Selector add-on for this: it provides an autosuggest that show possible choices as the person types.

  10. Hi Roland,

    I purchased and installed the Chosen Dropdown Element add-on plugin. I then ran through the code posted above – installed the PDB populate chosen dropdown. But, in my signup and record edit pages, I can’t get the dropdown to show any values. Do I need to change the code in the pdb-populate-dropdown.php to recognize my fields? I did not change anything in that code and just installed the .zip file that I downloaded.

    Thanks!

    1. Yes, you must edit the code in the example for it to work on your site. You can do this using the WordPress built-in plugin editor, or you can edit the file on your computer, then upload it using FTP.

      1. That makes sense, thanks! So, stupid question… do I change the field name of “specialty” to my own field name, in line 23?

        // this is the name of the field we want to add options to
        $fieldname = ‘specialty’;

        And, is there a way to add more than one field, or can this plugin only do just one chosen field?

        1. You will need to have an understanding of php and probably mysql to successfully use this plugin. Yes, you need to change the field that the plugin operates on, and yes, you can make it work on multiple fields, but knowing exactly how you need to do that is up to you.

        2. Thanks Roland. Changing the ‘specialty’ field to my field name worked. Now I just need to look up how to make it work for more than one field without breaking it!

          For sorting my results alphabetically in the dropdown list, would you suggest I sort the array that is made or should it come from an “order by” SQL function in the SQL statement?

        3. Just to follow up, using ORDER BY in the query got me results in alphabetical order:

          $query = ‘
          SELECT DISTINCT

          ’ . $fieldname . ‘

          FROM ‘ . $wpdb->prefix . ‘participants_database
          ORDER BY

          ’ . $fieldname . ‘

          ’;

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.

42 thoughts on “Pre-load a “Chosen” Dropdown with Options from the Database

  1. Hi Roland i have an elementor pro plugin , i have a worker databases that i need every day this workers submit some information .. i have an external mysql database , the database have a users table, i need a select in my elementor form with all the users names..

    can you brinme some code to do that? i read touse a webhook ? is the only way?

    thank you in advance

    1. If you need some custom code and you cannot write it yourself, you will need to find a coder to help you with that, I cannot provide custom code. This article provides you with an example, but you are expected to have the technical knowledge to adapt it to what you want to do. It is possible, but I cannot write the code for you, I’m sorry.

  2. Hi Roland,

    How do I set the value and the text of the options to be different? I’ve tried lots of things, but nothing seems to work for me. Is it possible?

    Thanks

    1. This is possible, you need to put your title in the key of the option in the options array…for example, on the line where the option is added to the array:

      $options[] = $record->first_name . ‘ ‘ . $record->last_name;

      Place your title in the key like this:

      $options[ $title ] = $record->first_name . ' ' . $record->last_name;

      You must set the value of $title such that it is different for each option. If you try to give two options the same title, you will only get the second one because it will overwrite the first one.

      1. Thanks so much, so simple!

        One other thing I’d like to be able to do is to have the dropdown list specific to the current record_id. Is it possible to get the record_id with php so that I can specify it in the WHERE clause?

        Many thanks

        1. You can use the record_id property of the $field object, like this: $field->record_id

        2. Hi Roland,

          I tried using the following but it returns an empty dataset

          WHERE record_id='. $field->record_id . '

          If I replace $field->record_id with 466 (the actual record_id of the test page), it works

        3. Take a look at the wp_participants_database table, there is no “record_id” column, you are looking for the “id” column.

          I just tested this, looks like it is working with that change.

  3. Hi again, Roland,
    I’m trying to do what your tutorial above is saying, to pre-load the chosen fields. What are people saying to install the PDB populate chosen dropdown? My chosen dropdown files don’t include a populate chosen dropdown file. Do I just create one somewhere? In the same folder as the chosen dropdown php file? and then put the function where? Or does it work automatically? I have 4 fields in the Participant Record that uses the chosen dropdown menu. Thanks!

    1. This refers to installing the plugin example code that I am providing. Check out this page if you’re unsure how to do that: Installing a WordPress Plugin from a Gist

    2. Oh, one more thing: you will definitely need php skills to get this working, especially if you want to do it to multiple fields. The code I provide is only an example, you will need to modify it to work for your purposes.

  4. I just purchased the Chosen Dropdown plugin.

    Trying to add Sates and Cities:

    Alabama::optgroup, Birmingham::X, Montgomery, Mobile, Huntsville,

    I want the user to see Birmingham in the list(list the user selects from), but see X on the frontend (Master list where all the selections are shown)

    Only showing Birmingham

    What am I doing wrong??

    1. Take a look at the docs where it is explained how to set up options for your dropdown. You probably don’t nee the “::X” part of that option.

  5. You cannot have commas in your field options, you can get around this by using the html entity instead:

    ,

    1. The values don’t have any commas. I believe the array may be adding the commas…

      This is my data input, which seems correct:

      a:2:{i:0;s:24:”Johnny Galecki (Faculty)”;i:1;s:20:”Test Dummy (Faculty)”;}

      It’s just the display of the “search-choice” li tag is not correct when I go back to update my profile. It’s not splitting the choices out into li’s….

      1. I’ve posted the whole code to the forum… maybe that’ll help. Sorry.

        1. answering the same question form the same person in two different venues is not preferred, I either don’t answer one of them or have to answer twice.

    2. I’ve got a problem.
      I’m a diletant in this field and I’d like a help, if possible. My task is to help Society of Clinical Speach Therapists with their page. They want to display a single ‘search function’ – ‘search family name’ –> who was “accredited and certified as clinical speach therapist”.
      I’ve done a lot of tries, but unfortunately unsuccessfully. Still You platform seems to be the very best for this purpose, I installed it, it seems powerful…

      Unfortunately, despite of reading almost the most of instructions, forums, etc., I could not reach the results and outcome we’d like to.

      Could you please assist me, if possible, even if its private or charged etc. I can give access to the page backend in private. If You have a time to help me, cetainly we make a donation and mention Your plugin at the EKLS web page.

      Very best and thanks in advance,
      Meris

      1. Hi Meris,

        I can’t provide you with this kind of service (it’s a matter of not having the time), but if you have a specific question about configuring the plugin, I’m happy to help.

        For example, if you want your search form to only search on the Last Name field, you can configure that in the shortcode like this:

        [pdb_list search=true search_fields=last_name]

        If you want to limit the list display to only certified providers, you can do that with a filter in the shortcode. This is explained here: List Shortcode Filters

        1. Greatest thanks! I really appreciate the time You’ve spent and apologise.

          But now I got the main, seems the biggest problem. I created search form and it displays in frontend with: [pdb_list template=flexbox search=true filter="approved=yes" search_fields="perenimi,eesnimi"] (where perenimi is last_name and eesnimi is first_name in database),

          But HOW to get the search results visible on the page?
          (if I write wrong name, it shows “Not found”, but if to write right name, it shows nothing…) :(

          (on the webpage, one of correct search fist_name (eesnimi) is Meris )

        2. Can you provide a link so I can see it?

        3. Dear Roland!
          Finally I succeed to display search results etc, it works fine now. But now I have more tricky task to solve here – I use [supress=true] to avoid list to be seen before search button was pressed, this worked fine.
          But, search works also if I insert just first letter of family name and all participants are displayed, who’s faimily mane begins with this letter. My problem is – how to make search work ONLY IF the full (precise) family name is inserted into search field, not just first letter or part of it? Do You have some hints to give me? (https://ekls/lk-otsing) – You can try letter “m” in search filed.

        4. You can try the “Whole Word Match Only” setting under the combo search tab in the Combo Multisearch settings.

        5. Thank You! Great, I installed Combo multisearch and mach whole word works well. The only problem, until now search/clear buttons and result count line were translated into Esonian, after Combo installation they are only in English. Can I change it /translate it?

        6. Actually, it seems I solved problems with translation, thank You for submitting also POT file together with plugin languages :)

        7. thanks, I was just going to explain that. If you want to send me your translation files, I’d be happy to include them in the plugin.

  6. Roland, I have this somewhat working with Chosen Multi Dropdown…. It works up until I click SAVE and when it refreshes, it’s joining my choices into one tag such as this:

    Johnny Galecki (Faculty), Test Dummy (Faculty)<a></a>

    That is originally two choices. Do you know of a fix? Thanks

  7. Hi Roland,

    I’ve got this working to an extent, but the options loading from the database have a strange format. See https://imgur.com/a/XeaknpP

    Is this because it’s a “chosen multi dropdown”? In any case I’d appreciate some guidance. Many thanks.

    1. Hi Neil,

      That is what will happen if you use a multi-select type field….because the field can have multiple values, you’re getting arrays back from the db instead of a simple value. If you want to stick with doing this with a multi-select, you’ll have to modify the code so that when it gets the value from the db, it might be a serialized array. If that’s the case it needs to be unserialized, and then each value in that array looked at for unique values. It is not a simple mod and so you will need to have some php skills to pull it off.

      I updated the tutorial to make this clear.

      1. Thanks Roland, this makes sense.

  8. hi, Roland, I have purchased and installed Chosen Dropdown Element add-on plugin but I have a problem with the search box in a dropdown field it does not exist I have changed the number in Disable Search Threshold but nothing the problem I have purchased this addon for this purpose can you help pls thank you

    1. I will really need to see this in action to help you with this. It is possible there is some other javascript or even some CSS that is interfering with the normal operation of the Chosen Selector.

      1. use this code to enter my website 2512 because is not ready to make it public but when you enter change the language i menu and click register button to see my problem in city field dropdown thank you

        1. If your site is multilingual, that may not works as expected with the chosen dropdown autosuggest.

          If you want to send me a private link you can send it to support@xnau.com.

  9. thank you roland for this awesome plugin i want to know how i can add autocomplete search box in dropdown field
    for example i have set dropdown field for cities and i have long list i need search box to allow users to use it for choosing name of city thank you

    1. You can use the Chosen Selector add-on for this: it provides an autosuggest that show possible choices as the person types.

  10. Hi Roland,

    I purchased and installed the Chosen Dropdown Element add-on plugin. I then ran through the code posted above – installed the PDB populate chosen dropdown. But, in my signup and record edit pages, I can’t get the dropdown to show any values. Do I need to change the code in the pdb-populate-dropdown.php to recognize my fields? I did not change anything in that code and just installed the .zip file that I downloaded.

    Thanks!

    1. Yes, you must edit the code in the example for it to work on your site. You can do this using the WordPress built-in plugin editor, or you can edit the file on your computer, then upload it using FTP.

      1. That makes sense, thanks! So, stupid question… do I change the field name of “specialty” to my own field name, in line 23?

        // this is the name of the field we want to add options to
        $fieldname = ‘specialty’;

        And, is there a way to add more than one field, or can this plugin only do just one chosen field?

        1. You will need to have an understanding of php and probably mysql to successfully use this plugin. Yes, you need to change the field that the plugin operates on, and yes, you can make it work on multiple fields, but knowing exactly how you need to do that is up to you.

        2. Thanks Roland. Changing the ‘specialty’ field to my field name worked. Now I just need to look up how to make it work for more than one field without breaking it!

          For sorting my results alphabetically in the dropdown list, would you suggest I sort the array that is made or should it come from an “order by” SQL function in the SQL statement?

        3. Just to follow up, using ORDER BY in the query got me results in alphabetical order:

          $query = ‘
          SELECT DISTINCT

          ’ . $fieldname . ‘

          FROM ‘ . $wpdb->prefix . ‘participants_database
          ORDER BY

          ’ . $fieldname . ‘

          ’;

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.