What is the best method for displaying the total in datatable?

Question

What is the best method for displaying the total in datatable?

I am encountering a slight issue while working on creating a table using Datatable and Bootstrap 4. Everything seems to be functioning well, but I have hit a snag. I am attempting to add a totalizer so that the sum of one or more columns is displayed at the end of the table. The code calculates the total correctly; however, it displays the total in the header rather than the footer. Any idea why this might be happening? Thank you.

                 echo '<div class="card-body"><table id="html5-extension" class="table table-hover table-striped " style="width:100%">';
                 echo '<thead class=""><tr role="row">
                 <th >'.$palabra['numeroor'].'</th>
                 <th >'.$palabra['serieano'].'</th>
                 <th >'.$palabra['taller'].'</th>
                 <th >'.$palabra['fecha'].'</th>
                 <th >'.$palabra['matricula'].'</th>
                 <th >'.$palabra['vehiculo'].'</th>
                 <th >'.$palabra['cristalroto'].'</th>
                 <th >'.$palabra['compania'].'</th>
                 <th ></th>
                 <th></th>
                   </tr> </thead>';
                  echo ' <tfoot>
                          <td></td>
                          <td></td>
                          <td></td>
                          <td></td>
                          <td></td>
                          <td></td>
                          <td></td>
                          <td></td>
                          <td></td>
                          <td></td>
                      </tfoot>
                    <tbody>';
                     echo '<tr role="row">
                         <td scope="row">'.$lisc['or_interna'].'</td>
                         <td scope="row">'.$lisc['serie_or'].'/'.$lisc['ano_or'].'</td>
                         <td >'.$datostaller->nombre.'</td>
                         <td >'.cfecha($lisc['fecha_entrada']).'</td>
                         <td >'.$lisc['matricula'].'</td>
                         <td >'.$marca.$espacio.$modelo.'</td>
                         <td >'.$arraydano['nombre'].'</td>
                         <td >'.$cia->nombre_comercial.'</td>
                     ';

my js code:

<script>
 //#html5-extension

 $(document).ready(function () {
       $('#html5-extension tfoot td').each(function () {
           var title = $(this).text();
           $(this).html('<input type="text" placeholder="Filter.." />');
       });

       var table = $('#html5-extension').DataTable({
           "dom": 'B<"float-left"i><"float-right"f>t<"float-left"l><"float-right"p><"clearfix">',
           "responsive": false,
           ...
   });
</script>

I edit. I have the following code so that the search engines appear at the top (instead of at the bottom). I know that's why the total comes out at the top, what I don't know is how to fix the total at the bottom and the search engines at the top

    <style>
        #html5-extension tfoot input {
            width: 100% !important;
        }

        #html5-extension tfoot {
            display: table-header-group !important;
        }
    </style>

javascript bootstrap-4 datatable

Answer 1

Answer №1

If you're looking for a good starting point, I recommend checking out this example. It does a great job of placing filters in the heading correctly.

The example creates a second header row to keep sorting controls separate from filtering controls. By using the orderCellsTop: true option, DataTables knows to use the first header row for sorting.

(Note that the example also utilizes FixedHeader, which may not be necessary for your project, so feel free to ignore that part.)

This setup allows you to place your totals in the footer, which doesn't interfere with the filter controls' layout.

You can then handle your totals logic in the footerCallback section of the DataTable, similar to what's shown in this example, assuming you're already using something similar in your current DataTable implementation.

For an integrated demonstration, here's a sample runnable demo combining all these elements into one DataTable. The data used is from the DataTables website, focusing on summing the "age" data field (just as an illustrative example).

You'll still need to adjust the data and include the Bootstrap library back in. However, this approach eliminates the need for custom CSS to manage control placement and avoids the use of !important declarations.

