News Blog: WordPress & Joomla Extensions

From Databases to Tables with WP Table Manager

From Databases to Tables with WP Table Manager

WP Table Manager is a useful tool that allows you to create and display tables for your users. We’ve already seen how the WordPress plugin can be used to create tables manually or import and sync them with Excel files. The new update allows you to do even more: create tables from your MySQL database, which stores data about your WordPress website.

There are many reasons why you’d want to connect your database to WP Table Manager, and it is not isolated to just website developers either! For example, you might want to compile a quick, organized table with all your users or a table of recent posts. The possibilities are endless, and WP Table Manager has got you covered!

 

Setting up Tables

Setting up tables from your WordPress database is very simple. To get started, head to WP Table Manager, click on Create New from the top left and then choose Database Table. WP Table Manager will take you to the Table Creation Wizard: a one-pager full of possibilities.

1-Create-new-database-table

 

The Table Creation Wizard is separated into sections. You might want to think about each section and process sequentially to avoid having to go back-and-forth. In the Table Creation Wizard, you will go through the process of choosing tables, choosing columns, setting column titles, and adding other MySQL-like conditions.

The first step is choosing which database tables you want to use to create tables. It's all very simple: the first section shows you all tables in your WordPress database. You can select multiple tables by clicking on them, and if there are too many to browse, use the search functionality. Click on Next, select columns to proceed.

2-Table-creation-wizard

 

The second step is to choose which columns to use when creating your new tables. Once again, you can choose multiple columns and search. Press on Next, column titles to rename your columns; since most column names are not friendly, you can choose a simpler display name for each one.

3-Columns

 

The next part is where WP Table Manager shines. If you're a SQL programmer, you will find that WP Table Manager gives you all the functionality of SQL when creating database tables. And if you are not a SQL programmer, you will find it's very simple to get started.

The first SQL-like setting lets you define relations between tables, or foreign keys in SQL. In our example, we are displaying information from the post and postmeta tables. Each post has an ID, and each post ID has metadata associated with it. The relations settings let you specify these relations, as shown in the next figure.

4-Display-settings

 

The next settings are also very similar to MySQL. The first one is analogous to SQL's where, or filtering. This functionality lets you add display conditions, such as only showing information about certain posts. The box on the right lets you group the table's data by the value of certain columns. In both cases, you can introduce new rules by clicking on Add.

The final three settings are more concerned with how the table looks. First, you can specify the sorting rules. Next, you can enable or disable pagination, which is most useful when you have a lot of data. Finally, you can limit the number of rows to show: 10, 20, 40 or all rows.

When you're ready, click on Next Step, which will take you to a brand new screen. The latest additions to WP Table Manager allow you to do three new things, and two of them are here. The first option is to allow table editing. By default, the Allow editing switch is off, which means that you cannot edit cell content, but now you can toggle it on.

5-Allow-editing

 

If you allow table editing, you need to be careful. You need to choose a base column for row identification, or the primary key, from the dropdown on the right. This dropdown exists because when you are editing the table, you are also editing the database.

Just below, you can preview the MySQL script used to generate the table. You do not have to edit this script yourself; WP Table Manager generates it automatically from the settings you chose earlier. However, if you are an SQL programmer, you can tweak the script. Click on Apply whenever you update the MySQL script.

6-MySQL-query

 

When you are done, you can preview the table, and if you're satisfied, create the table. Once WP Table Manager creates the table, the plugin will take you to the table editor. If you enabled cell editing, you can change the cells' contents; otherwise, the cells will be greyed out.

Before you leave the table editor, there is one more new feature. If your intention is to display the table to your WordPress users, you might want to hide certain columns in the frontend while keeping them in the backend. To hide columns in the frontend, simply right click on a column name and click on the first option; repeat the process if you want to show the column.

7-Hide-column

 

After creating the database table, all of WP Table Manager's features are at your disposal. You can create charts from the Chart menu or embed the table in any post or page. Simply head to a post or a page, click on WP Table Manager, navigate to the newly-created table and insert it!

Tables created in this way are maintained automatically, which means that as data changes in your database, the modifications are reflected right away in your tables. So go ahead, create your tables, visualize them with ease, and share them with your WordPress users.

 

Automatic charts from WordPress database

Now that we have created our table from the database let's see how to create a chart to display important, and useful things on your site, for example, let's say that we have a subscription site and we want to show how many users have been joining to our site with a chart to make it looks professional, for this, you can look into your tables for the last user registered, and check the ID which should be the number where the last user is positioned meaning that, for example, the first user registered ahs as ID 1, the second user registered has as ID 2, and so on.

Now that we know this, you can set the conditions to show the table called Prefix_users(default prefix is wp_), in columns, select the .ID, and the .user_status(if you want to show id the user is active or not) then, in order, set it as descending so we will have the last user registered(with the total amount of users) at the top, and set the rows to be only 10 as we will only need the first row.

 

set-chart-rules

Now that we have selected this, you can proceed to the chart creation, you will see a table like this one.

 

user-database-table

Now select all the rows that you want to add, and then, click on  Chart > Create Chart from Data in the top panel.

 

create-chart-from-data

On the right panel, you will have all the available options to create the chart like the chart theme, colors.

 

right-panel

So set the chart how you want and, in the left panel, you'll see a live preview just so you don't publish it until it's perfect :)

 

graph-preview

As you can see, a perfect tool as it is going to be automatically updated without having to edit anything on your own, on each new registration, the chart will be updated automatically in the frontend, you will only have to go to edit it in case you want to, for example, change the color, or the chart theme.

If you think about it, the possibilities are unlimited! You can add more plugins, customize your database and all these tables are going to be available to create tables, and charts from them ;)

This can be used at a frontend level for your users as well for your staff so you can, for example, publish a private page to show statistics from your products, sales, and many other things, possibilities are unlimited!

 

Do you want to start using database tables on WordPress? Get WP Table Manager from here!

2
Numbers and Stats in WP File Download
Caching Meets Joomla!
 

Comments

No comments made yet. Be the first to submit a comment
Already Registered? Login Here
Guest
Tuesday, December 07 2021

Captcha Image

By accepting you will be accessing a service provided by a third-party external to https://www.joomunited.com/