Construct Table from Custom SQL

Custom SQL Query is one of the most powerful features of the Ninja Tables. Using your custom SQL query you can make a table from your SQL database table. Where the table data comes dynamically. Every change that you made on your database by manually or any plugin it will appear on the frontend table that you made by Ninja tables custom SQL.

As an example If you make a table from the wp_user table then, it will show all the users data on your table. When a new user creates on your site it will automatically sync with your database table.
Then you can modify, and redesign your table like a default table.

Isn’t it crazy!

Let’s create a very simple table on Ninja Tables using a custom SQL query:

Here we can see a database table on my wp database named wp_buyers_table. From this database table, we can easily make a table in our Ninja table by writing a single line query.

A database table that we want to make on Ninja table

Steps to create a table using custom SQL:

  1. Click on the Add Table button & select Custom SQL
  2. Name your table
  3. 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`
  4. Click on Add
Steps for creating custom SQL table

Congratulation! Your Table is now created successfully. You can modify your table design as you can do with other tables.

Full table imported from database

Let’s see some conditional Query:

You can get the data from your database table conditionally. Where you can use the normal way of SQL coding. You can set the data fetch condition after the WHERE keyword.

Single conditional queries:
Now If we want to create a table from  wp_buyers_table table to show only book buyers then we should follow this structure.

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'

Then you can get this filtered table:


Multiple conditional queries:
Now If we want to create a table from  wp_buyers_table to show book and computer buyers then we should follow this structure.

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

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

Then you will get the table with book and computer buyers data only.

Some basic example queries:

NB: All example queries are written with respect to this “wp_buyers_table”, You should write your own based on your table data.

wp_buyers_table (database table)

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

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

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

To get 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

There are some Dynamic Placeholders available to write queries. You can use current user id, current date, current date time, current post id, current post title and prefix value dynamically using this shortcode bellow on your queries.

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:

If we want to create a table from our wp-posts database table where the data creates by the current user. Then we can write:

SELECT * FROM wp_posts WHERE post_author = {current_user_id}


When the data creates before the current date. Then we can write:
SELECT * FROM `wp_posts` WHERE post_date < {current_date}

When the data creates by the current user. Then we can write:
SELECT * FROM `wp_posts` WHERE user_id = {current_user_id}

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

Was this article helpful to you?

7 4

How can we help?

Please submit a support ticket if you have any question or pre-sale questions. Our Customer support engineers will answer your query as soon as possible

Open a support ticket