$(document).ready(function () {
    // Setup - add a text input to each footer cell
    $('#example thead tr')
        .clone(true)
        .addClass('filters')
        .appendTo('#example thead');
 
    var table = $('#example').DataTable({
        orderCellsTop: true,
        fixedHeader: true,
        
        footerCallback: function ( row, data, start, end, display ) {
            var api = this.api(), data;
 
            // Remove the formatting to get integer data for summation
            var intVal = function ( i ) {
                return typeof i === 'string' ?
                    i.replace(/[\$,]/g, '')*1 :
                    typeof i === 'number' ?
                        i : 0;
            };
 
            // Total over all pages
            total = api
                .column(3)
                .data()
                .reduce( function (a, b) {
                    return intVal(a) + intVal(b);
                }, 0 );
 
            // Total over this page
            pageTotal = api
                .column(3, { page: 'current'})
                .data()
                .reduce( function (a, b) {
                    return intVal(a) + intVal(b);
                }, 0 );
 
            // Update footer
            $(api.column(3).footer()).html(
                '€'+pageTotal +' (€'+ total +' total)'
            );
        },
        
        initComplete: function () {
            var api = this.api();
 
            // For each column
            api
                .columns()
                .eq(0)
                .each(function (colIdx) {
                    // Set the header cell to contain the input element
                    var cell = $('.filters th').eq(
                        $(api.column(colIdx).header()).index()
                    );
                    var title = $(cell).text();
                    $(cell).html('<input type="text" placeholder="' + title + '" />');
 
                    // On every keypress in this input
                    $(
                        'input',
                        $('.filters th').eq($(api.column(colIdx).header()).index())
                    )
                        .off('keyup change')
                        .on('change', function (e) {
                            // Get the search value
                            $(this).attr('title', $(this).val());
                            var regexr = '({search})'; //$(this).parents('th').find('select').val();
 
                            var cursorPosition = this.selectionStart;
                            // Search the column for that value
                            api
                                .column(colIdx)
                                .search(
                                    this.value != ''
                                        ? regexr.replace('{search}', '(((' + this.value + ')))')
                                        : '',
                                    this.value != '',
                                    this.value == ''
                                )
                                .draw();
                        })
                        .on('keyup', function (e) {
                            e.stopPropagation();
 
                            $(this).trigger('change');
                            $(this)
                                .focus()[0]
                                .setSelectionRange(cursorPosition, cursorPosition);
                        });
                });
        },
    });
});

<!doctype html>
<html>
<head>
  <meta charset="UTF-8">
  <title>Demo</title>
  <script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
  <script src="https://cdn.datatables.net/1.12.1/js/jquery.dataTables.min.js"></script>
  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.12.1/css/jquery.dataTables.min.css">
  <link rel="stylesheet" type="text/css" href="https://datatables.net/media/css/site-examples.css">
</style>

</head>

<body>

<div style="margin: 20px;">

    <table id="example" class="display" style="width:100%">
        <thead>
            <tr>
                <th>Name</th>
                <th>Position</th>
                <th>Office</th>
                <th>Age</th>
                <th>Start date</th>
                <th>Salary</th>
            </tr>
        </thead>
        <tbody>
          [Sample data omitted for brevity]
        </tbody>
        <tfoot>
            <tr>
                <th></th>
                <th></th>
                <th></th>
                <th>123</th>
                <th></th>
                <th></th>
            </tr>
        </tfoot>
    </table>
    
</div>

</body>
</html>

Answer 2

If you're looking for a good starting point, I recommend checking out this example. It does a great job of placing filters in the heading correctly.

The example creates a second header row to keep sorting controls separate from filtering controls. By using the orderCellsTop: true option, DataTables knows to use the first header row for sorting.

(Note that the example also utilizes FixedHeader, which may not be necessary for your project, so feel free to ignore that part.)

This setup allows you to place your totals in the footer, which doesn't interfere with the filter controls' layout.

You can then handle your totals logic in the footerCallback section of the DataTable, similar to what's shown in this example, assuming you're already using something similar in your current DataTable implementation.

For an integrated demonstration, here's a sample runnable demo combining all these elements into one DataTable. The data used is from the DataTables website, focusing on summing the "age" data field (just as an illustrative example).

You'll still need to adjust the data and include the Bootstrap library back in. However, this approach eliminates the need for custom CSS to manage control placement and avoids the use of !important declarations.

