Construct Table from Custom SQL

One of the most versatile features of Ninja Tables is the Custom SQL Query. You can create a table from your SQL database with the help of your custom SQL query. Here the table data is dynamically fetched. Each change that is manually made in the database or on a plugin, will be displayed in the table’s front-end that was made by the Custom SQL.

For example, when you create a table from the wp user table it will display all user data on the table. Once you have a new user, it automatically synchronizes with your database table on your site. After that, the table can be edited and redesigned like a default table.

Now, here is a pretty simple table is created in Ninja Tables using Custom SQL Query:

Below you can notice a database table on my WordPress DB called wp_buyers_table. You can easily create a table from this database table by writing a single line query in our Ninja table.

Steps to make a Custom SQL table:

First, go to the Add Table button and Custom SQL and name your table. Then write a custom SQL query:
SELECT * FROM “your-database-table-name”

For our wp_buyers_table it will be like:
SELECT * FROM `wp_buyers_table`

And then click on the Add button.

Congratulations! Your Table is now successfully created. You can alter the design of your table like you can with other tables.

Some conditional Query:

The data can be accessed conditionally from your database table. Here you can use the standard SQL coding method. You can set the condition of the data to fetch after the keyword WHERE.

Single conditional queries:
So if a table is made from the wp_buyers_table table to display only book buyers then this structure should be followed.

SELECT * FROM `Your Database Table Name` WHERE columnName = ‘yourFilterValue’

For our wp_buyers_table it will be:
SELECT * FROM `wp_buyers_table`WHERE products = 'book'

Multiple conditional queries:
If a table is created to display book and computer buyers from the wp buyers table then this structure can be followed.

SELECT * FROM `wp_buyers_table` WHERE coloumnName = ‘filterData1’ OR columnName= ‘filterData2’

For our wp_buyers_table it will be like this:
SELECT * FROM `wp_buyers_table`WHERE products = 'book'OR products = 'Computer’

Few Basic Examples:

With respect to this `wp_buyers_table` all sample queries are written, you can write your own based on your table data.

To grab the first five data:
SELECT * FROM `wp_buyers_table` WHERE id <= 5

To fetch data except the first five:
SELECT * FROM `wp_buyers_table` WHERE id > 5

To get data of the first five that contains only book buyers:
SELECT * FROM `wp_buyers_table` WHERE id <=5 AND products = 'book'

To fetch the data of the first five except book buyers:
SELECT * FROM `wp_buyers_table` WHERE id <=5 AND products != 'book'

To get the data of the first five pen and computer buyers:
SELECT * FROM `wp_buyers_table` WHERE id <=5 AND (products = 'pen' OR products = 'computer')

Advanced Custom Queries

To write queries, some Dynamic Placeholders are available. Use this shortcode below on your queries to dynamically use the current user id, current date, current date-time, current post id, current post title, and prefix value.

Current user id: {current_user_id}

Current Date: {current_date}

Current Date Time: {current_date_time}

Current Post Id: {current_post_id}

Current Post Title: {current_post_title}

And Prefix: {prefix}

Example Of some advanced queries:

To create a table from our database table’s wp-posts where the current user creates the data it will be:

SELECT * FROM wp_posts WHERE post_author = {current_user_id}

When the data is created before the current date:
SELECT * FROM `wp_posts` WHERE post_date < {current_date}

When the data is created by the current user:
SELECT * FROM `wp_posts` WHERE user_id = {current_user_id}

When the data needs to match with the post title, we can write:
SELECT * FROM `wp_posts` WHERE post_title = {current_post_tltle}