Create a MySQL-query-based table by generating a query to MySQL database

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 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.

City

Population

Country

Language

Ahmedabad

2,876,710

India

Hindi

Alexandria

3,328,196

Egypt

Arabic

Alger

2,168,000

Algeria

Arabic

Ankara

3,038,159

Turkey

Turkish

Baghdad

4,336,000

Iraq

Arabic

Baku

1,787,800

Azerbaijan

Azerbaijani

Bandung

2,429,000

Indonesia

Malay

Bangalore

2,660,088

India

Hindi

Bangkok

6,320,174

Thailand

Thai

Barcelona

1,503,451

Spain

Spanish

Belo Horizonte

2,139,125

Brazil

Portuguese

Berlin

3,386,667

Germany

German

Brasília

1,969,868

Brazil

Portuguese

Bucuresti

2,016,131

Romania

Romani

Budapest

1,811,552

Hungary

Hungarian

Buenos Aires

2,982,146

Argentina

Spanish

Cairo

6,789,479

Egypt

Arabic

Calcutta [Kolkata]

4,399,819

India

Hindi

Cali

2,077,386

Colombia

Spanish

Cape Town

2,352,121

South Africa

Afrikaans

Caracas

1,975,294

Venezuela

Spanish

Casablanca

2,940,623

Morocco

Arabic

Changchun

2,812,000

China

Chinese

Changsha

1,809,800

China

Chinese

Chengdu

3,361,500

China

Chinese

Chennai (Madras)

3,841,396

India

Hindi

Chicago

2,896,016

United States

English

Chongqing

6,351,600

China

Chinese

Ciudad de México

8,591,309

Mexico

Spanish

Curitiba

1,584,232

Brazil

Portuguese

Dalian

2,697,000

China

Chinese

Dar es Salaam

1,747,000

Tanzania

Swahili

Delhi

7,206,704

India

Hindi

Dhaka

3,612,850

Bangladesh

Bengali

Ecatepec de Morelos

1,620,303

Mexico

Spanish

Faisalabad

1,977,246

Pakistan

Urdu

Fortaleza

2,097,757

Brazil

Portuguese

Fuzhou

1,593,800

China

Chinese

Giza

2,221,868

Egypt

Arabic

Guadalajara

1,647,720

Mexico

Spanish

Guayaquil

2,070,040

Ecuador

Spanish

Hamburg

1,704,735

Germany

German

Hangzhou

2,190,500

China

Chinese

Harbin

4,289,800

China

Chinese

Ho Chi Minh City

3,980,000

Vietnam

Vietnamese

Houston

1,953,631

United States

English

Hyderabad

2,964,638

India

Hindi

Inchon

2,559,424

South Korea

Korean

Istanbul

8,787,958

Turkey

Turkish

Izmir

2,130,359

Turkey

Turkish

Jakarta

9,604,900

Indonesia

Malay

Jedda

2,046,300

Saudi Arabia

Arabic

Jinan

2,278,100

China

Chinese

Jokohama [Yokohama]

3,339,594

Japan

Japanese

Kabulg

17,800,004

Afghanistan

Dari

Kanpur

1,874,409

India

Hindi

Kanton [Guangzhou]

4,256,300

China

Chinese

Karachi

9,269,265

Pakistan

Urdu

Kowloon and New Kowloon

1,987,996

Hong Kong

English

Kunming

1,829,500

China

Chinese

Kyiv

2,624,000

Ukraine

Ukrainian

La Habana

2,256,000

Cuba

Spanish

Lahore

5,063,499

Pakistan

Urdu

Lanzhou

1,565,800

China

Chinese

Lima

6,464,693

Peru

Aimará

London

7,285,000

United Kingdom

English

Los Angeles

3,694,820

United States

English

Lucknow

1,619,115

India

Hindi

Madrid

2,879,052

Spain

Spanish

Manila

1,581,082

Philippines

Pilipino

Mashhad

1,887,405

Iran

Persian

Medan

1,843,919

Indonesia

Malay

Medellín

1,861,265

Colombia

Spanish

Melbourne

2,865,329

Australia

English

Minsk

1,674,000

Belarus

Belorussian

Moscow

8,389,200

Russian Federation

Russian

Mumbai (Bombay)

10,500,000

India

Hindi

Nagoya

2,154,376

Japan

Japanese

Nagpur

1,624,752

India

Hindi

Nanchang

1,691,600

China

Chinese

Nanking [Nanjing]

2,870,300

China

Chinese

New York

8,008,278

United States

English

Osaka

2,595,674

Japan

Japanese

Paris

2,125,246

France

French

Peking

7,472,000

China

Chinese

Philadelphia

1,517,550

United States

English

Pune

1,566,651

India

Hindi

Pusan

3,804,522

South Korea

Korean

Pyongyang

2,484,000

North Korea

Korean

Qingdao

2,596,000

China

Chinese

Quezon

2,173,831

Philippines

Pilipino

Quito

1,573,458

Ecuador

Spanish

Rangoon (Yangon)

3,361,700

Myanmar

Burmese

Rio de Janeiro

5,598,953

Brazil

Portuguese

Riyadh

3,324,000

Saudi Arabia

Arabic

Roma

2,643,581

Italy

Italian

Salvador

2,302,832

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

São Paulo

9,968,485

Brazil

Portuguese

Sapporo

1,790,886

Japan

Japanese

Seoul

9,981,619

South Korea

Korean

Shanghai

9,696,300

China

Chinese

Shenyang

4,265,200

China

Chinese

Shijiazhuang

2,041,500

China

Chinese

Singapore

4,017,733

Singapore

Chinese

St Petersburg

4,694,000

Russian Federation

Russian

Surabaya

2,663,820

Indonesia

Malay

Sydney

3,276,207

Australia

English

Taegu

2,548,568

South Korea

Korean

Taipei

2,641,312

Taiwan

Mandarin Chinese

Taiyuan

1,968,400

China

Chinese

Teheran

6,758,845

Iran

Persian

Tianjin

5,286,800

China

Chinese

Tokyo

7,980,230

Japan

Japanese

Toskent

2,117,500

Uzbekistan

Uzbek

Tripoli

1,682,000

Libyan Arab Jamahiriya

Arabic

Warszawa

1,615,369

Poland

Polish

Wien

1,608,144

Austria

German

Wuhan

4,344,600

China

Chinese

Xi´an

2,761,400

China

Chinese

Zhengzhou

2,107,200

China

Chinese

City

Population

Country

Language

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.

WPDB query generator

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.

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 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.

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.