I have the following problem when I want to visualize data in the famous Datatable library using a Store Procedure made in SQLServer, I use the Store Procedure to display my information in the table, the problem is that my data is not being reflected in the table, so this I use PHP and AJAX.
To see the information in my Datatable I use its API to attach secondary rows better known as Child Rows, the following is the AJAX code with which I build my table and its parameters:
/* Formatting function for row details - modify as you need */
function format ( d ) {
// `d` is the original data object for the row
return '<table cellpadding="5" cellspacing="0" border="0" style="padding-left:50px;">'+
'<tr>'+
'<td>Currency</td>'+
'<td>'+d.Currency+'</td>'+
'</tr>'+
'</table>';
}
$(document).ready(function() {
var table = $('#example').DataTable( {
// "processing": true,
"serverSide": true,
"ajax": {
url :"../utileria.php",
type: "POST",
data: {
param: 1,
},
},
columns: [
{
"className": 'details-control',
"orderable": false,
"data": null,
"defaultContent": ''
},
{ "data" : "Order_Buy" },
{ "data" : "Currency" },
],
"order": [[1, 'asc']]
} );
// Add event listener for opening and closing details
$('#example tbody').on('click', 'td.details-control', function () {
var tr = $(this).closest('tr');
var row = table.row( tr );
if ( row.child.isShown() ) {
// This row is already open - close it
row.child.hide();
tr.removeClass('shown');
}
else {
// Open this row
row.child( format(row.data()) ).show();
tr.addClass('shown');
}
} );
} );
It is important to note that my parent row will contain the Order_Buy field and my child row the Currency field
The following is my PHP code where I call my Stored Procedure and the class conectar.php
utileria.php
<?php
header('Content-Type: text/html; charset=utf-8');
$param = $_POST['param'];
switch($param) {
case '1':
$query = array();
include 'conectar.php';
$sql = "{call SPTest(?)}";
$stmt = sqlsrv_query($conn, $sql);
if ( $stmt === false) {
die( print_r( sqlsrv_errors(), true) );
}
while( $row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC) ) {
$record = array(
"F.ORDER" => utf8_encode ($row['Order_Buy']), //Orden de compra
"F.CURRENCY" => utf8_encode ($row['Currency']), //Moneda
);
array_push($query, $record);
}
echo json_encode($query);
sqlsrv_free_stmt( $stmt);
sqlsrv_close($conn);
break;
}
?>
I do not know why my table does not show me data, it could be that I have something wrong in any of my codes.
If it helps to give a better clarity of the question I attach the HTML code with which I generate my Datatables table.
!doctype html>
<html lang="es">
<head>
<title></title>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="libraries/css/bootstrap.min.css" crossorigin="anonymous">
<link rel="stylesheet" href="libraries/css/estilos.css" type="text/css">
<script src="libraries/js/jquery-1.12.3.min.js"></script>
<script src="javascript/Example.js"></script>
<link href="https://cdn.datatables.net/1.10.24/css/jquery.dataTables.min.css" rel="stylesheet"/>
<script src="https://cdn.datatables.net/1.10.24/js/jquery.dataTables.min.js"></script>
<link rel="icon" type="image/png" href="libraries/img/icon.png" />
</head>
<body>
<div class="cuerpo">
<div class="dividir menu">
<div class="centrar-vertical">
<img src="libraries/img/logo.jpg" width="200" height="40" alt="Test" class="img-responsive">
</div>
<div class="alinear-derecha centrar-vertical">
<button id="logout-btn" class="btn btn-default">Cerrar Sesión</button>
</div>
</div>
<div id="tablaDatatable">
<table id="example" class="display" width="100%">
<thead>
<tr>
<th></th>
<th>Order_Buy</th>
<th>Currency</th>
</tr>
</thead>
</table>
</div>
</div>
</body>
</html>
<script type="text/javascript">
$(document).ready(function() {
$('#example').DataTable();
} );
</script>