$(document).ready(function () {
    // Setup - add a text input to each footer cell
    $('#example thead tr')
        .clone(true)
        .addClass('filters')
        .appendTo('#example thead');
 
    var table = $('#example').DataTable({
        orderCellsTop: true,
        fixedHeader: true,
        
        footerCallback: function ( row, data, start, end, display ) {
            var api = this.api(), data;
 
            // Remove the formatting to get integer data for summation
            var intVal = function ( i ) {
                return typeof i === 'string' ?
                    i.replace(/[\$,]/g, '')*1 :
                    typeof i === 'number' ?
                        i : 0;
            };
 
            // Total over all pages
            total = api
                .column(3)
                .data()
                .reduce( function (a, b) {
                    return intVal(a) + intVal(b);
                }, 0 );
 
            // Total over this page
            pageTotal = api
                .column(3, { page: 'current'})
                .data()
                .reduce( function (a, b) {
                    return intVal(a) + intVal(b);
                }, 0 );
 
            // Update footer
            $(api.column(3).footer()).html(
                '€'+pageTotal +' (€'+ total +' total)'
            );
        },
        
        initComplete: function () {
            var api = this.api();
 
            // For each column
            api
                .columns()
                .eq(0)
                .each(function (colIdx) {
                    // Set the header cell to contain the input element
                    var cell = $('.filters th').eq(
                        $(api.column(colIdx).header()).index()
                    );
                    var title = $(cell).text();
                    $(cell).html('<input type="text" placeholder="' + title + '" />');
 
                    // On every keypress in this input
                    $(
                        'input',
                        $('.filters th').eq($(api.column(colIdx).header()).index())
                    )
                        .off('keyup change')
                        .on('change', function (e) {
                            // Get the search value
                            $(this).attr('title', $(this).val());
                            var regexr = '({search})'; //$(this).parents('th').find('select').val();
 
                            var cursorPosition = this.selectionStart;
                            // Search the column for that value
                            api
                                .column(colIdx)
                                .search(
                                    this.value != ''
                                        ? regexr.replace('{search}', '(((' + this.value + ')))')
                                        : '',
                                    this.value != '',
                                    this.value == ''
                                )
                                .draw();
                        })
                        .on('keyup', function (e) {
                            e.stopPropagation();
 
                            $(this).trigger('change');
                            $(this)
                                .focus()[0]
                                .setSelectionRange(cursorPosition, cursorPosition);
                        });
                });
        },
    });
});

<!doctype html>
<html>
<head>
  <meta charset="UTF-8">
  <title>Demo</title>
  <script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
  <script src="https://cdn.datatables.net/1.12.1/js/jquery.dataTables.min.js"></script>
  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.12.1/css/jquery.dataTables.min.css">
  <link rel="stylesheet" type="text/css" href="https://datatables.net/media/css/site-examples.css">
</style>

</head>

<body>

<div style="margin: 20px;">

    <table id="example" class="display" style="width:100%">
        <thead>
            <tr>
                <th>Name</th>
                <th>Position</th>
                <th>Office</th>
                <th>Age</th>
                <th>Start date</th>
                <th>Salary</th>
            </tr>
        </thead>
        <tbody>
          [Sample data omitted for brevity]
        </tbody>
        <tfoot>
            <tr>
                <th></th>
                <th></th>
                <th></th>
                <th>123</th>
                <th></th>
                <th></th>
            </tr>
        </tfoot>
    </table>
    
</div>

</body>
</html>

What is the best method for displaying the total in datatable?

Answer №1

Similar questions

jQuery seems to have difficulty selecting the text of a hyperlink's element using the text() or html() methods

an all-encompassing loading animation designed specifically for updates to location.href

JavaScript function overriding

What is Reddit's approach to creating a login/signup modal?

Changing a class using JavaScript: Adding and removing class dynamics

`Is it possible to retrieve numerous downloaded images from formData using Express for recovery purposes?`

What is the process for utilizing datePipe in an Angular component?

A guide on how to implement promise return in redux actions for react native applications

When conducting unit testing with Express, encountering an error message such as "`call of undefined`" may indicate that

Do the HTML elements containing tracks in Twilio Programmable Video need to be detached before removing them, or is simply removing them enough?

The pre tag does not have any effect when added after the onload event

When attempting to render a base64 string in an <img> tag using ReactJS, an error message ERR_INVALID_URL is displayed

Is it necessary for me to master React in order to code with React Native?

How can one obtain a distinct identifier retroactively?

Storing extensive JSON data with AJAX, jQuery, and Java

What is the best way to access the local Express server that is located in the same directory as the frontend project

Guide to creating an animated filter effect for a list with the useTransition feature in react-spring

The custom element is unfamiliar: Have you properly registered the component? Vue.js in Laravel

Converting PHP variables to JavaScript variables: A step-by-step guide

retrieve all entries from a paginated grid