WP Data Access
  • Download
  • Support
  • Features
  • Pricing
  • Documentation
Select Page
Popular Search importsqlformqueryexport

WP Data Access

  • Getting started
  • Installation
  • What’s new?
  • Patches
  • Known limitations
  • FAQ

Data 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

Data Apps

  • Data Projects
  • Projects and templates
  • Supported Data Types
  • One-To-Many relationships
  • Many-To-Many relationships
  • Registration page
  • Manage table options
  • Shortcode usage
Project Templates
  • Project Templates
  • Creating templates
  • Table Settings
  • Relationships
  • List Table
  • Data Entry
  • Reconcile
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

Data Explorer

  • Getting started
  • Navigation
  • Explore tables & views
  • Manage tables & views
  • Naming conventions
Manage Actions
  • Actions
Manage Settings
  • Settings
  • Search Settings
  • Geolocation Settings
  • Table Settings
  • Column Settings
  • Dynamic Hyperlinks
  • Dashboard Menus
Data Management
  • Import CSV & SQL files
  • Run SQL script files
  • Data Backup
Problems & solutions
  • InnoDB #rows estimation
  • Import file too large
  • Transfer to other WP DB

Remote Databases

  • MySQL | MariaDB
Other DBMS
  • Heterogeneous Connections
  • Using ODBC | JDBC
  • Connect to SQL Server
Premium Data Services
  • Remote wizard
  • START HERE
  • Errors and messages
  • Limitations
  • Tutorials
  • Remote Databases
    • Remote databases
    • SQL Server
    • PostgreSQL
    • Oracle
    • MariaDB | MySQL
    Remote Data Files
    • Remote data files
    • CSV files
    • JSON files
    • MS Access
    • XML files
    • Public URL
    • Synchronization
    Advanced features
    • Performance
    • Indexes

SQL

  • Getting Started
  • Tutorials
  • Visual Query Builder
  • Writing queries
  • Data exchange
  • Stored procedures
  • Database triggers
  • Scheduled queries

Business Intelligence

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

Table Design

  • Getting started
  • Introduction
  • Basic mode

Plugin Settings

  • Getting started
  • Plugin
  • Back-end
  • Front-end
  • Dashboard
  • Data Forms
  • Data Tables
  • Data Backup
  • Uninstall
  • Manage Repository
  • Manage Roles
  • System Info

Code

Shortcodes
  • Overview
  • wpdataaccess
  • wpdadataproject
  • wpdadataforms
  • wpdadiehard
  • wpdageomap
  • wpdawidget
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

User roles

Table of Contents
  • NOTES
  • Demonstration Video

A Data Project consists of one or more data administration and/or static pages. You can use the role attribute to give users access to Data Projects pages.

Use the $$USER$$ or $$USERID$$ environment variables to get access to the current user:
$$USERID$$ > wp_user.user_id
$$USER$$ > wp_user.user_login

In a student administration system for example, you might need a page to inform students about their personal results. Suppose student results are stored in table STUDENT_RESULTS which has a column STUDENT_NAME that holds the WordPress username of the student (or STUDENT_ID that holds the WordPress userid of the student).

To give students access to thier own data only, you can add the following WHERE clause to the student result page:

STUDENT_NAME = $$USER$$ (or STUDENT_ID = $$USERID$$)

Students will see their own results only. If you have users who need to see all results, you might need a second student result page available to their role.

NOTES #

  • You can set the default value of a table column to the WordPress username using the $$USER$$ environment variable:
    Data Projects > Manage Table Options > select your table > set “Default value” to $$USER$$
  • If you set the WHERE clause to “your_column_name=$$USER$$” and the default value for your_column_name to $$USER$$, users will be able to see and edit their personal data only.
  • If you use the $$USERID$$ environment variable, make sure your column is numeric.
  • The $$EMAIL$$ environment variable points to the WordPress user email address.

Demonstration Video #

IMPORTANT Please notice that tab Manage Table Options is no longer available. Table Options was renamed to Project Templates and can now be accessed directly from the plugin menu.

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

How can we help?

Updated on 2022-12-20
Add less/more buttonManaging roles and users

