Please wait...

Creating Tables in WordPress based on MySQL queries

Video versionA video overview of MySQL-based wpDataTables

wpDataTable MySQL integrationwpDataTables is deeply integrated with MySQL engine

MySQL is a very powerful, yet free SQL-based database engine, which allows to create both small and huge databases, and to create, read, and edit data in these databases really quickly and effective. That’s why it has been for many years, and stays the #1 database engine in the web – and even WordPress itself runs on MySQL.

wpDataTables is deeply integrated with MySQL engine, it allows to display results of SQL query in a table, can use MySQL server to do searches/filters/sorting/pagination for large datasets, allows front-end (and back-end) editing of MySQL tables, creating MySQL tables from back-end, importing CSV or Excel to editable MySQL tables, generating SQL queries with visual constructor, and many more.

Whenever your data set gets more “serious” then a couple of hundreds of rows, and when you need more performance, MySQL-based wpDataTables is the way to go.

In this tutorial we will show how to create a wpDataTable based on a query to MySQL table.

You can download the SQL dump for this sample “dummy employees” table from this link and import it to your MySQL server using PHPMyAdmin or other software, to repeat the tutorial steps.

Let’s see the table first, and the step-by-step tutorial on how to create it below.

Employees test - MySQL example



Please note: to use this plugin feature you need to have at least basic knowledge of SQL, it is assumed that you can create the table in some MySQL data manager (PHPMyAdmin, MySQL Workbench) and prepare a SQL query that will return the data that you need. If you do not know how to use MySQL, refer to <a href=”/documentation/creating-new-wpdatatables-with-table-constructor/”>creating tables with Table Constructor</a> section which will explain how to create complete tables or MySQL queries without this knowledge. Please be advised that preparing MySQL queries for you is not included in plugin support, but can be done as a paid customization service.

Instructions to create the table

First step for creating a MySQL-based wpDataTable is to prepare the data on MySQL side.

We will assume here that you will use the same data set as we did in this example, and import the data via PHPMyAdmin (since almost every host has this tool pre-installed), but this is not mandatory. You can also prepare the structure and fill in the data from PHPMyAdmin, or from any other software.

So, first download the SQL dump from this link.

Then go to your PHPMyAdmin, choose the DB that wpDataTables is configured to use, open the “import” tab, click “browse files” and open the SQL dump file that you downloaded:

Then click “Go” and PHPMyAdmin will import the dump and create a table in your MySQL database.

You can then click on this table in the database browser on the left to review the results:

Now we need to prepare a query that will return the data that we need in our table. In our case it will be simple: “SELECT * FROM dummy_employees“. You can try it in “SQL” PHPMyAdmin tab to make sure that it returns the data that you need.

Such a simple SQL query is used here just for demo purposes. Feel free to paste complicated queries with joins, nested queries, conditions, or whatever else works correctly on MySQL side.

Create the wpDataTable and paste the query

Now that the data is prepared on MySQL server side, we need to create a wpDataTable that will display this data.

  1. Open your WordPress admin panel, and go to wpDataTables ->  Add from Data Source.
  2. Provide some name for the table in the Table Title input, that will help you to identify this table later.
  3.  Select MySQL query in the Table Type selectbox.
  4. Paste the SQL query that we prepared in the step 1 (SELECT * FROM dummy_employees) in the SQL editor that appears in MySQL query input.
  5. Click “Save” so that wpDataTables would read the table structure and initialize the columns metadata.

(Optional) - Define additional table and column settings

Once the table structure is read and the column metadata is initialized by wpDataTables, you might want to re-define the default settings.

In our case, we defined the following settings:

  1. Checked the “Limit table layout” checkbox;
  2. Checked the “Word wrap” checkbox;
  3. Unchecked the “Visible” checkbox for “id” column;
  4. Defined nicer name for columns: “First name“, “Last name“, “Birth date“, “Date hired“, “Position“.
  5. Defined “Birth date” and “Date hired” column types as “Date“, also set the filter type for these columns as “Date range“.
  6. Defined filter type for the column “Position” as “Select box“, also set the width of this column as 30%.

Once the setup is done click “Save” again to store the changes in wpDataTables. Also you can click “Preview” to see how the table will look in the front-end.

Insert the wpDataTable in WordPress post or page

When you finished preparing your MySQL-based wpDataTable, you just need to insert it to your post or page.

Create or open a WordPress post or page, place the cursor in the position where you would like to insert your table, click the “Insert a wpDataTable” button in the MCE editor panel, and choose the MySQL-based table that you prepared.

Or you can copy the wpdatatable shortcode that the wpDataTables edit page shows, and paste it manually where you need it.

Never miss new features!

Join 2000+ newsletter subscribers

Never miss notifications about new cool features, promotions, giveaways or freebies - subscribe to our newsletter! We send about one mail per month, and do our best to keep our announcements interesting.

We never spam or disclose your address to anyone.