Retrieve and display data from WordPress database
So, you want to fetch some data from your WordPress database and then display it somewhere on your blog? Easy!
All you need to know is some basics of the MySQL and Twig syntax, which you can learn instantly and effortlessly. The fastest way is to look at the examples found in Twig Reference.
In this tutorial, let’s go through a few steps to display post status statistics like this:
In other words, we want to display the number of published vs unpublished pages.
Step 1. Create a new Twig Template
Simply click on the “Add New” menu item under the “Twig Templates” menu to create a new Twig Template. Give it a name and choose MySQL settings as by the screenshot:
What does MySQL Result Type mean? It simply allows you to choose among the available native WordPressdata retrieval methods. In short:
- get_var – get a single value
- get_row – get a single row
- get_results – get all rows from the result of the MySQL query
- get_col – get multiple rows, every one consisting of a single column only
Step 2. Build a MySQL query
Next, enter your MySQL query into the MySQL Query area. The query is pretty simple – it looks like a standard MySQL query with just a few additions in the syntax:
SELECT post_status, count(post_status) as count
FROM {{ wp_globals.wpdb.posts }}
WHERE post_type = 'page'
GROUP BY post_status
If you didn’t know, in WordPress, table names are usually prefixed with wp_. For example, wp_posts, wp_postmeta etc. However, there is no guarantee that the prefix used in your WordPress instance is wp_ . What is more, in multisite setup, each blog will use its own tables, every one named differently. That is why WordPress makes table names available in a special global variable – $ wpdb . Twig Anything makes this variable available in your queries, so that you can use it to retrieve the correct table name, like this: {{ wp_globals.wpdb.posts }}
When rendering your template, this will be transformed to wp_posts or whatever is correct for your particular blog setup. I recommend you looking at the WordPress documentation for the complete list of the table namesavailable.
Actually, Twig Anything makes all WordPress globals available for you, so, for example, you can get the month of the post being displayed: {{ currentmonth }} .
For your convenience, there are more useful things available in both MySQL queries and Twig Templates, which is covered in the templates reference.
Step 3. Build a template
Now that we know which data is retrieved from our database, we want to display it. For this to happen, we have to enter a template, which is a simple HTML with Twig syntax enabled.
<ul>
{% for stat in data %}
<li>
{% if stat.post_status == 'draft' %}
Drafts:
{% elseif stat.post_status == 'publish' %}
Published:
{% else %}
{{ stat.post_status }}:
{% endif %}
{{ stat.count }}
</li>
{% endfor %}
</ul>
In your templates, you can access the MySQL result by a simple syntax:
{{ data.field1.field2.etc }}
In other words, the query result is stored in the data variable, which is accessible in your template. Because we selected get_results MySQL Result Type above, we expect data to hold an array, so we can loop over it with using Twig’s for statement:
{% for stat in data %} ... {% endfor %}
Everything inside this loop can access the stat variable, which is a particular row from our MySQL query result.
Now, we can use simple Twig’s if/else statement and {{stat.post_status}} and {{stat.count}} to output data in a simple <li> HTML tag, which makes up a single item in a list.
Finally, we wrap everything in <ul>...</ul> , which makes up a complete non-enumerated list in HTML.
When done, click on the blue Publish button to make your template available and ready to use.
You can optionally preview how your template renders by clicking on the “View rendered template” button right under the title:
Step 4. Embed it anywhere in WordPress
To output your template in WordPress, just use the standard WordPress shortcodes mechanism:
[twig-anything slug="abc"]
Replace abc by the slug found under the template title:
Alternatively, copy a ready-to-use shortcode from the Shortcode panel found to the right of your template code:
Now you can output your template wherever you wish: in WordPress posts, pages, widgets, footers etc.
Visual Composer（演示）
Discourse（演示，教程 1，教程 2）
