WordPress uses MySQL, as an open source database management system to store and retrieve all of the website’s information, everywhere from the content of the posts and pages to the comments, usernames, and passwords of your website.
What is MySQL
MySQL in WordPress is a central component in the LAMP stack of open source web application software which is used to create websites where LAMP stands for Linux, Apache, MySQL, and PHP. It is also used in the LAMP stack, which replaces Apache for Ngnix aka Engine-X.
WordPress uses PHP to store and retrieve data from MySQL databases, by using the SQL queries within the PHP markup.
WordPress data tables are deeply integrated with the MySQL engine. Allowing you to create both small and large databases, and to create, read, and edit data in these databases quickly efficiently.
How to create SQL Tables
The first step for creating a MySQL-based wpDataTable is to prepare the data on MySQL side.
For that, you can prepare the structure and fill in the data from PHPMyAdmin, or from any other software.
Now, go to PHPMyAdmin, choose the database that wpDataTables is configured to use, open the “import” tab, click “browse files” and open the SQL dump file that you have downloaded.
Then, click on “Go” and PHPMyAdmin will import the dump and create a table in MySQL database.
You can click on this table shown in the database browser on the left to review the results.
Next, prepare a query that will return the data you need in your table. It will be simple in this case: “SELECT * FROM dummy_employees“. You can try it out in “SQL” PHPMyAdmin tab to make sure that it returns the data that you need.
Create the wpDataTable and paste the query
Now when the data is prepared on the MySQL server side, you need to create a wp-DataTable to display this data.
1. Go to wpDataTables, then Create a Table, and select the Create a table linked to an existing data source option.
2. Provide a name for your table in the Table Title input, it will help you to identify this table later.
3. Select the MySQL query in the Input data source type select box.
4. Paste the SQL query you have prepared in step 1 (SELECT * FROM dummy_employees) in the SQL editor that appears in MySQL query input.
5. Now click on “Apply” so wpDataTables will read the table structure, and initialize the columns metadata.
Insert the wpDataTable in WordPress post or page
Now when you are finished preparing your MySQL-based wpDataTable, you need to insert it to your post or page.
Create or open a WordPress post or page, place the cursor in the position where you would like to insert your table, click the “Insert a wpDataTable” button in the MCE editor panel and choose the MySQL-based table that you have prepared.
Or, you can even copy the wp-datatable shortcode that the wpDataTables edit page shows, and paste it manually wherever you need it.