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_col 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.
Roland Barker 
Matthew Jones 
SJ 
Ryan Carmichael 
Matt 
alexandre peneau 
James
Hi Roland
I have been trying for almost a week to resolve this problem without success. Please help.
I can not get the dropdown field to populate data from the Participants DB. The only data populating is from the set data in the (recipients) dropdown field created in the Manage Database Fields options.
I have installed the PDB Volunteer plugin and made the minimal of required changes.
Changes made:
From
name === ‘volunteers’
to
name === ‘recipients’
From
SELECT first_name,last_name
WHERE form_type LIKE volunteer AND form_type NOT LIKE coordinator
to
SELECT organisation_name
WHERE dataset LIKE organisations
From
record->first_name . ‘ ‘ . $record->last_name;
to
record->organisation_name;
simplifying of the code was required to send this message via this portal.
I’m not sure what I am missing, but it is not working for me.
Do I need to somehow add the plugin to the page where I want the dropdown data to populate?
Do I need to do something to the (recipients) dropdown field in the Field Group of the Manage Database Fields?
Are you able to identify where my error is?
Thank you so much for a great plugin.
And also, do you know if it is possible to utilise Option Groups when populating dropdown list data from the Participants Database?
Yes, options groups are added by adding an element to the array of options using this format: $title => ‘optgroup’ All options in the array after that will be in that optgroup until there is another optgroup element.
If you are getting an array of values from the database, you will need to figure out how to make sure the array is in the correct order and then insert the optgroup elements in the right places in the array. Getting that done may be tricky.
Hi Philip,
Well, I can’t debug your code for you, but everything I see in your post makes sense, so the problem is likely not where you’re looking for it. Here are some things to check, do these in the order given, don’t skip steps.
First, get your php error log set up and working. For any kind of custom coding, this is a must. Use the error log to check on how your code is working.
1. Is you code running in the context where you need it? Adding an error_log statement to the code can tell you if it is running when you open the page. A properly enabled WP plugin will load on every page, no need to think about which page it’s running on.
2. is your code acting on the correct field?
3. is the database query working? You can use error_log statements to check that you’re getting the expected data. If there is an error in your query, that will show up in the error_log. Make sure you’re not introducing errors into the query.
By this point, it should be working.
The problem was that the plugin continues to deactivate. Initially activated it and thought the issue as fixed. Couple of days later, the dropdown was no longer populating so I checked the plugin and once again it was deactivated. It is just something I will need to keep a check on but ultimately the problem was an easy fix.
New related question.
Is it possible to have multiple dropdown lists in one sign up form?
I have the plugin populating with one dropdown list but not a second one. I copied the “add action” code and pasted it below it. I changed the variables names and I can’t seem to get it working. I just want to know if it is possible to do.
Thanks Roland.
I have worked out how to get multiple dropdown lists populating on a single signup form. One single letter can disrupt the whole code… :)
glad you got it figured out, was about to tell you you were on the right track
Roland – Thanks for the plugin! Is it possible to use value titles while auto populating a dropdown?
Yes, it is possible. Take a look at the code where the options array is getting built (line 58 in the article example code), you’ll see the index is blank. If you want to use a title for the value, you would put that into the index instead of leaving it blank:
$options[ $value_title ] = $value;For values that have value titles defined in the field, you can access those using the $field->value_title( $value ) method.
For example:
$options[ $field->value_title( $value ) ] = $value;This is safe to use even if $value doesn’t have a corresponding title in the field definition, it will use $value as the title.
Thanks for this plugin!
I’m trying to implement this for a multi-checkbox, but can’t seem to get the saved values to show on the frontend (or even the admin form) when the form is reloaded.
For example, let’s say I open a record which has multi-checkbox field with Option 1, Option 2, and Option 3 (pulled from the database). If I save the record with Option 1 and Option 2 checked, the next time I load the record, it shows all the options as unchecked.
I know it’s being saved in the database because it shows up in exports. I think this is because the plugin replaces the options whenever the form is loaded, without accounting for the saved values. Any thoughts to check against the saved values to display the checked options?
Thanks,
SJ
This tutorial assumes that the options you are getting from the DB are going to match the selected options. If the values coming in from the record don’t match any of the checkbox values, then they won’t show up as checked. This can be an issue if the options you are getting are not from the same field as the checkboxes.
Understood. Let me see if I can add some context with what I’m trying to accomplish:
1. Associate participants together via a field called “association_id”
2. Provide participants with a link to their record edit form. The idea is to allow them to “RSVP” to an event.
3. One of the fields (the field in question, let’s call this “associated_guests”) is a multi-checkbox that pulls all participants with the same association_id as the current participant
4. The current participant would check the box next to all the participants in associated_guests that will be attending the event.
5. The participant can refer back to their record edit form link to update their RSVP. (This is where I am stuck with displaying the saved associated_guests options back to the participant)
In addition, after the record update is submitted, I want to update a field called “attending” in the record of all participants that have their box checked. (Haven’t figured out a way to do this yet..)
As you can see, each participant will have different options in associated_guests because it will be different for everyone. I guess I could add the names of all participants in the database to the field as options, but then this would have to be updated for every new participant.
Any ideas to get this to dynamically sync up so that when the record edit form is displayed back to the user, it shows the saved results?
I can identify the saved options vs the unsaved options by adding the following code:
$options = array_diff($options, $participant[‘guest’]);
(where $participant is the current participant’s record)
But when it comes to:
$field->options = $options;
I don’t know how to let the $field variable know which of the options are saved vs not.
I’m not sure why the saved selections are not showing up as checked when the record edit form is displayed. Perhaps we should continue this by email so you can send me your custom plugin code. email me at support@xnau.com and I’ll take a look.
Roland, have you been able to make this technique work with Chosen Multi Dropdown?
Did you see this article?
Pre-load a “Chosen” Dropdown with Options from the Database
Good afternoon.
Please I need create into plugin PARTICIPANTS DATA BASE, a form Dropdown but I do not find where to write the values to choose in list.
The values are the cities names.
Thanks you
Take a look at this page under “Values”:
Defining Participants Database Fields
Hi Roland,
Thanks for the thorough tutorial and plugin! I have gotten my form’s dropdown to populate correctly using the plugin. However, I am interested in using the dropdown/other option, as the function of the dropdown will be to see what previous users have submitted — if their option isn’t already in the dropdown, they can add it using that “other” option. Do you know if it’s possible to implement based on a dropdown/other field?
Thanks in advance for your help.
Should work the same way on a dropdown/other field. Try it and let me know if it doesn’t work.
Thanks for your fast reply. I tried before commenting and it didn’t seem to be working (would return an empty dropdown list on the form).
Make sure the action is also changed after you change the form element in Participants Database:
The {$type} part of the action identifier has to match the name of the form element you’re using.
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! :)
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.
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.
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.
Another thought: look at the PHP error log (if you have it set up) it sometimes provides helpful info.
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.
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.
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
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?
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.