

Get in touch

Download Plugin Now
  • Follow
  • Follow
WP Data Access
  • Download
  • Support
  • Features
  • Pricing
  • Documentation
    • Tool Guide
    • App Builder
    • Data Explorer
    • SQL Query Builder
    • Plugin Settings
    • Legacy Tools
    • Remote Connections
a
M
M
  • Download
  • Support
  • Features
  • Pricing
  • Documentation
    • Tool Guide
    • App Builder
    • Data Explorer
    • SQL Query Builder
    • Plugin Settings
    • Legacy Tools
    • Remote Connections
Download Plugin Now

Legacy Tools

  • Deprecating in the near future

Tables

  • Getting Started
  • Creating and publishing
  • Responsive data tables
  • SEO friendly data tables
  • Adding hyperlinks
  • Extension Manager
  • Style Manager
  • Language settings
  • Styling
    • Style Manager
    • Premium styling
    • Global styling
    • Code Manager Styling
    • Manual styling
    • Styling issues
  • Filters
    • Static filters
    • Interactive filters
      • URL parameters
      • Search Panes
      • Search Builder
      • Search form
      • Advanced search
      • Geolocation search
  • Export & other buttons
    • Export to PDF, CSV, Excel, SQL
    • Print & copy buttons
    • Column selection buttons
    • Custom buttons
  • Advanced features
    • Advanced settings
    • Large tables & performance
    • Progressively SHOW MORE
    • Custom queries
    • Custom Post Types
    • Row grouping
    • Responsive master-detail
    • Master-detail tables
    • Non admin user access
  • Demos & tutorials
    • Demos
    • Tutorials

Forms

  • Data Projects
  • Projects and templates
  • Supported Data Types
  • One-To-Many relationships
  • Many-To-Many relationships
  • Registration page
  • Manage table options
  • Shortcode usage
  • Data Forms
    • Data Forms
    • Overview
    • Project Demo
    • Page Demo
  • Advanced features
    • Adding filters
    • Custom validation
    • Add less/more button
    • User roles
    • Managing roles and users
    • Advanced table options
    • Advanced lookups
    • Front-end toolbar
    • Transfer to other WP site
  • Demos & tutorials
    • Demos
    • Tutorials

Templates

  • Project Templates
  • Creating templates
  • Table Settings
  • Relationships
  • List Table
  • Data Entry
  • Reconcile

Designer

  • Getting started
  • Introduction
  • Basic mode

Dashboards

  • Getting started
  • Dashboards
  • Sharing dashboards
  • Dashboards Widgets
    • Dashboard Widgets
    • Publication Widget
    • Project Widget
    • Chart Widget
    • Custom Code Widget
    • Database Widget
    • Sharing Widgets

Charts

  • Chart Widget

Code

  • Shortcodes
    • Overview
    • wpdataaccess
    • wpdadataproject
    • wpdadataforms
    • wpdadiehard
    • wpdageomap
    • wpdawidget
  • Plugin Variables
    • Environment variables
  • Hooks & filters
    • Overview
    • Hooks
      • Overview
      • wpda_add_search_actions
      • wpda_add_search_filter
      • wpda_before_list_table
      • wpda_after_list_table
      • wpda_wpdataaccess_prepare
    • Filters
      • Overview
      • wpda_column_default
      • wpda_before_simple_form
      • wpda_after_simple_form
      • wpda_construct_where_clause
  • API
    • WP Data Access API
    • Remote database access
    • CRUD Example
    • Extensions
      • Alternative search algorithm
      • Alternative buttons extension
  • Code Manager
    • Overview
    • Write code
    • Share(d) code
View Categories

Static filters

There are two ways to add a static filter to a data table:

  1. Default where clause
  2. Shortcode parameters

1. Default where clause #

The default where clause supports conditions, subqueries and other more complex filters as long as it contains valid SQL.

Using plugin variables in your default where clause #

The WordPress user ID and some pre-defined plugin environment variables are available in the default where clause. The following example demonstrates how the WordPress user ID can be used to limit query output. Read more…

WordPress user ID in default where

Using URL parameters in your default where clause #

Premium users can also use URL parameters in their default where clauses. The following notations allow user to access these values:

httpGet[‘param_name’] Return the value of parameter param_name for an HTTP GET request
httpPost[‘param_name’] Return the value of parameter param_name for an HTTP POST request
httpRequest[‘param_name’] Return the value of parameter param_name for an HTTP GET and POST requests

