Posted on by

Populate a Form Dropdown from the Database

Sometimes, you want to give the user the ability to select from a list if things that are in the database, rather than a fixed set of options as defined in the field definition. This will require some simple PHP along with a database query, but once you get the idea, a lot of other possibilities will become available to you.

Our Example

So, for this tutorial, let’s say you have a signup form for volunteers. Your volunteers fill out the form and indicate that they are willing to volunteer their time for a project. Let’s say you also have volunteer coordinators that have a different signup form where they can select a volunteer to assign to the project. (I’ve explained how to set up different signup forms for different kinds of people in another tutorial.)

When the volunteer coordinator signs up, you will want to provide them with a dropdown selector that lists all of the volunteers. The only way to do that is to get the list of volunteers from the database and then use that to populate the dropdown.

The Query

To get our list of volunteers from the database, we need a query that gathers the information we need (in this case, their name) from the records that we know are from volunteers. Referring to the method in the Multiple Registration Forms article, we have a field in the form that is filled with the name of the page the form was on. This is how we will know whether the record was created by a volunteer or not. Let’s say the volunteer’s form is on a page named “volunteer-signup” and the coordinator’s form is on a page called “volunteer-coordinators.” To get one and not the other we need two statements.

Here is our query:

SELECT first_name,last_name 
FROM wp_participants_database
WHERE form_type LIKE "%volunteer%" 
  AND form_type NOT LIKE "%coordinator%"

That will get us our list of names of all the volunteers.

With WordPress, it’s best to use the $wpdb object for interacting with the database. To get an array of values, we use the $wpdb->get_results method like this:

<?php 
global $wpdb;
$values = $wpdb->get_col( '
   SELECT first_name,last_name 
   FROM `' . $wpdb->prefix . 'participants_database` 
   WHERE form_type LIKE "%volunteer%" 
     AND form_type NOT LIKE "%coordinator%"
' ); 
?>

There is no user input here so we don’t need to sanitize the query. This will give us an array of objects, one for each record returned.

Altering the Dropdown

This part is pretty simple if you are familiar with using WordPress actions. When the action is triggered, our function will be called, and it is given the Participants Database form element object for the current form element. Our action, in this case is documented as: pdb-form_element_build_{$type} which means if we want to use it on a dropdown-type form element, the final action slug will be pdb-form_element_build_dropdown. The action is triggered on every dropdown form element, so we need to check to see if the current element is the one we want to add the options to. Because it is an action, we won’t be returning any value. As is often the case with actions, we can change the state of the given object in the function that is triggered by the action. When that object is used next (such as to print the form element) it will include our changes.

The Plugin

Here is the fully-commented code for the plugin:

To install this plugin directly, go to the gist page, download the zip file, then upload it to your WordPress using the “Add Plugin” functionality.

10 thoughts on “Populate a Form Dropdown from the Database

  1. Hi Roland,
    I just want to get the number of row in participant database containing a certain value from a field.
    Unfortunately, when I do copy your code even deleting the content of the “xnau_set_volunteer_dropdown_options” function, it give me a blank page.
    I just copy the add_action with the associated function in the function.php of the template.
    Do you have any idea of this?
    Thks! :)

    1. Hi Alexadre,

      Usually a blank page indicates a syntax error in your script. Take a close look at your script and make sure it is all correct. I can’t tell you what the error is, and the tutorial does require you have some knowledge of PHP.

      1. First of all, thanks answering me so quickly.
        I deal a lot with php, I am not just used with wordpress, anyway, I do know about blank space but the thing is that I just copied that in my function.php of template :

        add_action( ‘pdb-form_element_build_dropdown’, ‘xnau_set_volunteer_dropdown_options’);

        function xnau_set_volunteer_dropdown_options($field)
        {
        echo $field->name;
        }

        And I have an 500 internal error when I check the debug toolbar on chrome.

        If I do delete this code, it does work perfectly.

        1. Check to make sure the context where you pasted that function is correct. The 500 probably indicates a syntax error, but there are no errors in the code you posted, so it must be due to where in the functions.php file you pasted it.

        2. Another thought: look at the PHP error log (if you have it set up) it sometimes provides helpful info.

  2. If I install the ZIP file at the bottom of the page, then the “Manage Database Fields” page will be corrupted when all Dropdown stops working.

    1. Thanks very much for bringing this to my attention, I’m sorry about that, the zip file link was for the wrong code. I have updated the article with instructions for getting the correct zip file for you to try.

  3. Hi Roland,

    Thanks for the reply. You were spot on, no idea why I didn’t think to check if it was saving the DB but yes it is. It’s just not showing up on the front end admin panel, just shows an empty dropdown selection box.

    I’ll do some tinkering, at least it’s saving perfectly. Thanks for the help again!

    James

  4. Hi mate,

    Cracking plugin. I’m pulling data from a custom DB table into the dropdown no problem but I can’t get the user selection to save? Any ideas?

    I’m pushing the options into an if statement as I need to check they enter a valid value on the step above, do think this is screwing up the save? It’s a simple if($options) statement so can’t see if it conflicting?

    1. James, your question is a little short on detail, but if you’re filtering the post data (using either of the plugin’s “before_signup” or “before_update” filters) make sure your data is getting returned OK by your filter function.

      If all you’re doing is checking that any value was chosen, then you can use the built-in validation for that.

      Finally, check the database record directly for the possibility that the value is getting saved, but you’re just not seeing it after the submission for some reason.

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