18 Comments

  1. Ben Watson
    Ben Watson on 2019-10-11 at 3:25 pm

    I’ve spent many hours trying to make this work with mostly frustration to show. The directions above were confusing. The term “WordPress username” appears ambiguous. If I use column user_login in wp_users, adding a record does produce the desired user_login value in the data entry form. However, if I include the WHERE clause in my project page, I get no records.

    Reply
  2. Peter Schulz
    Peter Schulz on 2019-10-12 at 4:10 pm

    Hi Ben,

    I added a short video that shows the steps needed to get your where clause to work with the $$USER$$ environment variable. I hope this helps. Let me know if you need more assistance.

    Best regards,
    Peter

    Reply
  3. Toby
    Toby on 2020-04-19 at 2:55 am

    Hi Peter

    I see there is a $$USER$$ variable – is there a $$POST_ID$$ variable? I would like to filter records in a view (that contains the post ID as a field) to show only those associated with the post being viewed.

    Thanks,
    Toby

    Reply
  4. Peter Schulz
    Peter Schulz on 2020-04-19 at 6:51 am

    Hi Toby,

    Sounds like a master detail relationship. What are your requirements? Do you have something like a screen sketch? Maybe you can use a master detail page to select a post from the master table and then show all related posts in the detail table?

    Best regards,
    Peter

    Reply
  5. Toby
    Toby on 2020-04-19 at 10:10 am

    Hi Peter – thanks for the fast response!

    I want to use WP Data Access to manage display of lists of articles. I will have 2 Custom Post Types (say A and B) – I am probably using the PODS Framework rather than ACF as the article data can be stored in it’s own table and the Taxonomy data is neater.

    One section of Post Type A is a list of Post Type B’s – probably title and some taxonomy data. These lists (a table) will be created in a custom user dashboard as part of a WP Data Access Data Project. So A to B will be a one-to-many and B to A will be a many-to-many relationship ie. Type A posts have only 1 list of Type B but type B posts may appear in multiple type A lists.

    I envisaged a table with a row for each Type A id and associated Type B Id. Such that a Type A Post with a list of 7 type B posts will have 7 rows in the table.

    A WP Data Access form will be used to allow authorised Type A Authors to select Type B Posts they want to list in their Type A post.

    I may not be on the right lines with this idea but currently this is how I see it working…

    Thanks,
    Toby

    Reply
  6. Peter Schulz
    Peter Schulz on 2020-04-20 at 5:44 am

    Hi Toby,

    Do you have a data model? Can you share it? Please use the contactform (just scroll down) if you prefer to send in private.

    Best regards,
    Peter

    Reply
  7. Toby
    Toby on 2020-04-20 at 4:14 pm

    Hi Peter

    I have just sent a link to Google Sheet which outlines the table data and links I have in mind.

    kind regards,
    Toby

    Reply
  8. Fédi
    Fédi on 2020-09-30 at 1:37 pm

    Hi Peter,

    It seems to be not possible to add %%USERID%% as a Default value for a column in the Manage columns for data entry form for table…

    Is it?

    Reply
    • Peter Schulz
      Peter Schulz on 2020-09-30 at 2:03 pm

      Hi Toby,

      Try the $$USERID$$ instead of %%ISERID%%. That should work! 🙂

      Best regards,
      Peter

      Reply
      • Fédi
        Fédi on 2020-10-06 at 4:05 pm

        Thanks!

        Reply
  9. Fédi
    Fédi on 2020-09-30 at 1:40 pm

    And by the way, please what is the “Less?” feature column?

    Reply
    • Peter Schulz
      Peter Schulz on 2020-09-30 at 2:09 pm

      Hi Toby,

      If you have a large number of columns in your master table, you can hide colums when the page is initialized. The plugin will add a “show more” / “show less” button to your parent table to show/hide selected columns. This works only for parent tables on parent/child pages.

      Does this help?
      Peter

      Reply
      • Fédi
        Fédi on 2020-10-06 at 4:06 pm

        Thanks a lot 😉

        Reply
  10. Fédi
    Fédi on 2020-10-06 at 4:03 pm

    Hi Peter,

    Thank you for the plugin!

    Please without passing thru a list table, like the Edit only option, how can you display a form to edit fields of an existing record, let’s say on wp_users table, using the $$USERID$$ in WHERE Clause or in the Default Value?

    Reply
    • Peter Schulz
      Peter Schulz on 2020-10-07 at 6:56 am

      Hi,

      I would not recommend to make your wp_users table editable this way! 🙂 But suppose you would have a table with a similar structure:
      – Add ID = $$USERID$$ to your default where
      – Add argument action=edit to your URL

      This will show the data entry form for the current user in edit mode (for view mode use: action=view). The user will see a back button to view the list. Other users will not be shown in the list because of the default where clause. If you want to hide or remove the back button checkout action hook wpda_after_list_table:
      https://wpdataaccess.com/docs/hooks/wpda_after_list_table/

      Good luck,
      Peter

      Reply
  11. Fédi
    Fédi on 2020-10-12 at 1:52 pm

    Hi Peter,
    Thank you for the quick response.
    I tried your advice and I got an error!

    Let’s say I have the front-end page /my-profile/ with the shortcode [wpdadiehard project_id="1" page_id="1"]
    The page_id=1 is set with Mode= edit, and actually showing a table with only the logged username and allows viewing and editing that user, thanks to the WHERE Clause= ID=$$USERID$$.
    Great! But I’m looking to show (only and) directly the edit form for that user (without passing thru the table).

    When I call this front page like that /my-profile/?action=edit, as I understood, I have the following error message:
    ERROR: Wrong arguments [missing key value]

    Did I miss something?

    Reply
  12. David
    David on 2022-05-30 at 6:39 am

    Hi,

    In the publications advanced tab I am trying to do something like this but it is not working:

    WHERE clause: lead_content LIKE %$$USER$$%

    I want to use $$USER$$ in a LIKE operator, e.g. LIKE ‘%mystring%’

    But it gives an error in the syntax like ‘mystring’%’

    Reply
    • Peter Schulz
      Peter Schulz on 2022-05-31 at 12:38 pm

      Hi David,

      Please use the concat function like this:
      lead_content LIKE concat($$USER$$, ‘%’)

      Good luck,
      Peter

      Reply

Submit a Comment Cancel reply

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

Table of Contents
  • NOTES
  • Demonstration Video

Links

Blogs
Tutorials
Demos

Socials

LinkedIn
Twitter

Get in touch

Premium support
Free support forum
Contact us

Resources

WordPress plugin directory
YouTube tutorials

Passionate Programmers B.V. © 2017 - 2023 All rights reserved