jQuery Datatable with Custom Json format in Php, Mysql part:1.2

This post is 1.2 of a series called Getting Started with Datatable 1.10 .

  1. DataTable demo (Server side) in Php,Mysql and Ajax
  2. jQuery Datatable with Custom Json format in Php, Mysql part:1.2
  3. DataTable (Server side) Custom Column Search
  4. Datatable Scroller (Server Side)
  5. DataTable Responsive (Server side)
  6. Datatable Custom Loader
  7. Datatable Colvis (Column Visibility Customization)
  8. DataTable Table tools
  9. DataTable Custom Search By Tags Input (Server side)
  10. DataTable Search By Datepicker (Server side)
  11. Datatable Bulk Delete (Server Side)
  12. Datatable Page Resize (Server Side)

Today in this post We’ll implement server side jquery datatable integration with custom json format. For server side part We’ll be using php and mysql as a database.

For server part, you can refer my first post of this datatable series. Before proceeding please take a look at the demo.

Markup

So here is our markup where we’ll show the table grid id=”datatable_demo” with four columns : Invoice #, Product Name, Delivery Status, Address.

<table id="datatable_demo" class="display" cellspacing="0" width="100%">
    <thead>
        <tr>
            <th>Invoice #</th>
            <th>Product Name</th>
            <th>Delivery Status</th>
            <th>Address</th>
        </tr>
    </thead>
</table>

Sql and service

Lets create a MySQL table “order_product” with 7 columns and dump some dummy data into that.

CREATE TABLE IF NOT EXISTS `order_product` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'identity',
  `invoice_no` varchar(255) NOT NULL COMMENT 'invoice number',
  `product_name` varchar(255) NOT NULL COMMENT 'Product name',
  `delivery_status` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'true = delivered; 0: not delivered',
  `pin_code` int(11) NOT NULL COMMENT 'zip code ',
  `city` varchar(255) NOT NULL COMMENT 'city',
  `country` varchar(255) NOT NULL COMMENT 'country',
  PRIMARY KEY (`id`)
)

The first column is for unique identity purpose, delivery_status is a boolean field to track product is delivered or not.last three for showing address merging city, country, and pin_code.

we will export that data as a json format to show in datatable. For searching, filtering, pagination or sorting logic please refer my first post DataTable demo (Server side) in Php,Mysql and Ajax Part:1

server-json-data

Initialization

Now we’ll initialize datatable into our frontend. before that don’t forget to include jQuery, datatable assets into your page.

$('#datatable_demo').DataTable( {
    "processing": true,
    "serverSide": true,
    "ajax": {
        "url": "server-json-data.php",
        "type": "POST",
        "dataSrc": "records"
    },
    "columns": [
        { "data": "invoice_no" },
        { "data": "product_name" },
        { "data": "delivery_status" },
        { "data": "pin_code" },
    ],
    "columnDefs": [
        {
            "targets": 2,
            "render": function ( data, type, row ) {
                return data == 1 ? 'Delivered': 'Not delivered';
            }
        },
        {
            "targets": 3,
            "render": function ( data, type, row ) {
                return row["city"] +', ' + row["country"] +', '+data;
            },
          
        },
    ]
});

Here are the step by step initialization:

1) Initialize datatable in #datatable_demo table through $(‘#datatable_demo’).DataTable({ — });

2) Enabling server side with “processing”: true, “serverSide”: true;

3) Defined json service url “server-json-data.php” also defined http method “POST”.

Our datasource attribute will be records, datatable will be fetching grid data from this json attribute. by default datatable set as data.

4) As we have defined four column in the markup definition, So for each column, we have defined json data sources attribute. { “data”: “invoice_no” }

5) Third column (ie, 0,1,2) we are getting boolean value 0 or 1 as delivery_status. we can target each column using columnDefs and modify cell text as per our need.

6) The Same way we have merged city, country and pin_code as address and rendered in 4th column.

data returns particular column information, row returns entire row information.

That’s all for integrating jquery datatable with custom json format. let me know your query or suggestion on the comment section.

DownloadDemo