DataTable Grid on Button Click with Ajax, MySQL and JQuery

This article shows you how to display a grid item selector with datatable, ajax and jQuery plugin when you click on a button.

Table of Contents

Referring DataTable and JQuery Plugins

First of all, you need to refer JQuery js and datatable js and css.

You may use following CDN (check first before using):

<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/dt/jq-3.6.0/dt-1.13.1/datatables.min.css"/>
 
<script type="text/javascript" src="https://code.jquery.com/jquery-3.5.1.js"></script>

<script type="text/javascript" src="https://cdn.datatables.net/v/dt/jq-3.6.0/dt-1.13.1/datatables.min.js"></script>

Add Button to Show datatable Grid

<button  id="select_item_button">Select an Item... </button>

Adding Click Event

<script>
    $("#select_item_button").click(function(e){
       e.preventDefault();      
       $("#dialog-item-list").dialog('option', 'title', "Select an Item");
       dialog1.dialog( "open" );
    });
</script>

Make sure the ids (select_item_button) equal. Above code try to open a dialog name dialog1. So we need to create it inside jquery function. Note there is another id reference ‘dialog-item-list‘ which is the id of ‘div’ which contains the datatable grid, also title of the dialog define here.

Create JQuery Dialog

<script>
$(function() {
  dialog1 = $("#dialog-items-list").dialog({
      autoOpen: false,     
      width: 500,
      modal: true,
      buttons: {        
        Cancel: function() {
            dialog1.dialog( "close" );
        }
      },      
    });
});

Create Table for DataTable Grid

<div id="dialog-items-list"> 
<table id="item_table">
    <thead>
        <tr style="height:35px;">  
            <th class="table_no" >#</th>                      
            <th class="distributor_column" style="table-layout:fixed; width:150px; overflow:hidden; word-wrap:break-word;">Distributor</th>
            <th class="action_column" style="table-layout:fixed; overflow:hidden; word-wrap:break-word; ">Actions</th>            
        </tr>
    </thead>   
    <tbody class="items">
            
    </tbody>
</table>
</div>

Make sure the id ‘dialog-items-list‘ is same as we defined early on two scripts.

Create Page to get Data from MySql

Here create a page named ‘get_items.php’.

$sql = "SELECT ID,Company FROM `items` ";
$queryItems = mysqli_query($conn, $sql)); // You need to specify database connection. 
	while( $row = mysqli_fetch_row($queryItems) ) { 
		$data[] = $row;
	}	
	$json_data = array(			
			"data" => $data   // return data array. The name should 'data'.
			);
	echo json_encode($json_data)

The data array should stored on the variable ‘data’. Else you need to specify it’s name on ajax call with dataSrc property.

Call the Function Using Ajax

$('#item_table').DataTable( {
        select: true,
        processing: true,       
        ajax: {
            url: "get_items.php",   
            data: {
                "item_id": 10
            },  
            dataSrc : "data",              
        },
        
       'columns': [
		         	{ data: 'ID' },
		         	{ data: 'Company' },
		      	],
    } );

Where ‘ID’ and ‘Company’ are the column names from the database table.

Make sure the ‘item_table‘ is same as the table on datatable. Where url specify the page name which return value from database.

Where

data: {
“item_id”: 10
},
dataSrc : “data”,

which is optional.

‘Data‘ is used to pass additional parameter to the connection page and dataSrc is the return variable name if its name is NOT ‘data‘.