Child Row with database SQL Server

Romuald · January 2020

Hello,

I would like to do : https://datatables.net/examples/api/row_details.html

Parent row : Nom - Adresse - CodePostal - Ville - Pays
Children row : Telephone - Telecopie - SiteInternet - SiteSupport

The data is stored in the table name : 'Para_Editeur'

I work with database SQL Server.

How I can add icon "+" on the rows of my table ?
How I search the data of my row parent ?

My script 'editeur.js' (Datatable parent):

/* 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>Téléphone :</td>'+
            '<td>'+d.Telephone+'</td>'+
        '</tr>'+
        '<tr>'+
            '<td>Télécopie :</td>'+
            '<td>'+d.Telecopie+'</td>'+
        '</tr>'+
        '<tr>'+
            '<td>Site Internet :</td>'+
            '<td>'+d.SiteInternet+'</td>'+
        '</tr>'+
        '<tr>'+
            '<td>Site Support :</td>'+
            '<td>'+d.SiteSupport+'</td>'+
        '</tr>'+     
    '</table>';
}
 
$(document).ready(function() {
    var table = $('#table_editeur').DataTable( {
        "bProcessing": true,
        "serverSide": true,
        "ajax":{
            url :"./response-displayrow_editeur.php", // json datasource
            type: "post",  // type of method  ,GET/POST/DELETE
            error: function(){
                $("#table_editeur_processing").css("display","none");
              }            
          },        
        "columns": [
            {
                "className":      'details-control',
                "orderable":      false,
                "data":           null,
                "defaultContent": '',
            },
            { "data": "Nom" },
            { "data": "Adresse" },
            { "data": "CodePostal" },
            { "data": "Ville" },
            { "data": "Pays" },
            { "data": "Telephone" },
            { "data": "Telecopie" },
            { "data": "SiteInternet" },
            { "data": "SiteSupport" },
        ],
        "order": [[1, 'asc']]
    } );
     
    // Add event listener for opening and closing details
    $('#table_editeur tbody').on('click', 'td.details-control', function () {        
        var tr = $(this).closest('tr');
        // var tdi = tr.find("i.fa");
        var row = table.row( tr );
 
        if ( row.child.isShown() ) {
            // This row is already open - close it
            row.child.hide();
            // tr.find('svg').attr('data-icon', 'plus-circle');
            tr.removeClass('shown');            
            // tdi.first().removeClass('fa-minus-square');
            // tdi.first().addClass('fa-plus-square');
        }
        else {
            // Open this row
            row.child( format(row.data()) ).show();
            // row.child(format(tr.data('ACA'), tr.data('ACA'))).show();
            // tr.find('svg').attr('data-icon', 'minus-circle');
            tr.addClass('shown');
            // tdi.first().removeClass('fa-plus-square');
            // tdi.first().addClass('fa-minus-square');
        }
    } );    
} );

My script 'response-displayrow_editeur.php' (Datatable parent):

```
<?php
//include connection file
include_once(".\db_connection.php");

// getting total number records without any search
$sql = "SELECT dbo.Para_Editeur.Nom,dbo.Para_Editeur.Telephone,dbo.Para_Editeur.Telecopie,dbo.Para_Editeur.SiteInternet,dbo.Para_Editeur.SiteSupport FROM dbo.Para_Editeur";
$stmt = sqlsrv_query( $conn, $sql);

if( $stmt === false ) {
    die( print_r( sqlsrv_errors(), true));
    }

//iterate on results row and create new index array of data
while( $obj = sqlsrv_fetch_object( $stmt)) {
     $data[] = $obj;
}   

echo json_encode($json_data);  // send data as json format

<?php > ``` ?>

Thank you for help

kthorngren · January 2020

How I can add icon "+" on the rows of my table ?

Does that not work with the code you have?

EDIT: You may need to create a CSS similar to what is shown in the CSS tab of the example you linked.

How I search the data of my row parent ?

You are using server side processing and it looks like the data you want to show in the Child Details is also defined in columns.data. You can use columns.visible to hide those columns and be able to search them using server side process.

If this doesn't help then please provide more details of the issues. Better yet please provide a link to your page or a test case replicating the issue so we can take a look.

Kevin

Romuald · January 2020

Hello,

I have a database SQL server.

I have a table name : 'Para_Editeur'.
In this table, I have the fields : ID_Para_Editeur - Nom - Adresse - CodePostal - Ville - Pays - Telephone - Telecopie - SiteInternet - SiteSupport.

In my datatable, I show the fields in parent rows : ID_Para_Editeur - Nom - Adresse - CodePostal - Ville

I would like show in child rows the fields : Telephone - Telecopie - SiteInternet - SiteSupport.

I based myself on the example : https://datatables.net/examples/server_side/row_details.html

But it's no work !!!

My file ccs 'rowdetail_editeur' for show "+" and "-"

td.details-control {
    background: url('../resources/details_open.png') no-repeat center center;
    cursor: pointer;
}
tr.details td.details-control {
    background: url('../resources/details_close.png') no-repeat center center;
}

My file php 'Editeur'

<!doctype html>
<html lang="fr">
    <head>
        <!-- Required meta tags -->
        <?php include_once('./header.php'); ?>
        <meta charset="utf-8">
        <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
        <!-- <title>Catalogue Matmut</title> -->
    <!-- class="table table-striped" -->
    <body>
    <h1><img src="./img/Editeur.png" width="50" height="50" alt="Editeur" /> Editeur</h1>
    <hr color="black">
    <table id="table_editeur" class="display nowrap" width="100%">
        <thead>
            <tr>                
                <th>ID</th>                
                <th>Nom</th>
                <th>Adresse</th>
                <th>Code Postal</th>
                <th>Ville</th>
                <th>Pays</th>                                   
            </tr>
        </thead>
    <tbody>
                
    <?php
        $sql = "SELECT dbo.Para_Editeur.ID_Para_Editeur,dbo.Para_Editeur.Nom,dbo.Para_Editeur.Adresse,dbo.Para_Editeur.CodePostal,dbo.Para_Editeur.Ville,dbo.Para_Editeur.Pays,dbo.Para_Editeur.Telephone,dbo.Para_Editeur.Telecopie,dbo.Para_Editeur.SiteInternet,dbo.Para_Editeur.SiteSupport FROM dbo.Para_Editeur ORDER BY Nom ASC";
        $stmt = sqlsrv_query( $conn, $sql);
        
        if( $stmt === false ) {
            die( print_r( sqlsrv_errors(), true));
            }

        while( $obj = sqlsrv_fetch_object( $stmt)) {                                 
            
    ?>
    <!--✔-->
        <tr>
            <td><?php echo utf8_encode($obj->ID_Para_Editeur); ?></td>            
            <td><?php echo utf8_encode($obj->Nom); ?></td>
            <td><?php echo utf8_encode($obj->Adresse); ?></td>
            <td><?php echo utf8_encode($obj->CodePostal); ?></td>
            <td><?php echo utf8_encode($obj->Ville); ?></td>
            <td><?php echo utf8_encode($obj->Pays); ?></td>           
        </tr>                    
        <?php                        
            }                           
        ?>
    </tbody>
</table>   
        <script type="text/javascript" src="jQuery-3.3.1/jquery-3.3.1.min.js"></script>
        <script type="text/javascript" src="JSZip-2.5.0/jszip.min.js"></script>
        <script type="text/javascript" src="pdfmake-0.1.36/pdfmake.min.js"></script>
        <script type="text/javascript" src="pdfmake-0.1.36/vfs_fonts.js"></script>        
        <script type="text/javascript" src="DataTables-1.10.20/js/jquery.dataTables.min.js"></script>      
        <script type="text/javascript" src="Buttons-1.6.1/js/buttons.html5.min.js"></script>
        <script type="text/javascript" src="Buttons-1.6.1/js/buttons.print.min.js"></script>
        <script type="text/javascript" src="./js/editeur_test.js"></script>   
  </body>
</html>

My file javascript "editeur_test":

function format ( d ) {
    return 'Téléphone : '+d.Telephone+' '+d.Telecopie+'<br>'+
        'Site Internet : '+d.SiteInternet+'<br>'+
        'Texte libre...';
}

$(document).ready(function() {
    otable = $('#table_editeur').DataTable( {
        dom: 'Bfrtip',
        "buttons": [
            {extend: 'copy',text: 'Copier'},
            {extend: 'csv',text: 'Export CSV', filename: 'catalogue Applicatif Matmut'},
            {extend: 'excel',text: 'Export Excel',title: 'Catalogue Applicatif Matmut', filename: 'catalogue Applicatif Matmut'},
            {extend: 'pdfHtml5',text: 'Export PDF',title: 'Catalogue Applicatif Matmut', filename: 'catalogue Applicatif Matmut' ,exportOptions: {orthogonal: "ExportPDF"},         
            customize: function (doc) {                           
              //var iColumns = $('tableau').length;              
              var iColumns = 5                            
              //alert(iColumns);              
              //var rowCount = document.getElementById("tableau").rows.length;
              //var rowCount = $('#tableau').DataTable().rows().count() + 1;              
              var table = $('#table_editeur').DataTable();
              var info = table.page.info();
              rowCount = info.recordsDisplay + 1;
              alert(rowCount);
              for (i = 0; i < rowCount; i++) { 
                doc.content[1].table.body[i][iColumns - 1].alignment = 'center';
                doc.content[1].table.body[i][iColumns - 2].alignment = 'center';                
              };
              
              doc.footer=(function(page, pages) {
                return {
                    columns: [
                      {
                        alignment: 'right',
                        text: [,
                        'Page ',
                        { text: page.toString(), italics: true },
                        ' sur ',
                        { text: pages.toString(), italics: true }
                        ]
                      }
                    ],
                margin: [40, 0]
                }
            });              
            }},
            {extend: 'print',text: 'Imprimer', title: 'Catalogue Applicatif Matmut'}            
        ],
        "processing": true,
        "serverSide": true,
        "ajax": "./response-displayrow_editeur.php",  
        "columns": [
            {
                "className":      'details-control',
                "orderable":      false,
                "data":           null,
                "defaultContent": "",
            },
            { "data": "Nom" },
            { "data": "Adresse" },
            { "data": "CodePostal" },
            { "data": "Ville" },
            { "data": "Pays" },
            { "data": "Telephone" },
            { "data": "Telecopie" },
            { "data": "SiteInternet" },
            { "data": "SiteSupport" },
        ],
        "Nom": [[1, 'asc']],
        "paging" : true
    } );
     
    var detailRows = [];
 
    $('#table_editeur tbody').on( 'click', 'tr td.details-control', function () {
        var tr = $(this).closest('tr');
        var row = dt.row( tr );
        var idx = $.inArray( tr.attr('ID_Para_Editeur'), detailRows );
 
        if ( row.child.isShown() ) {
            tr.removeClass( 'details' );
            row.child.hide();
 
            // Remove from the 'open' array
            detailRows.splice( idx, 1 );
        }
        else {
            tr.addClass( 'details' );
            row.child( format( row.data() ) ).show();
 
            // Add to the 'open' array
            if ( idx === -1 ) {
                detailRows.push( tr.attr('ID_Para_Editeur') );
            }
        }
    } );
    dt.on( 'draw', function () {
        $.each( detailRows, function ( i, id ) {
            $('#'+ID_Para_Editeur+' td.details-control').trigger( 'click' );
        } );
    } );
} );

My file php for the child rows 'response-displayrow_editeur':
```
<?php
// DB table to use
$table = 'dbo.Para_Editeur';
// Table's primary key
$primaryKey = 'dbo.ID_Para_Editeur';

$columns = array(
array(
'db' => 'dbo.ID_Para_Editeur',
'dt' => 'DT_RowId',
'formatter' => function( $d, $row ) {
return 'row_'.$d;
}
),
array( 'db' => 'dbo.Nom', 'dt' => 'Nom' ),
array( 'db' => 'dbo.Adresse', 'dt' => 'Adresse' ),
array( 'db' => 'dbo.CodePostal', 'dt' => 'CodePostal' ),
array( 'db' => 'dbo.Ville', 'dt' => 'Ville' ),
array( 'db' => 'dbo.Pays', 'dt' => 'Pays' ),
array( 'db' => 'dbo.Telephone', 'dt' => 'Telephone' ),
array( 'db' => 'dbo.Telecopie', 'dt' => 'Telecopie' ),
array( 'db' => 'dbo.SiteInternet', 'dt' => 'SiteInternet' ),
array( 'db' => 'dbo.SiteSupport', 'dt' => 'SiteSupport' )
}
)
);

$sql_details = array(
'user' => 'User_Web',
'pass' => 'P@ssword',
'db' => 'Catalogue',
'host' => 'srvwds01'
);

// require( 'ssp.class.php' );

// echo json_encode(
// SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
// );

<?php > ``` ?>

I don't know how I could make an example online with a database ?

Thank you for your help

kthorngren · January 2020

When you say it doesn't work what is not working?

Do you get errors?

Do you see the plus and minus buttons?

In my datatable, I show the fields in parent rows : ID_Para_Editeur - Nom - Adresse - CodePostal - Ville

Looks like your Datatb le also shows Telephone - Telecopie - SiteInternet - SiteSupport.. Do these appear?

You've posted a lot of code which is difficult to just look through to debug the issue. You can take an example of your data and, instead of using ajax to fetch it, use data to supply the data to Datatables. Similar to this example:
https://datatables.net/examples/data_sources/js_array.html

Kevin

kthorngren · January 2020

The first problem I see is you have this in your table:

        <thead>
            <tr>               
                <th>ID</th>               
                <th>Nom</th>
                <th>Adresse</th>
                <th>Code Postal</th>
                <th>Ville</th>
                <th>Pays</th>                                  
            </tr>
        </thead>

But are defining this with columns.data:

        "columns": [
            {
                "className":      'details-control',
                "orderable":      false,
                "data":           null,
                "defaultContent": "",
            },
            { "data": "Nom" },
            { "data": "Adresse" },
            { "data": "CodePostal" },
            { "data": "Ville" },
            { "data": "Pays" },
            { "data": "Telephone" },
            { "data": "Telecopie" },
            { "data": "SiteInternet" },
            { "data": "SiteSupport" },
        ],

If you look in your browser's console you are probably seeing an error, something like this:

Uncaught TypeError: Cannot read property 'style' of undefined

If you don't want to display the last four columns in Telephone - Telecopie - SiteInternet - SiteSupport in your table then remove them from columns.data. You can still return that data from the server and use it in the child rows.

Fix this and keep an eye on the browser's console for errors.

Kevin

Romuald · January 2020

Hello,

Yes I see this error in my console : Uncaught TypeError: Cannot read property 'style' of undefined.

Romuald · January 2020

Hello,

Now I see the symbol "+" and "-".

But I'm not see the data...(see picture attached)

Thank you for your help.

kthorngren · January 2020

Does your JSON response data contain these columns Telephone, Telecopie, SiteInternet, SiteSupport?

In the format function you can use console.log( d ); to see what is avaialb ein the row data.

Kevin

Romuald · January 2020

Hello Kévin

After a few tests, this no work !!!

I'm not undestand !!!

My table :
* ID_Para_Editeur : Primary Key
* Nom : Parent row
* Adresse : Parent row
* CodePostal : Parent row
* Ville : Parent row
* Pays : Parent row
* Telephone : Child row
* Telecopie : Child row
* SiteInternet : Child row
* SiteSupport : Child row

1 - How I can show my image "+" and "-" (See picture) ?

My file 'rowdetail_editeur.css' :

td.details-control {
    background: url('../_Test/img/details_open.png') no-repeat center center;
    cursor: pointer;
}
tr.details td.details-control {
    background: url('../_Test/img/details_close.png') no-repeat center center;
}

My file 'editeur.php':

<!doctype html>
<html lang="fr">
    <head>
    <!-- Required meta tags -->
    <?php include_once('./header.php'); ?>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
    <!-- <title>Catalogue Matmut</title> -->
<link rel="stylesheet" type="text/css" href="./rowdetail_editeur.css"/>
    </head>    
    <body>
    <h1><img src="./img/Accueil.png" width="50" height="50" alt="Accueil" /> Editeur (New)</h1>
    <hr color="black">
    <table id="table" class="display nowrap" width="100%">
        <thead>
            <tr>    
                <th>Nom</th>
                <th>Adresse</th>
                <th>CodePostal</th>                        
                <th>Ville</th>                                              
                <th>Pays</th>
            </tr>
        </thead>
    <tbody>
                
    <?php
        $sql = "SELECT dbo.Para_Editeur.ID_Para_Editeur,dbo.Para_Editeur.Nom,dbo.Para_Editeur.Adresse,dbo.Para_Editeur.CodePostal,dbo.Para_Editeur.Ville,dbo.Para_Editeur.Pays FROM dbo.Para_Editeur ORDER BY Nom ASC";
        $stmt = sqlsrv_query( $conn, $sql);
        
        if( $stmt === false ) {
            die( print_r( sqlsrv_errors(), true));
            }

        while( $obj = sqlsrv_fetch_object( $stmt)) {
    ?>
    
    <tr>        
        <td><?php echo utf8_encode($obj->Nom); ?></td>
        <td><?php echo utf8_encode($obj->Adresse); ?></td>                      
        <td><?php echo utf8_encode($obj->CodePostal); ?></td>                            
        <td><?php echo utf8_encode($obj->Ville); ?></td>                            
        <td><?php echo utf8_encode($obj->Pays); ?></td>                                    
    </tr>                    
    <?php
        }                           
    ?>
        </tbody>
    </table>     
     <script type="text/javascript" src="jQuery-3.3.1/jquery-3.3.1.min.js"></script>
        <script type="text/javascript" src="JSZip-2.5.0/jszip.min.js"></script>
        <script type="text/javascript" src="pdfmake-0.1.36/pdfmake.min.js"></script>
        <script type="text/javascript" src="pdfmake-0.1.36/vfs_fonts.js"></script>        
        <script type="text/javascript" src="DataTables-1.10.20/js/jquery.dataTables.min.js"></script>
        <script type="text/javascript" src="AutoFill-2.3.4/js/dataTables.autoFill.min.js"></script>
               <script type="text/javascript" src="Buttons-1.6.1/js/dataTables.buttons.min.js"></script>
        <script type="text/javascript" src="Buttons-1.6.1/js/buttons.flash.min.js"></script>
        <script type="text/javascript" src="Buttons-1.6.1/js/buttons.html5.min.js"></script>
        <script type="text/javascript" src="Buttons-1.6.1/js/buttons.print.min.js"></script>        

        <script type="text/javascript" src="./js/editeur.js"></script>   
  </body>
</html>

My file 'response-displayrow_editeur.php':

// DB table to use
$table = 'dbo.Para_Editeur';
 
// Table's primary key
$primaryKey = 'dbo.ID_Para_Editeur';
 
// Array of database columns which should be read and sent back to DataTables.
// The `db` parameter represents the column name in the database, while the `dt`
// parameter represents the DataTables column identifier - in this case object
// parameter names
$columns = array(
    array(
        'db' => 'dbo.ID_Para_Editeur',
        'dt' => 'DT_RowId',
        'formatter' => function( $d, $row ) {
            // Technically a DOM id cannot start with an integer, so we prefix
            // a string. This can also be useful if you have multiple tables
            // to ensure that the id is unique with a different prefix
            return 'row_'.$d;
        }
    ),
    array( 'db' => 'dbo.ID_Para_Editeur', 'dt' => 'ID_Para_Editeur' ),
    array( 'db' => 'dbo.Nom', 'dt' => 'Nom' ),
    array( 'db' => 'dbo.Adresse', 'dt' => 'Adresse' ),
    array( 'db' => 'dbo.CodePostal', 'dt' => 'CodePostal' ),
    array( 'db' => 'dbo.Ville', 'dt' => 'Ville' ),
    array( 'db' => 'dbo.Pays', 'dt' => 'Pays' ),
    array( 'db' => 'dbo.Telephone', 'dt' => 'Telephone' ),
    array( 'db' => 'dbo.Telecopie', 'dt' => 'Telecopie' ),
    array( 'db' => 'dbo.SiteInternet', 'dt' => 'SiteInternet' ),
    array( 'db' => 'dbo.SiteSupport', 'dt' => 'SiteSupport' ),    
    )
);
 
$sql_details = array(
    'user' => '*******',
    'pass' => ********',
    'db'   => '*******',
    'host' => '*******'
);
 
 
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * If you just want to use the basic configuration for DataTables with PHP
 * server-side, there is no need to edit below this line.
 */
 