10 thoughts on “Populate a Form Dropdown from the Database

  1. Hi Roland,
    I just want to get the number of row in participant database containing a certain value from a field.
    Unfortunately, when I do copy your code even deleting the content of the “xnau_set_volunteer_dropdown_options” function, it give me a blank page.
    I just copy the add_action with the associated function in the function.php of the template.
    Do you have any idea of this?
    Thks! :)

    1. Hi Alexadre,

      Usually a blank page indicates a syntax error in your script. Take a close look at your script and make sure it is all correct. I can’t tell you what the error is, and the tutorial does require you have some knowledge of PHP.

      1. First of all, thanks answering me so quickly.
        I deal a lot with php, I am not just used with wordpress, anyway, I do know about blank space but the thing is that I just copied that in my function.php of template :

        add_action( ‘pdb-form_element_build_dropdown’, ‘xnau_set_volunteer_dropdown_options’);

        function xnau_set_volunteer_dropdown_options($field)
        {
        echo $field->name;
        }

        And I have an 500 internal error when I check the debug toolbar on chrome.

        If I do delete this code, it does work perfectly.

        1. Check to make sure the context where you pasted that function is correct. The 500 probably indicates a syntax error, but there are no errors in the code you posted, so it must be due to where in the functions.php file you pasted it.

        2. Another thought: look at the PHP error log (if you have it set up) it sometimes provides helpful info.

  2. If I install the ZIP file at the bottom of the page, then the “Manage Database Fields” page will be corrupted when all Dropdown stops working.

    1. Thanks very much for bringing this to my attention, I’m sorry about that, the zip file link was for the wrong code. I have updated the article with instructions for getting the correct zip file for you to try.

  3. Hi Roland,

    Thanks for the reply. You were spot on, no idea why I didn’t think to check if it was saving the DB but yes it is. It’s just not showing up on the front end admin panel, just shows an empty dropdown selection box.

    I’ll do some tinkering, at least it’s saving perfectly. Thanks for the help again!

    James

  4. Hi mate,

    Cracking plugin. I’m pulling data from a custom DB table into the dropdown no problem but I can’t get the user selection to save? Any ideas?

    I’m pushing the options into an if statement as I need to check they enter a valid value on the step above, do think this is screwing up the save? It’s a simple if($options) statement so can’t see if it conflicting?

    1. James, your question is a little short on detail, but if you’re filtering the post data (using either of the plugin’s “before_signup” or “before_update” filters) make sure your data is getting returned OK by your filter function.

      If all you’re doing is checking that any value was chosen, then you can use the built-in validation for that.

      Finally, check the database record directly for the possibility that the value is getting saved, but you’re just not seeing it after the submission for some reason.

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