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

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
  • Table Settings
  • Column Settings
  • Search Settings
  • Geolocation 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

Introduction

Table of Contents
  • Table design
  • Table creation
  • Create a new table design
  • Create a table design from an existing database table
  • Local and remote databases
  • Local and remote databases
  • Modes
  • Reverse engineering
  • Reconcile

Table design #

A table design is a kind of blue print for a database table. It is not a physical table. When you create a table design in the Data Designer, no physical database table is created (yet). A table design might contain index definitions as well.

Table creation #

When your table design is ready, click the CREATE button to create the table and its indexes. If you modified a design of an existing (reverse engineered) table, click the ALTER button to alter the table.

Create a new table design #

There are two ways to create a new table design:

  1. Click on Design new table at the top of the Data Explorer main page to create a new table design.
  2. Navigate to the Data Designer menu and click on Design new table at the top of the Data Designer page to create a new table design.

Create a table design from an existing database table #

Call the Data Designer directly from the Data Explorer to alter specific tables. Find your table, click the Manage link and click ALTER on the Actions tab.

Local and remote databases #

The Data Designer can be used for local as well as remote databases, presuming you have the necessary privileges.

Local and remote databases #

The Data Designer can be used for local as well as remote databases, presuming you have the necessary privileges.

Modes #

Basic – Start here if you have absolute no experience in data modelling.
Advanced – Use this option to create tables if you have some basic understanding of database tables.
Developer – If you are an experienced developer, you can execute your own scripts from the Data Explorer or use the Query Builder.

Reverse engineering #

Use reverse engineering to create a table design from an existing table. Indexes are reverse engineered as well. The Data Designer allows you to make modifications to your table design and then either alter your existing table or create a new one.

Reconcile #

The reconcile feature performs a reverse engineering action on the selected table. The actual table design will be overwritten with the reverse engineered version. After a reconcile action, the table design matches the structure of the physical table.

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

How can we help?

Updated on 2022-12-20
Getting startedBasic mode

6 Comments

  1. Joseph
    Joseph on 2020-12-29 at 8:47 pm

    Hi Peter,
    Is there a way to display only part of the field that is retrieved from the remote database, I have a date field that also has a default time 00:00 at the end, I only want to display the date part (10 length yyyy-mm-dd), I tried to set the filed lenght and type in the Designer (please see screenshot: https://gyazo.com/5a8234bedb6c450e0c0a2827bf816dcb), but it has no effect on the search results page: https://www.rutherfordglobal.com/pars-search-results/?wpda_search_column_ccn=014-57776806&wpda_search_column_po= (screenshot here: https://gyazo.com/a9f0ce536e91b4d498196291cec877f4)
    Thanks,
    Joseph

    Reply
    • Peter Schulz
      Peter Schulz on 2020-12-30 at 8:12 am

      Hi Joseph,

      Data Tables does not use the WordPress date format as it uses jQuery DataTables. So you have to do this manually (at least for now). There are two ways to do this:
      (1) Use a view and format your data column in your view definition (SQL)
      (2) Add a renderer (JavaScript)

      There is tutorial how to add a renderer here:
      https://wpdataaccess.com/docs/data-tables-advanced-features/advanced-settings/
      Please search for: fnRowCallback

      But…I think it should be possible to use the WordPress date format. So I added this issue to my to do list. Not sure if I manage to add it to the next release, but I’ll do my best. Hope you don’t mind to wait a little… 🙂

      Thanks,
      Peter

      Reply
      • Joseph
        Joseph on 2020-12-30 at 12:07 pm

        Hi Peter,
        Appreciate your quick response time and solution provided, I’ll try each of the suggestions and see how it goes and of course I’ll be glad to wait for a final WordPress solution 🙂
        Meantime wishing you and your team A Very Happy New Year
        Thanks,
        Joseph

        Reply
      • Joseph
        Joseph on 2020-12-30 at 4:50 pm

        Hi Peter
        Tried to look up all the documentation for dom on https://datatables.net/reference/option/dom, but not able to find anything related to how would I display only first 10 characters of the date value retrieved from the Production remote table or hide anything after 10 characters
        This is what I had in my original php page before moving to WordPress:

        $itemDate = $row['rdate'];
        echo substr($itemDate, 0, 10);

        Any suggestion how I convert this to add it in Data Tables Table options (advanced)
        Thanks,
        Joseph

        Reply
        • Peter Schulz
          Peter Schulz on 2020-12-31 at 8:46 am

          Hi Joseph,

          Below you’ll find the solution. I hope! 🙂 I cannot test it for you…

          Please paste this JSON code into the table options advanced column of your publication:
          { "columns":
          [ null, null, null,
          { "data": "3", "render": "function ( data, type, row, meta ) { return data.substr(0,10); }" },
          null, null, null, null ]
          }

          The third column, which is your date column, is rerendered. If you change your publication, you might need to change your JSON code as well. Make sure the position of your date column matches the position in the columns array: "data": "3" (start counting from 0).

          Wishing you a very happy new year! 🙂

          Best regards,
          Peter

          Reply
          • Joseph
            Joseph on 2020-12-31 at 12:51 pm

            Hi Peter,
            Thank you very much, appreciate your help, I added the code and noticed that now the Date is being rendered as full month dd, yyyy so i adjusted the length of the substr to 18, looks fenomenal, even better than before, much appreciated.
            Happy New Year 🙂
            Thanks,
            Joseph

            Reply

Submit a Comment Cancel reply

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

Table of Contents
  • Table design
  • Table creation
  • Create a new table design
  • Create a table design from an existing database table
  • Local and remote databases
  • Local and remote databases
  • Modes
  • Reverse engineering
  • Reconcile

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