[pdb_list] shortcode has a “filter” attribute that you can use to determine which records get shown in the list that shortcode displays. It’s a little complicated, but once you get how it works, you’ll find it is extremely flexible and can tailor your list to show exactly what you want. List shortcode filters are also used by the
[pdb_total] shortcode so you can tell it what records to count.
The general format of the parameter is this: field operator value. So, for instance, if you wanted to show all records from the state of New York, you would put
[pdb_list filter="state=NY"] . “state” is the field (it must match exactly the field name as listed on the Manage Database Fields page in the plugin) “=” is the operator and “NY” is the value. The filter is grabbing all records where the state is equal to “NY.”
The filters work by comparing two values: the value in the filter is compared against the value in the record to determine if that record should be shown or not. How the comparison is made is determined by the operator. Here are the operators and what they do:
|Symbol||Operator||What it does||Example|
|=||equals||filter value matches the record value exactly||state=NY|
|~||contains||finds the filter value in the record value||interests~yoga|
|!||not in||filter value is not found in the record value||interests!hiking|
|!=||not equals||filter value does not equal the record value||state!=CA|
|>||greater than||record value is greater than the filter value||amount_due>10|
|>=||greater than or equal to||record value is greater than or equals the filter value||balance>=100.00|
|<||less than||record value is less than the filter value||last_name<b|
|<=||less than or equal to||record value is less than or equals the filter value||last_name<=z|
You can use more than one filter by stringing them together like this:
[pdb_list filter="last_name>c&last_name<h"]< code=""> (note ampersand <code class="">& between filter statements) This gives you all records with last names that start with d through g. Upper case or lower case doesn’t matter. The ampersand “ands” the two filters together, for instance if you want all the unemployed applicants from New York, you’d use
Filters can be also be chained by an “or” operator by using the
| (pipe) character. For instance, to get all the records with a city of either “New York” or “Brooklyn” you could use:
filter="city=New York|city=Brooklyn" And they can be combined:
filter="color=red|color=orange&size=large" will give all large items in red or orange. Note that items separated with an “or” operator will be parenthesized (put in parentheses), meaning that the above example would not return a list of all the red items plus all large orange items. This is because the parenthesized part is resolved first, so “red or orange” is resolved as a unit before the rest of the filter is included.
Filters accept search wildcards so you can set up a shortcode to show, for instance, all the entries beginning with a certain letter. This could be useful for breaking a list up into alphabetical groups. The wildcards you can use are
*, which means “any string of characters” or
? which means “any single character.” To filter for all last names that begin with “J” you would use a filter like this:
Filtering for Blank Data
You can use the filter to exclude records that are missing a field or have a blank value by using the not operator and no value:
[pdb_list filter="phone!"]That will show all the records that have a phone number (in other words: the phone field is not empty). You can of course chain this with other filters, just remember all you’re doing is omitting the “value” part of the filter statement:
[pdb_list filter="photo!&state=OR"] gives you all the people in Oregon that have uploaded a photo.
You’ll notice that the filter statement uses certain characters in it’s syntax. But what if the data you’re filtering for uses these characters? In most cases, a double-backslash will do the trick to tell the code that the character should be treated as a “literal.” A literal is a character that should just be treated as a plain character with no special meaning.
Here is a list of characters and how they can be used as literals in the filter statement:
|Character||How to Use||Example|
|&||double backslash||filter=”type=brick \\& mortar”|
|“||enclose in single quotes||filter=’first_name=Raymond “Ray”‘|
|‘||enclose in double quotes||filter=”last_name=O’Malley”|
When it says “enclose in single quotes” that means you need to enclose the whole filter in quotes, not just that one part. Normally you would, it’s just that you can use either double or single quotes for that. You’ll note that it’s not possible to have a chained filter that uses both single- and double-quotes as literals.
What if none of this works? The best way to get around that situation would be to use the ~ operator and filter for the parts you can use in the filter, leaving out the characters that don’t seem to work.
To correctly compare dates, the field must be defined as a date field form element on the manage database fields page. Date values should be a regular date string like this:
[pdb_list filter="date>jan 3,2012"]Filtering for a range of dates requires two filters:
[pdb_list filter="date>=jan 1,2012&date gives you all the records for the year 2012.
It’s best to use a name for the month (and maybe you have to use English month names) because the date/month/year order is different on different servers. If you really want to use numbers for the date, just try it to see what the correct order for your web server is. The “Strict Date Format” setting affects how the shortcode filters are interpreted, so if this is checked, the date format in your filter argument must match the date format set in your WP General Settings.
Dynamic Date Values
It is possible to use a dynamic date value in date filters. These “date keys” are phrases that are converted to date values when the shortcode is displayed. There are 5 available:
- current_date – the current date and time
- current_day – the current day, beginning at 00:00UTC
- current_week – the first day of the current week
- current_month – the first day of the current month
- current_year – the first day of the current year
- +n_days, -n_days – number n of days before or after current date
- +n_months, -n_months = number of months n before or after current date
You can only use these in your filter statements if the field you are comparing to is a date field. For example, to show the signups for the current month, you can use a filter like this:
If you’re wondering why we need to use the “>=” operator, each of the “date keys” only gives the first day (or time) of the time period, so to include that date and all the dates after that, we need to use the “>=” operator.
For the date keys that have an “n” you just substitute the “n” with your number. For example:
[pdb_list filter="date_recorded>=-6_months"] will give you all the records that were created in the last 6 months.
[pdb_list filter="event_date<=+10_days&event_date>=current_date"] will give you all the records with an “event_date” within the next 10 days.
Filtering Numeric Values
If you need to compare or filter values based on their numeric value, you must use a “numeric” type field. This means you cannot include units, commas, or denomination marks in the values. Using numeric fields, you can filter by numeric range:
Filtering Link fields
When using filters with fields that store their values as arrays (the Link field is the only standard field that does this), you must use the
~ operator because the values are stored with other information (technically, the array is serialized). A
= will never work because of that additional information.