Please wait...

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

Video versionA video overview of Create a MySQL-query-based table by generating a query to WordPress database

Example of a wpDataTable based on a generated queryUsing a built-in WPDB query generator

WordPress and its plugins store the data in a specific MySQL database, usually referred to as “WPDB”. Structure that is known to users is based on different post types, post custom (meta) fields, and taxonomies; and these are all stored in a number of MySQL tables; and querying these might become a headache to somebody not that familiar with relative databases structure and building SQL queries.

wpDataTables has a built-in WPDB query generator that is made to help such users – it has a graphical user interface which allows to pick the post types, custom/meta fields, taxonomies, and generates a query and a preview. You can modify the query as you need it, and then create a wpDataTable based on this query.

Let’s see a live example first, and then go through all the WPDB query constructor elements.

This site runs on WP, and let’s say we would like to get the documentation data in a wpDataTable.

Please note that this tool is not an ultimate query generator, it just constructs a suggestion of a query. 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 the query is, the higher is the chance that it will not return exactly what you need. So play around with the resulting query.

WPDB query generator

Generate a query to WordPress database

First we go to WordPress admin, open wpDataTables -> Create a Table, then choose the third option “Generate a query to WordPress database“, and click “Next“.

The documentation is stored in WordPress pages, so we choose “page” post type in the first selectbox, “page.ID“, “page.post_title“, “page.title_with_link_to_post”, “page.post_author”. Just for demo purposes we added one condition: “page.ID > 5“.

Let’s go through the elements of WPDB query generator in details:

1. All post types. Here you can choose one or more post types for your table, this list is generated by WordPress and is ‘unique’ for each WordPress installation, since different themes, plugins or self-written code register different post types. It is allowed to choose one or more post types, also you can select the “all” option if you want to fetch all posts independent from the type.

2. Selected post types. This box is empty when you first open the page. This box will show post types that you want to use in query. You can add post types from All post types tables box by selecting them and clicking on  button or by dragging them.

3. All post properties. This selectbox is initially empty, and is filled when you choose one ore more post types. In this selectbox you will see the full list of parameters for each post type, in a form “post_type_name.paramter_name” (separated with a dot); parameters for each post type include the “default” ones (title, author, etc.) and the full list of registered meta keys for the post types, and taxonomies.

4. Selected post properties. This box is empty when you first open the page. This box will show post properties that you want to use in query. You can add columns from All post properties box by selecting them and clicking on  button or by dragging them.

Generate a query to WordPress database
Generate a query to WordPress database

5. Post type relation rules configurator. This block appears if you choose more then one post type, it helps you to define how do the posts type relate in the query: should they be treated as “equal”, or should there be some kind of “parent-child” relation.

First option is “Define relations (joining rules) between post types“. If you choose this option, you will need to also provide the relationship logic (see point 6), so the “INNER JOIN” or “LEFT JOIN” block would be generated for you. This can be used if you want to return e.g. a post with its children.

Second option is “Do not define relations between post types – do a full outer join“. In this case all the rows will be treated as equal and output in the same table without any additional logic.

6. Relation rules generation block. In this block you can define the relations between different post type properties for joining them. E.g. “post.post_parent = page.ID“. The wizard generates as many blocks as post types chosen (e.g. 2 blocks for 2 post types), but usually you would need to define one block less then generated (e.g. only 1 out of 2).

The first dropdown of each block represents fields of the certain post type, the second dropdown shows fields of all other post types, 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.

7. Additional conditions block. This block allows to define additional conditions: e.g. if you want only the rows where a certain field is equal to, greater then, or less then some value, etc.

You can add as many conditions as you need with a Add MySQL condition 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 on the screenshot 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 Delete MySQL grouping button.

Generate a query to WordPress database

8. 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 Delete MySQL grouping button.

Query editor

Generate a query to WordPress database

Next page is the query and result preview page. It has just several elements:

1. Query editor. Here you can see the query that wpDataTables generated for you. You might be not quite satisfied with it so you can edit it here right away.

2. Refresh button. If you edit the query, click this button to send the query to the server, and fetch the results in the preview table.

3. Results preview. Here you can see first several rows that MySQL server returns using the query provided. If you see “No data” here it means that MySQL server returned nothing, either because of an error in the query, or because it really returns just an empty data set though the syntax is correct.

When you’re satisfied with the results, just click on “Create the table” to initialize a new wpDataTable and edit its settings, e.g. provide nicer column names, configure responsiveness, or other options.

If you have a lot of pages, you can then enable server-side processing for the table so it would load faster and work faster on the front-end.

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.