List shortcode filters let you show any selection of records in the list.
The [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.”
Operators
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 |
Problems with the Less-Than Character in Shortcodes
Note on using the “<” symbol in Shortcode Blocks: it is sometimes necessary to use the HTML entity instead of the literal character. In that case, the editor will show a message that the block has invalid content. What you need to do is start over with a new shortcode block, but use the HTML entity for the “<” character. For example:
[pdb_list filter="price<=100"]
Chaining Filters
You can use more than one filter by stringing them together like this:
[pdb_list filter="last_name>c&last_name<h"]
Note the ampersand & 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 employed=no&state=NY
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.
Using Wildcards
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: filter="last_name=j*"
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.
Special Characters
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” |
| =,~,! | double backslash | filter=”example=4+4\\=8″ |
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. This includes apostrophes, which are just single quotes as far as HTML is concerned.
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.
Filtering Dates
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<jan 1,2013"]
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 Keys
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 several 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: [pdb_list filter="date_recorded>=current_month"]
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.
When using dates in the list filter, remember that the current_date value will be exact to the second, which means that if you use a statement like “date_updated=current_date” you probably won’t get any matches, even though records were updated today. Using the “~” operator will make the comparison without the time, for example: [pdb_list filter="date_updated~current_date"] will give you all the records that were updated today.
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.
When using numeric fields, you can filter by numeric range: income>20000&income<=50000
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.
Filtering the List With a URL
It’s possible to set up a filter in the URL the user is using to view the list, giving you a way to create links to the list with a preset filter.