Video versionA video overview of Create a MySQL-query-based table by generating a query to MySQL database
Example of wpDataTable based on a generated querywpDataTables Table Constuctor has a generic MySQL query generator
The WPDB we studied in the previous tutorial is a custom example of a MySQL database. MySQL based tables are one of the most popular table types in wpDataTables, which is why a specific “Generate a query to MySQL database” option was created; a more generic MySQL query generator also exists in wpDataTables.
In this example, we will use a “classic” sample MySQL World database that you can download from the MySQL site: http://dev.mysql.com/doc/index-other.html
It has 3 tables: City, Country and CountryLanguage.
In this tutorial, we will demonstrate how you can create a wpDataTable that will show all cities with populations greater than 1,500,000, the countries where they are located, and the language spoken.
We’ll examine the table first, and then go through the steps necessary to create this table and study in detail the query generator elements.
First, go to your WordPress admin, open wpDataTables -> Create a Table, and choose the fourth option: “Generate a query to MySQL database“. Click “Next“.
Going through the elements of the MySQL query generator in detail:
1. All MySQL tables. Here you can see all MySQL tables in your database, and choose one or several that you want to use in wpDataTables. 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. Selected MySQL tables. This box is empty when you first open the page. It becomes populated with those MySQL tables you want to use in your query. You can add tables from the All MySQL tables box by selecting them and clicking on the 
button, or by dragging them.
3. All MySQL columns. This box is empty when you first open the page, but it gets populated when you select one or more MySQL tables. Here you will see the columns you would like to appear in the resulting wpDataTable.
4. Selected MySQL columns. This box is empty when you first open the page. It becomes populated with the MySQL columns you want to use in your query. You can add columns from the All MySQL columns box by selecting them and clicking on the button, or by dragging them.
5. Table relation rules configurator. This block appears if you choose more than one table. Here, you can define the relational logic between the tables: i.e., which fields of different tables to use for joining.
The wizard generates as many blocks as the number of tables that were chosen (e.g. 3 blocks for 3 MySQL tables), but you will usually need to define one block less than the generated number (e.g. only 2 out of 3).
The first dropdown of each block represents the columns of a certain MySQL table, the second dropdown shows the fields of all other MySQL tables, and the checkbox after the dropdowns allows you 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 given field in both tables will be returned. Left join means that all rows will be returned, even if there are no matches. Read more about joining here.
6. Additional conditions block. This block allows you to define additional (“WHERE”) conditions: e.g., if you want to see only the rows in which a certain column is equal to, greater than, or less than some value, and so on.
You can add as many conditions as you need to with an 
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 the following:
- =, <, <=, >=, > – equal, less than, less than or equal, greater than or equal, greater than
- <> – not equal
- LIKE – comparison for ‘fuzzy’ comparison with strings. For example, if you need to return all rows where a “orders.ship_name” starts with “abc” you would define it like on the image on the right.
The ‘%’ sign 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% – This is a “Fuzzy” comparison with a predefined masked input; it will match any string that contains the defined substring.
- IN – allows you 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 
button.
7. Grouping rules block. This block allows you to add grouping by a certain field. This may be needed if, for example, you want to display a post with all its tags. Without grouping, the post will be displayed as many times, as the number of tags it has; for example, it will be 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 an 
button. It has only one dropdown with all the fields that are selected above, and it can be removed with the a button.
For this sample table, we’ve defined the following 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.
On the last step of the MySQL query generator you can view:
1. The constructed query in an SQL editor (editing is allowed).
2. A “Refresh query and preview” button, which allows you to see how the returned data set will change immediately after your changes.
3. A Preview of table data – Here you will see the first several rows of the returned data set. If you see “No data”, it means that the constructed query does not return any rows, either due to an error, or because the logic has in fact returned 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 identical names for “Name” columns from “Country” and “City” tables, and we also added a “Language.isOfficial = ‘T'” condition, so only official languages would be fetched.
Once you’re satisfied, click on the “Create the table” button. A wpDataTable will be created, and you’ll be redirected to the table properties editor to adjust some settings – e.g., provide more appropriate headers for the columns, or define different sorting logic.
That’s it. We plan to add greater flexibility to the query generator in the future in the event it becomes increasingly popular and highly requested feature.
This is how your table will look like with ‘Aqua skin’ To insert it on your page or post please check out the section for Adding wpDataTables shortcodes on the page.
