Please wait...

Generating MySQL queries with Table Constructor

Video versionA video overview of generic MySQL query generator in wpDataTables

Example of wpDataTable based on a generated querywpDataTables Table Constuctor has a generic MySQL query generator

WPDB that we studied in the previous tutorial is a custom case of a MySQL database. This is probably the most frequently used case, that’s why a specific Table Constructor option was created for that; but a more generic MySQL query generator exists in wpDataTables as well.

Please note that MySQL query constructor is not an ‘ultimate generator’, it should be consider a helper that constructs a suggestion of a query trying to ‘guess’ what you want. We constantly work on improving it, but SQL is such a complicated and flexible language that full automation for constructing queries is hardly possible. So the more complicated your request is, the higher is the chance that it will not return exactly what you need. Play around with the resulting query to achieve better results.

MySQL example database

In this example we will use a “classic” sample MySQL World database that you can download from MySQL site:

It has 3 tables: City, Country and CountryLanguage.

In this tutorial we will show how to create a wpDataTable that will show all cities with population more then 1500000, the countries that they belong to, and the spoken language.

Let’s see the table first, and then go through the steps needed to create this table and study all the query generator elements in detail.

Table built by auto-generated MySQL query

wpDataTables constructor

First go to your WordPress admin, there open wpDataTables -> wpDataTable Constructor, then choose the fourth option: “I want to construct a table based on data from existing MySQL DB tables” and click “Next“.

WPDB query generator

Let’s go through the elements of MySQL query generator in detail:

1. MySQL tables selectbox. Here you can choose one or several MySQL tables; the list is fetched from the MySQL server defined in wpDataTables settings (by default it is the same DB that WordPress is using, but you can re-configure the plugin to use a remote server).

2. Table columns selectbox. This box is empty when you first open the page, but it gets populated when you select one or more MySQL tables. Here you can choose the columns that you would like to see in the resulting wpDataTable.

3. Table relation rules configurator. This block appears if you choose more then one table. Here you can define relation logic between the tables: which fields of different tables to use for joining.

The wizard generates as many blocks as many tables were chosen (e.g. 3 blocks for 3 MySQL tables), but usually you would need to define one block less then generated (e.g. only 2 out of 3).

The first dropdown of each block represents columns of a certain MySQL table, the second dropdown shows fields of all other MySQL tables, and the checkbox after the dropdowns allows to define whether to use an “inner join” (checked) or a “left join” (unchecked). Inner join means that only the rows that have matches for a particular field in both tables will be returned, left join means that all rows will be returned, even if there are no matches. You can read more about joining e.g. here.

4. Additional conditions block. This block allows to define additional (“WHERE”) conditions: e.g. if you want to see only the rows where a certain column is equal to, greater then, or less then some value, etc.

You can add as many conditions as you need with a “+” button, in the first dropdown you can select a field for defining the condition, in the second dropdown you can choose a comparison operator. It can be one of these:

  • =, <, <=, >=, > – equal, less then, less then or equal, greater then or equal, greater then
  • <> – not equal
  • LIKE – comparison for ‘fuzzy’ comparison with strings. E.g. if you need to return all rows where a “post.post_title” starts with “abc” you would define it like:
Generating SQL query conditions with wpDataTables Table Constructor

‘%’ sign here stands for a pattern, which means that it will match any characters (and any number of characters), if you need to match a single  character you can use the ‘_’ sign.

  • %LIKE% – “Fuzzy” comparison with predefined masked input; it will match any string that contains the defined substring.
  • IN – allows to match against a set of values, e.g. “post.ID IN 1,2,3,4,5,6″

You can always remove the condition blocks with the “X” button.

6. Grouping rules block. This block allows to add grouping by a certain field. This may be needed when e.g. you want to display a post with all of its tags: without grouping the post will be displayed as many times, as many tags it has; so e.g. repeated 5 times if there are 5 tags assigned. But if you group by post title or post ID, it will be displayed only once, and the tags will be comma-separated.

A grouping rule block can be added with a “+” button, it has only one dropdown with all the fields that are selected above, and can be removed with a “X” button.

For this sample table, as you can see, we defined this configuration:

  • Chosen 3 tables in the MySQL tables selectbox: City, Country, CountryLanguage
  • Chosen these columns: City.Name, City.Population, Country.Name, CountryLanguage.Language
  • Defined these relation rules:
    • City.CountryCode = Country.Code (inner join)
    • Country.Code = CountryLanguage.CountryCode (inner join)
  • Defined this grouping column: City.Name

When this configuration is done, we need to click “Next” to see the generated query and preview the result.

Query editor

On the last step of MySQL query generator you can see:

1. The constructed query in a SQL editor (editing is allowed).

2. “Refresh query and preview” button which allows you to see how the returned data set will change right away after your changes.

3. Preview of table data – here you will see first several rows of the returned data set. If you see “No data” here it means that the constructed query does not return any rows either due to an error, or because this logic really returns an empty data set.

In our case we needed to add 2 small adjustments to the query: we added aliases to column names to avoid same names for “Name” columns from “Country” and “City” tables, and also added a “Language.isOfficial = ‘T'” condition so that only official languages would be fetched.

When you’re satisfied just click on the “Create wpDataTable” button so that a wpDataTable would be created and you would be redirected to the table properties editor to adjust some settings – e.g. provide nicer headers for the columns, or define different sorting logic.

That’s pretty much it. We plan to add more flexibility to the query generator in future if this will be a highly requested feature.

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.