Adding jQuery DataTable to your page part 3

Execute Join and Union query with dataTable

In previous lesson, we have learned how to load data to table using a ajax call. It’s really a very useful feature. Using it, we can execute our sql code and then can show it directly to users.  Using previous ssp library, we can run simple sql query. But what should we do if we need to join another table with previous table or we need to use union? Suppose, in previous example, we have showed product code in our table. However, we should show product name rather than product code and it’s coming from another table. Now we will solve this problem.

  1. At first, we need to change columns definition for dataTable calling. We just need to update first column’s db from productCode to products.productName as it will come from another table named products.
var columns = [
{"db": "products.productName as productName", "dt": 0, "field": "productName"},
{"db": "orderNumber", "dt": 1, "field": "orderNumber"},
{"db": "quantityOrdered", "dt": 2, "field": "quantityOrdered"},
{"db": "priceEach", "dt": 3, "field": "priceEach"},
{"db": "orderLineNumber", "dt": 4, "field": "orderLineNumber"}
];

Also update your table header for that particular column from Product Code to Product Name. Then change your datatable calling method like below code –

$(document).ready(function() {
    var tableElement = $('#example');
    var exampleTable = tableElement.DataTable( {
        "processing": true,
        "serverSide": true,
        "ajax": {
            "type": "POST",
            "url": "dataTable.php",
            "data": {"table": "orderdetails", "primary_key": "id", "columns": columns,"page":"order_detail"}
        }
    });
});

Here we have added page name for making it reusable for different pages.

2.  For adding this join & union with extra where option, we have customized dataTable’s official ssp library. you can download it from here, We have modified internal queries for making it workable with all the dataTable’s basic feature & with these additional info. Now you can pass information for your join query, extra condition using an extra where clause, group by clause, page name(it will be useful when you need any change for specific pages) & lastly an option for union where clause. You have no need to change anything here. Just download it & replace with previous ssp library.

3. Now we will update dataTable.php for adding these extra options for making it workable. First of all, we will define our join query to $joinQuery variable, then if we need to use any extra where condition, we will define that $extraWhere & lastly we will finalize our $unionWhere with $group_by. If we have no need to use any of these options then declare these variable with only an empty string like below $group_by variable.

Finally we will call that simple method of SSP library with all of these additional parameter. Moreover, we have used an additional parameter for specific page information. It’s useful when we need to add dataTable for different pages with different parameter.

$joinQuery = "FROM orderdetails JOIN products on (products.productCode = orderdetails.productCode)";
$extraWhere = "orderNumber = '10100'";
$unionWhere = "orderNumber != '10100'";
$group_by = "";

echo json_encode(SSP::simple( $_POST, $sql_details, $table, $primaryKey, $columns, $joinQuery, $extraWhere, $group_by , $_POST['page'], $unionWhere));
DataTable Example

DataTable Example

At last, you will get your desired result(product name from another table) like this below image using a join & union query.

Like to work with different type of web based application, have completed my graduation from RUET & I am a Zend Certified PHP Engineer and also a Certified Scrum Master, like agile methodologies for development. I am working as a Web Developer for 4+ years, specially experienced in Codeigniter and Laravel.

Leave a Reply

Your email address will not be published. Required fields are marked *