require( 'ssp.class.php' );
 
echo json_encode(
     SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
);

Thank you very much

Romuald

kthorngren · January 2020

How I can show my image "+" and "-" (See picture) ?

Is this the correct path to the PNG files `background: url('../_Test/img/details_open.png') '?

Do you get 404 errors in the browser's developer tools when trying to load these files?

Kevin

Romuald · January 2020

Kevin

No, I don't get 404 errors in the browser's developer tools when trying to load these files.

I have a other error...(see picture)

Thank you for help

kthorngren · January 2020

There are lots of threads with that error, for example:
https://datatables.net/forums/discussion/comment/158655/#Comment_158655

It is not the result of loading the PNG files. It is due to a mismatch between your HTML table and Datatables config. If you are unable to solve the issue with what you can find in the format then we will need a link to your page or a test case replicating the issue to help debug the problem.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

Romuald · January 2020

Kevin,

I advanced a little on my problem... (see picture)

I create the file Ajax manual.

My database in SQL Server...

How i can find the datain my database without writing it to a text file (JSON) ?

Thank you very much

Romuald · January 2020

Kévin

I create file JSON:

$sql = "SELECT * FROM dbo.Para_Editeur ORDER BY Nom ASC";
$stmt = sqlsrv_query( $conn, $sql);
        
echo '<p>$stmt</p>'; 
        
if( $stmt === false ) {
    die( print_r( sqlsrv_errors(), true));
}

$data = array();
while( $row = sqlsrv_fetch_object( $stmt)) {
$data[]=$row;
}       
echo '<p>Requête terminée</p>'; 
        
$fp = fopen('results.json', 'w');
//fwrite($fp, json_encode($data, JSON_FORCE_OBJECT)); //Ok
fwrite($fp, json_encode(["data" => $data], JSON_FORCE_OBJECT)); //Test
        
//fwrite($fp, json_encode(array("response"=>$data), JSON_PRETTY_PRINT));
fclose($fp);

{"data":{"0":{"ID_Para_Editeur":16,"Nom":"ACA","Adresse":"69 rue Monceau","CodePostal":"75008","Ville":"Paris","Pays":"France","Telephone":"+33 1 53 53 80 80","Telecopie":"+33 1 53 53 80 10","SiteInternet":"www.aca.fr","SiteSupport":""},"1":{"ID_Para_Editeur":12,"Nom":"Actuaris","Adresse":"13\/15 boulevard de la Madeleine ","CodePostal":"75001","Ville":"Paris","Pays":"France","Telephone":"+33 1 56 89 07 70","Telecopie":"+33 1 56 89 07 71","SiteInternet":"http:\/\/www.actuaris.fr","SiteSupport":""}

And in my file JS:

"ajax": {
                "url": "./ajax/data/results.json",
                "dataSrc": "data",

Why I'm not see the data in my datatable and no error in console ?

Thank you for help

Romuald · January 2020

Kévin

Why I'm not see the data in my datatable ?

My file JSON :

{"data":{"0":{"ID_Para_Editeur":16,"Nom":"ACA","Adresse":"69 rue Monceau","CodePostal":"75008","Ville":"Paris","Pays":"France","Telephone":"+33 1 53 53 80 80","Telecopie":"+33 1 53 53 80 10","SiteInternet":"www.aca.fr","SiteSupport":""},"1":{"ID_Para_Editeur":12,"Nom":"Actuaris","Adresse":"13\/15 boulevard de la Madeleine ","CodePostal":"75001","Ville":"Paris","Pays":"France","Telephone":"+33 1 56 89 07 70","Telecopie":"+33 1 56 89 07 71","SiteInternet":"http:\/\/www.actuaris.fr","SiteSupport":""}

My file generate the file JSON:

$sql = "SELECT * FROM dbo.Para_Editeur ORDER BY Nom ASC";
$stmt = sqlsrv_query( $conn, $sql);

if( $stmt === false ) {
    die( print_r( sqlsrv_errors(), true));
}

$data = array();
while( $row = sqlsrv_fetch_object( $stmt)) {
    $data[]=$row;
}       
        
$fp = fopen('results.json', 'w');
//fwrite($fp, json_encode($data, JSON_FORCE_OBJECT)); //Ok
fwrite($fp, json_encode(["data" => $data], JSON_FORCE_OBJECT)); //Test
        
fclose($fp);

My file JS for read the file JSON:

"ajax": {
    "url": "./ajax/data/results.json",
    "dataSrc": "data",

Thank you for help

kthorngren · January 2020

This is your JSON:

{
    "data": {
        "0": {
            "ID_Para_Editeur": 16,
            "Nom": "ACA",
            "Adresse": "69 rue Monceau",
            "CodePostal": "75008",
            "Ville": "Paris",
            "Pays": "France",
            "Telephone": "+33 1 53 53 80 80",
            "Telecopie": "+33 1 53 53 80 10",
            "SiteInternet": "www.aca.fr",
            "SiteSupport": ""
        },
        "1": {
            "ID_Para_Editeur": 12,
            "Nom": "Actuaris",
            "Adresse": "13\/15 boulevard de la Madeleine ",
            "CodePostal": "75001",
            "Ville": "Paris",
            "Pays": "France",
            "Telephone": "+33 1 56 89 07 70",
            "Telecopie": "+33 1 56 89 07 71",
            "SiteInternet": "http:\/\/www.actuaris.fr",
            "SiteSupport": ""
        }
...

Even though this structure is a valid JSON structure its not one that is supported by Datatables. Datatables expects an array of rows as d3scribed in the data manual. The JSON needs to look more like this:

{
    "data": [
        {
            "ID_Para_Editeur": 16,
            "Nom": "ACA",
            "Adresse": "69 rue Monceau",
            "CodePostal": "75008",
            "Ville": "Paris",
            "Pays": "France",
            "Telephone": "+33 1 53 53 80 80",
            "Telecopie": "+33 1 53 53 80 10",
            "SiteInternet": "www.aca.fr",
            "SiteSupport": ""
        },
        {
            "ID_Para_Editeur": 12,
            "Nom": "Actuaris",
            "Adresse": "13\/15 boulevard de la Madeleine ",
            "CodePostal": "75001",
            "Ville": "Paris",
            "Pays": "France",
            "Telephone": "+33 1 56 89 07 70",
            "Telecopie": "+33 1 56 89 07 71",
            "SiteInternet": "http:\/\/www.actuaris.fr",
            "SiteSupport": ""
        }
     ]
}

Kevin

Romuald · January 2020

Kevin,

I succeeded to show the data...

My table count 136 rows. (Database SQL Server)

My process may not be the best for use datatable :

I create a file JSON
I load the file JSON

My file write JSON:

try {
                
        $sql = "SELECT * FROM dbo.Para_Editeur ORDER BY Nom";
        $stmt = sqlsrv_query( $conn, $sql);
                
        if( $stmt === false ) {
            die( print_r( sqlsrv_errors(), true));
        }
                
        $data = array();
        while( $row = sqlsrv_fetch_object( $stmt)) {
            $data[]=$row;               
        }       
        
        $fp = fopen('./ajax/data/results.json', 'w');
        fwrite($fp, json_encode(["data" => $data], JSON_PRETTY_PRINT));     
        fclose($fp);
        //echo '<p>Exportation terminée</p>'; 
    }
    catch(Exception $e) {
        die('Erreur : '.$e->getMessage());
    }

My file load JSON:

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>Téléphone:</td>'+
          '<td>'+d.Telephone+'</td>'+
      '</tr>'+
      '<tr>'+
          '<td>Télécopie:</td>'+
          '<td>'+d.Telecopie+'</td>'+
      '</tr>'+      
      '<tr>'+
          '<td>Site internet:</td>'+
          '<td>'+d.SiteInternet+'</td>'+
      '</tr>'+  
      '<tr>'+
          '<td>Site support:</td>'+
          '<td>'+d.SiteSupport+'</td>'+
      '</tr>'+  
  '</table>';
}

$(document).ready(function() {  
    var dt = $('#table_editeur').DataTable( {  
        dom: 'Bfrtip',
            "buttons": [
            {extend: 'copy',text: 'Copier'},
            {extend: 'csv',text: 'Export CSV', filename: 'Editeur'},
            {extend: 'excel',text: 'Export Excel',title: 'Editeur', filename: 'Editeur'},
            {extend: 'pdfHtml5',text: 'Export PDF',title: 'Editeur', filename: 'Editeur' ,exportOptions: {orthogonal: "ExportPDF"},customize: function (doc) {
                //alert("Test...");
                //var iColumns = $('tableau').length;              
                var iColumns = 5                            
                //alert(iColumns);              
                //var rowCount = document.getElementById("tableau").rows.length;
                //var rowCount = $('#tableau').DataTable().rows().count() + 1;              
                var table_editeur = $('#table_editeur').DataTable();
                var info = table_editeur.page.info();
                rowCount = info.recordsDisplay + 1;
                //alert(rowCount);
                // for (i = 0; i < rowCount; i++) { 
                //   doc.content[1].table_editeur.body[i][iColumns - 1].alignment = 'center';
                //   doc.content[1].table_editeur.body[i][iColumns - 2].alignment = 'center';                
                // };              
                doc.footer=(function(page, pages) {
                    return {
                        columns: [
                        {
                            alignment: 'right',
                            text: [,
                            'Page ',
                            { text: page.toString(), italics: true },
                            ' sur ',
                            { text: pages.toString(), italics: true }
                            ]
                        }
                        ],
                    margin: [40, 0]
                    }
                });              
            }
            },
            {extend: 'print',text: 'Imprimer', title: 'Editeur'}            
            ],
            "ajax": {
                "url": "./ajax/data/results.json",
                "dataSrc": "data",
            },
            "columns": [{
                    "className":      'details-control',
                    "orderable":      false,
                    "data":           null,
                    "defaultContent": ""
                },                         
                {"data": "Nom"},
                {"data": "Adresse"},
                {"data": "CodePostal"},
                {"data": "Ville"},
                {"data": "Pays"},               
            ],        
            "paging" : true,
            "searching" : true,
            "info" : true,
            "language": {
                buttons: {
                    copyTitle: 'Ajouté au presse-papiers',                
                    copySuccess: {
                        _: '%d lignes copiées',
                        1: '1 ligne copiée'
                    }},
                processing:     "Traitement en cours...",
                search:         "Rechercher&nbsp;:",
                lengthMenu:     "Afficher _MENU_ &eacute;l&eacute;ment(s)",
                info:           "Affichage de _TOTAL_ &eacute;l&eacute;ment(s)",
                infoEmpty:      "Affichage de l'&eacute;lement 0 &agrave; 0 sur 0 &eacute;l&eacute;ment",
                infoFiltered:   "(filtr&eacute; de _MAX_ &eacute;l&eacute;ment(s) au total)",
                infoPostFix:    "",
                loadingRecords: "Chargement en cours...",
                zeroRecords:    "Aucun &eacute;l&eacute;ment &agrave; afficher",
                emptyTable:     "Aucune donnée disponible dans le tableau",        
                paginate: {
                    first:      "Premier",
                    previous:   "Pr&eacute;c&eacute;dent",
                    next:       "Suivant",
                    last:       "Dernier"
                    },
                aria: {
                    sortAscending:  ": activer pour trier la colonne par ordre croissant",
                    sortDescending: ": activer pour trier la colonne par ordre décroissant"
                }
            }
    } );                                                         
    
    $('#myInputTextField').on( 'keyup', function () {
        dt.search( this.value ).draw();
    } );

    // Add event listener for opening and closing details
    $('#table_editeur tbody').on('click', 'td.details-control', function () {
        var tr = $(this).closest('tr');
        var row = dt.row( tr );

        if ( row.child.isShown() ) {
            // This row is already open - close it
            row.child.hide();
            // tr.find('svg').attr('data-icon', 'plus-circle'); 
            tr.removeClass('shown');
        }
        else {
            // Open this row            
            row.child( format(row.data()) ).show();
            // tr.find('svg').attr('data-icon', 'minus-circle');
            tr.addClass('shown');
        }
    } );
} );

How I can stored the rows in var without write the file JSON ?

Thank you very much

colin · January 2020

If I understand correctly, you would need the Ajax to point to a web service that returns that structure, rather than going via the file.

Colin

Romuald · January 2020

Colin,

I have a database SQL Server.

I would like to show the fields with columns parents and children:

Parents rows : Nom - Adresse - Code Postal - Ville
Children rows : Téléphone - Télécopie - Site Internet - Site Support

For the rows children, I create a file JSON.

How I can tu put the data in var ?

Sorry for my bad english

Thank you

Child Row with database SQL Server

Child Row with database SQL Server

Answers

Howdy, Stranger!

Categories

DataTables

Child Row with database SQL Server

Child Row with database SQL Server

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables