Index

  1. Home
  2. Docs
  3. Index
  4. Data Publisher
  5. Adding filters

Adding filters

There are three ways to add a filter to a publication:

  1. Default where clause
  2. Shortcode parameters
  3. URL parameters

Default where clause

The Data Publisher allows plugin users to add any default where clause to a publication (click image below to enlarge). This includes subqueries and other more complex filters. The default where clause also supports the $$USER$$ and $$USERID$$ environment variables to filter user specific data (user must be logged in).

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:

[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.

URL parameters

To add a filter on a specific table column you can add an URL argument to your request that follows the following name convention:

wpda_search_column_<column_name>

This works for POST and GET request!

Example

<input name="wpda_search_column_first_name" type="text" value="Sacha" />

Adds condition

first_name = 'Sacha'
Was this article helpful to you? Yes No

15 Replies to “Adding filters”

  1. 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.

    1. 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.

  2. 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.

    1. 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

    1. Hi Charles,

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

      Good luck,
      Peter

  3. 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 Publisher 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.

    1. 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

      1. 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

      2. 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

        1. Hi Joseph,

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

          Thanks,
          Peter

          1. 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 Publisher

            Thanks,

            Joseph

          2. 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

  4. 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

Leave a Reply

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

18 − eight =