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:
| <?php | |
| /** | |
| * Plugin Name: PDB Volunteer Dropdown | |
| * Description: load the registered volunteers into a Participants Database form dropdown | |
| */ | |
| /* | |
| * sets our function to be called when the pdb-form_element_build_dropdown action | |
| * is triggered by the form | |
| * | |
| * if your field is not a dropdown, change the action so it is triggered on the correct | |
| * element, for example if the field is a multiselect checkbox, the action would be | |
| * 'pdb-form_element_build_multi-checkbox' | |
| */ | |
| add_action( 'pdb-form_element_build_dropdown', 'xnau_set_volunteer_dropdown_options'); | |
| /** | |
| * sets the options for the volunteer dropdown | |
| * | |
| * @param PDb_FormElement object $field the current field | |
| */ | |
| function xnau_set_volunteer_dropdown_options ( $field ) | |
| { | |
| if ( $field->name === 'volunteers' ) : // check for our dropdown field | |
| global $wpdb; // grab the db helper object | |
| /* | |
| * For multiselect fields, values that don't match the defined values for the | |
| * field are dumped into the "other" element of the value array. In this case, | |
| * we take the "other" value and make it into an array and add it to the main | |
| * array of values so that they will show as selected in the form element. | |
| */ | |
| if ( $field->is_multi( $field->form_element ) && isset( $field->value['other'] ) ) { | |
| $field->value = array_merge( (array) $field->value, explode(',', $field->value['other'] ) ); | |
| } | |
| /* | |
| * define the query for getting the list of volunteer names | |
| * | |
| * note that the $wpdb->prefix method is used to get the table | |
| * prefix; this is so it will work on all WP installs | |
| */ | |
| $query = ' | |
| SELECT first_name,last_name | |
| FROM `' . $wpdb->prefix . 'participants_database` | |
| WHERE form_type LIKE "%volunteer%" AND form_type NOT LIKE "%coordinator%" | |
| '; | |
| // 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 = array(); | |
| foreach ( $raw_names as $record ) { | |
| $options[] = $record->first_name . ' ' . $record->last_name; | |
| } | |
| // now set the field object with the new options list | |
| $field->options = $options; | |
| endif; | |
| } |
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.
Helen Payne 
Roland Barker
Hi Roland,
How would I use this to create a dropdown list of names from a PDB Log? The Log is called “clients” and the field title is “full_name”, thus a field name of “clients_name”. I tried the following but it returns an empty list (it works fine when the SELECT is a field in the primary record). Many thanks,
$query = 'SELECT clients_full_name
FROM ' . $wpdb->prefix . 'participants_database '
First, you must use the field’s name in db queries, the title is not used at all. Second, each log has its own db table, so to get values from log entries, the query must be on the log’s table. For example:
$query = 'SELECT full_name from ' . $wpdb->prefix. 'participants_database_clients'It is usually a good idea to take a look at the database itself when constructing queries (using phpMyAdmin or similar application), it lets you see the exact structure of the database.