Notes #

  • Null is returned when no argument with the given name is found
  • Values are sanitized and prepared automatically

Examples #

Show only rows for a specific student

student_id = httpPost['student_id'] and httpPost['student_id'] is not null

Add a like condition using if

first_name like if( httpGet['student_firstname'] is null, first_name, concat( '%', httpGet['student_firstname'], '%' ) )

Show only rows for a specific student when parameter student_id is present or all rows if parameter student_id is not present

student_id = ifnull( httpPost['student_id'], student_id )

2. Shortcode parameters #

Shortcode wpdataaccess provides two parameters to filter data:
  • filter_field_name (field name(s) to be filtered)
  • filter_field_value (field value(s) to filter, performs a LIKE and therefor allows wildcards)

Example of a filter for one specific column

[wpdataaccess pub_id="18" filter_field_name="lastname" filter_field_value="jose%"]

For filters containing multiple column names and values, use a comma seperated value and make sure the array size of filter_field_name and filter_field_value are the same.

Example of a multiple column filter

[wpdataaccess pub_id="6" filter_field_name="job,ename" filter_field_value="president,ford"]

The filter allows to use % as a wildcard as shown in the single column filter example. These filter parameters do not support operators like IN, OR, NOT, >, < and so on. Please use the default where clause to use these operators and more complex filters.

Share This Article :
  • Facebook
  • X
  • LinkedIn
  • Pinterest
Still stuck? How can we help?

How can we help?

Updated on 2025-02-09

32 Comments

  1. Michal Vaško
    Michal Vaško on 2020-08-16 at 3:49 pm

    Hi, is it possible to set the order by attribute in the admin page with a column that is not selected in column names? For example sort by date_time column but I don’t choose this column in column names because I don’t want to how this column in the result.

    Reply
    • Peter Schulz
      Peter Schulz on 2020-08-17 at 7:55 am

      Yes Michal, you can do that, although it might be a bit confusing fo a user to get a sorted list without seeing the sorting column. Please keep in mind that a user can change the order by clicking on the header of another column.

      Reply
  2. Clas Karlsson
    Clas Karlsson on 2020-11-01 at 3:35 pm

    Hi, I am still evaluating your plugin and I might already be pushing it to the limit. But is there a way to filter the content with the help of user meta? I tried WHERE Clause: FIELD_NAME = ‘get_the_author_meta( ‘FIELD_NAME’, $user->ID )’ with zero result.

    Reply
    • Peter Schulz
      Peter Schulz on 2020-11-02 at 8:41 am

      Hi Clas, You can use FIELD_NAME=$$USERID$$ to compare with the user id or FIELD_NAME=$$USER$$ to compare with the user name. You cannot use PHP functions in a where. Hope this helps, Peter

      Reply
  3. Charles Godwin
    Charles Godwin on 2021-02-10 at 5:54 pm

    I tested this
    https://ottawa.ogs.on.ca/researching/indexes/library-catalog/?wpda_search_column_Title=Wesleyan

    and get nothing found but if I go to
    https://ottawa.ogs.on.ca/researching/indexes/library-catalog
    and put Wesleyan in the column filter I get expected results.

    what am I doing wrong?

    Reply
    • Peter Schulz
      Peter Schulz on 2021-02-22 at 9:30 am

      Hi Charles,

      Please change: ?wpda_search_column_Title to ?wpda_search_column_title. It works than.

      Good luck,
      Peter

      Reply
  4. Joseph
    Joseph on 2021-02-11 at 7:03 pm

    Hi Peter,
    I probably once again need your help and expertise to figure this one out, I have option to search for the production table based on Cargo Control Number (unique field) or by PO (reference number) which may vary, the whole PO number might have additional characters to the left or right of it, but we need to see all occurrences that contain the searchable PO term.

    We use the Gravity Form on https://www.rutherfordglobal.com/pars-search/ where the user has to choose the search by, then Gravity form notification is set to wpda_search_column_ccn={CCN:2}&wpda_search_column_po={PO:4} where CCN:2 is the second field ID of the term and PO:4 is the form field ID 4 and the final results are displayed on https://www.rutherfordglobal.com/pars-search-results/

    The search results page has the shortcode [wpdataaccess pub_id="2"]

    All works well as long as the user enters a CCN number or a full exact PO number, but as soon as the PO number is any shorter form it will not find the record(s)

    As I understand I need a WHERE clause similar to what I have on the old website where I use php diect select statement such as:
    ==============================================================
    // Set SQL string based on selected search option
    if ($search == “CCN”)
    $sql = “SELECT FileNo,rdate,rtime,port,ccn,po,sub,status from Production where ccn = ‘”.$parsNo.”‘”;
    else if ($search == “REF”)
    $sql = “SELECT FileNo,rdate,rtime,port,ccn,po,sub,status from Production where po like ‘%”.$parsNo.”%'”;
    ===============================================================
    I tried to put some filter in the Where clause under Data Tables using po like ‘%wpda_search_column_po%’, tried many variations of this, even tried to put in the actual PO number I’m searching po like ‘%2588594%’ and never found, but there are many records in the PO table having in them this number

    Sorry for the long description, I just wanted to explain in detail what I’m looking to achieve:

    1). Allow the search for PO only to be any part of the actual PO number stored in the database and display all results
    2). If that is possible how do I make sure that it will not affect the search for CCN number which is always exact

    Hope you can help on this as you did many times in the past.

    Reply
    • Peter Schulz
      Peter Schulz on 2021-02-22 at 9:42 am

      Hi Joseph,

      The plugin allows you to use wildcard with URL arguments. You just need to add them yourself. This example:
      wpda_search_column_po=%%
      adds a wildcard in front and after your search value.

      Not sure if this works with Gravity Forms, but it could look like this:
      wpda_search_column_po=%{PO:4}%

      Can your please test if this works?

      Thanks,
      Peter

      Reply
      • Joseph
        Joseph on 2021-02-22 at 12:45 pm

        Hi Peter,

        Excellent idea and thank you very much for your reply.

        I applied the change and tested in current website using WP-Access and in old website using direct PHP search, I tested for PO # 858859, on the PHP side shows 10 rows:
        https://gyazo.com/25f45df5ff1f0baa430a7c4ba23901af
        Using WP-Access and applying wpda_search_column_ccn={CCN:2}&wpda_search_column_po=%{PO:4}% as confirmation for Gravity Search Form gives me all the records in the database:
        https://gyazo.com/5e556cc618610e8ef3c8ef799fa98d4d

        But I also tested for search on CCN # and that was correct, so the change you suggested works well for CCN, but gives me all the database as a result for PO#

        Thanks,

        Joseph

        Reply
      • Joseph
        Joseph on 2021-02-22 at 1:07 pm

        Hi Peter,

        Excellent idea and thank you very much for your reply.

        I applied the change and tested in current website using WP-Access and in old website using direct PHP search, I tested for PO # 858859, on the PHP side shows 10 rows:

        https://gyazo.com/25f45df5ff1f0baa430a7c4ba23901af

        Using WP-Access and applying wpda_search_column_ccn={CCN:2}&wpda_search_column_po=%{PO:4}% as confirmation for Gravity Search Form gives me all the records in the database:

        https://gyazo.com/5e556cc618610e8ef3c8ef799fa98d4d

        But I also tested for search on CCN # and that was correct, so the change you suggested works well for CCN, but gives me all the database as a result for PO#

        Thanks,

        Joseph

        Reply
        • Peter Schulz
          Peter Schulz on 2021-02-23 at 4:42 pm

          Hi Joseph,

          Great to read it works for CCN #. What happens if you remove the wildcards for PO #?

          Thanks,
          Peter

          Reply
          • Joseph
            Joseph on 2021-02-24 at 12:28 pm

            Hi Peter,

            If I remove the wildcards from PO it continues to work for CCN, but PO needs to be exact match to show, what I need is a way to allow still for CCN search and in the same time make the PO search show all available results that contain the PO entered

            Hope that’s possible, if not I thought to create 2 separate searches, one for the exact CCN wpda_search_column_ccn={CCN:2} and a second one for wildcard PO wpda_search_column_po=%{PO:4}% each displaying its own results, but I don’t know if I can create 2 separate searches in Data Tables

            Thanks,

            Joseph

            Reply
          • Peter Schulz
            Peter Schulz on 2021-02-27 at 8:20 am

            Hi Joseph,

            I see there already is some javascript in your form submit. Can you change it, so that:
            – Only the CCN # is send WITH wildcards when CCN # is entered
            – Only the exact PO # is send when PO # is entered

            Would this solve the problem?

            Thanks,
            Peter

            Reply
  5. Joseph
    Joseph on 2021-02-12 at 3:22 pm

    Hi Peter,
    How would I define a WHERE clause when I have 2 options for searching: option by CCN# (which is always the same length) and option by REF# where I need to display all records that have the REF# entered as part of the record?

    I tried adding in the WHERE clause: po like ‘%wpda_search_column_po%’ which did not work and which also prevented getting the results for any search by ccn number which are working without needed a where clause

    Thanks,

    Joseph

    Reply
  6. Joseph
    Joseph on 2021-02-17 at 12:19 pm

    Hi Peter,

    Any chance you can help on the where clause please

    Thanks,

    Joseph

    Reply
  7. Joseph
    Joseph on 2021-03-24 at 1:08 pm

    Hi Peter,

    sorry was away for awhile, my search results page uses:
    wpda_search_column_ccn={CCN:2}&wpda_search_column_po={PO:4}

    If I use the %{CCN:2}% and no % on the PO I get no results for any
    If I use {CCN:2} and %{PO:4}% i get CCN result and all the database for PO results
    If I use {CCN:2} and %{PO:4} I get CCN result and all the database as result for PO
    Same would happen if I use {PO:4}% I get CCN result and no result for PO

    I’m not using any JS in the form, it’s a Gravity Form that allows only entering the search term for either CCN or PO, then it takes the entered term and adds it to the wpda shortcode

    For testing, if I use 3959752984492 for CCN# I get correct results, if I use 858859 for REF# (PO) I get no results, but there are 12 records that have 858859 in their full PO number

    Thanks,

    Joseph

    Reply
    • Peter Schulz
      Peter Schulz on 2021-03-24 at 1:47 pm

      Hi Joseph,

      Are you sure about your numbers? If I search for PO=106306 it works!

      I noticed that a search on column PO is much slower than a search on column CCN. Is it possible to create an idex on column PO?

      Thanks,
      Peter

      Reply
      • Joseph
        Joseph on 2021-03-24 at 2:40 pm

        Hi Peter,

        Tried the PO number 106306 it does display 2 records, but looking in the actual database I can see 61 records that have the number 106306 included, it’s true that there are these 2 records that have identical PO, but all the other ones have the same number in their PO column, like SEGU9106306 or GM605321937010630666 and they want to see all the occurrences of the PO column that has the number entered anywhere in the record (noticed that the entered number is never at the front of the string)

        I noticed too that the search for PO is much slower than for CCN, how would I go and create an index to speed up the search for PO?

        Thanks,

        Joseph

        Reply
        • Peter Schulz
          Peter Schulz on 2021-03-24 at 5:13 pm

          Hi Joseph,

          So you want to do a wildcard search? In that case you need to add % characters to both sides of your argument like: PO=%106306%

          But that might return too many rows. Can you check that? It also makes no sense to add an index on that column if you use a wildcard. Is there maybe a way to narrow a search on PO?

          Thanks,
          Peter

          Reply
          • Joseph
            Joseph on 2021-03-25 at 1:35 pm

            Hi Peter,
            Here is my search:
            wpda_search_column_ccn={CCN:2}&wpda_search_column_po=%{PO:4}%
            doing a live search at https://www.rutherfordglobal.com/pars-search/ for CCN 17K8PARS016310 display the correct amount of records from the database, but if I do a PO search for 858859 it gives me the whole database while there are only 12 records%858859% with this pattern
            Using the same search with %PO% for PO # 106306 doesn’t show any records while in the database there 78 records with this pattern
            I cleared the cache and used 2 separate browsers, same result

            Thanks,

            Joseph

            Reply
          • Peter Schulz
            Peter Schulz on 2021-03-27 at 7:39 am

            Hi Joseph,

            Can you send me your table structure so I can have a closer look at this issue? Please follow these steps:
            > Data Explorer > Select table > Manage > SQL > Copy to clipboard

            You can use the contactform to send it in private.

            Thanks,
            Peter

            Reply
  8. Joseph
    Joseph on 2021-03-29 at 1:16 pm

    Hi Peter,

    Sent Explorer/Table/SQL clipboard data through the contact form @ https://wpdataaccess.com/contact/

    Thanks,

    Joseph

    Reply
  9. Mike Creighton
    Mike Creighton on 2021-03-31 at 10:54 pm

    Is it possible, as part of a publication, to create a filter for users to apply – in particular for a date range?
    For example in my weather records table, a user might want to see the data from 1 January-31 January 2005.

    And while we’re at it, is it possible to perform calculations on the filtered result – average temperature for the days selected and so on?

    Thanks as always.

    Reply
    • Peter Schulz
      Peter Schulz on 2021-04-02 at 7:38 am

      Hi Mike,

      A date range filter is not available at this time. Sorry!

      I did a little research. I’ll add a custom search form to the next release, which allows you to make data range selections. But it only works client side! WP Data Access works server side by default. With server side processing the plugin loads the data shown on the actual page only, which usually makes a publication faster. Especially if you have a lot of data. Client side processing can have advantages to of course. If users do a lot of searching, ordering, etc on your publication data, loading all the data on page load might speed up local (client side) actions.

      You can change your publication to work client side, forcing the plugin to load all the data at once and see if that works for you. Although you have over 4400 rows in your publication, the row content is not taking up a lot of space. Might be worth to give it a try. To force WP Data Access to use client side processing, just add this to column table options advanced of your publication:
      { "serverSide": true }

      >>> And while we’re at it, is it possible to perform calculations on the filtered result – average temperature for the days selected and so on?

      I will add support for row grouping which allows you to do that to the next release. That was on my to do list anyway… 🙂

      Best regards,
      Peter

      Reply
  10. Danie Goosen
    Danie Goosen on 2021-04-03 at 7:14 am

    How do i create a query in WP Data access for only top 3

    Reply
    • Peter Schulz
      Peter Schulz on 2021-04-03 at 9:49 am

      Hi Danie,

      You can use the pageLength option in table options advanced. An example can be found here:
      https://wpdataaccess.com/docs/data-tables-advanced-features/advanced-settings/

      Hope this helps,
      Peter

      Reply
  11. CDE Chiboi
    CDE Chiboi on 2021-08-26 at 5:46 pm

    Hi Peter:

    I’m trying to create an url filter to get some kind of “master-detail” report. I think I have achieved how to do the hyperlink but the “child” table seems to don’t care about arguments.

    This is the example: https://cnchiboi.com/vd-contacto/

    What am I doing wrong?

    Thank you.

    Reply
    • Peter Schulz
      Peter Schulz on 2021-08-27 at 5:31 am

      Your master detail report looks great! 🙂 You just need to change your URL argument
      from: ?idPeticion=4
      to: ?wpda_search_column_idPeticion=4

      The plugin needs the wpda_search_column_ prefix to start searching.

      Good work,
      Peter

      Reply
  12. CDE Chiboi
    CDE Chiboi on 2021-08-28 at 11:35 am

    Hi Peter:

    Yes! It works even hiding id colum to final user. That’s exactly the idea: The user doesn’t need to know how it works.

    Thank you for the final help!!

    Regads

    Reply
  13. CDE Chiboi
    CDE Chiboi on 2021-08-28 at 3:10 pm

    I’ve just given five stars to your amazing plugin. The most powerfull I have reached for WordPress all over the world.

    Regards from Spain

    Reply
  14. Tim Thenman
    Tim Thenman on 2024-07-12 at 11:46 am

    Hi, is it possible to only show data in the table for those rows that match an input parameter defined in an advanced custom field for the specific post?

    i.e.:
    – i have post with the advanced custom field “symbol” which holds the ticker symbol “AAPL” for a specific post.
    – i have my database table with the latest share prices for all symbols in the database, also “AAPL”
    – i then want to publish that database table in my post and use the advanced custom field as the input parameter, so that it only shows the share price for “AAPL”.

    This would allow me to create a template custom post type with the table embedded automatically. Then, depending on the symbol defined per each post, the table would return the right row with the share price.

    Reply
    • Kim Leong
      Kim Leong on 2024-07-15 at 5:42 am

      Hi Tim,

      If you can access the value of your custom field via GET or POST, you can try the URL parameters in your Default WHERE clause. Kindly refer to the example notations above or here.

      Reply

Submit a Comment Cancel reply

Your email address will not be published. Required fields are marked *

Table of Contents
  • 1. Default where clause
    • Using plugin variables in your default where clause
    • Using URL parameters in your default where clause
      • Notes
      • Examples
  • 2. Shortcode parameters
WP Data Access
  • Follow
  • Follow
Quick Links
$

Blogs

$

Tutorials

$

Demos

Get in touch
$

Premium support

$

Free support forum

$

Contact us

Resources


WordPress plugin directory



YouTube tutorials

Copyright © 2025 | All Right Reserves

We use cookies to ensure that we give you the best experience on our website. If you continue to use this site we will assume that you are happy with it.Ok