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
WPDB that we studied in the previous tutorial is a custom case of a MySQL database. MySQL based tables are one of the most popular table types in wpDataTables, that’s why a specific “Generate a query to MySQL database” option was created for that; but a more generic MySQL query generator exists in wpDataTables as well.
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.
|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 -> Create a Table, then choose the fourth option: “Generate a query to MySQL database” and click “Next“.
Let’s go through the elements of 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 wpDataTable. 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. This box gets populated with MySQL tables that you want to use in query. You can add tables from All MySQL tables box by selecting them and clicking on 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 can see the columns that you would like to see in the resulting wpDataTable.
4. Selected MySQL columns. This box is empty when you first open the page. This box gets populated with MySQL columns that you want to use in query. You can add columns from All MySQL columns box by selecting them and clicking on button or by dragging them.
5. 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.
6. 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 “orders.ship_name” starts with “Abc” you would define it like on the image on the right.
‘%’ 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 button.
7. 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 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 the table” 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.