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.
Generating MySQL queries with Table Constructor
In this example we will use a “classic” sample MySQL World database that you can download from MySQL site: http://dev.mysql.com/doc/index-other.html
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
|Cape Town||2.352.121||South Africa||Afrikaans|
|Ciudad de México||8.591.309||Mexico||Spanish|
|Dar es Salaam||1.747.000||Tanzania||Swahili|
|Ecatepec de Morelos||1.620.303||Mexico||Spanish|
|Ho Chi Minh City||3.980.000||Vietnam||Vietnamese|
|Kowloon and New Kowloon||1.987.996||Hong Kong||English|
|Los Angeles||3.694.820||United States||English|
|New York||8.008.278||United States||English|
|Rio de Janeiro||5.598.953||Brazil||Portuguese|
|Santafé de Bogotá||6.260.862||Colombia||Spanish|
|Santiago de Chile||4.703.954||Chile||Spanish|
|Santo Domingo de Guzmán||1.609.966||Dominican Republic||Spanish|
|St Petersburg||4.694.000||Russian Federation||Russian|
|Tripoli||1.682.000||Libyan Arab Jamahiriya||Arabic|
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“.
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:
‘%’ 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.
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.