PDO DataTable Footer Callback not working

Shamaine

Hi,

I have a datatable as in the code below and the footer total does not seem to work. Please help, not sure where I went wrong.

<table class="table table-bordered table-striped" id="order_data" style="margin-top:20px;">
                <thead>
                    <th>ID</th>
                    <th>Firstname</th>
                    <th>Lastname</th>
                    <th>Address</th>
                    <th style="text-align: right;">Number</th>
                    <th>Action</th>
                </thead>
                <tbody>
                    <?php
                        //include our connection
                        include_once('connection.php');

                        $database = new Connection();
                        $db = $database->open();
                        try{    
                            $sql = 'SELECT * FROM members';
                            foreach ($db->query($sql) as $row) {
                                ?>
                                <tr>
                                    <td><?php echo $row['id']; ?></td>
                                    <td><?php echo $row['firstname']; ?></td>
                                    <td><?php echo $row['lastname']; ?></td>
                                    <td><?php echo $row['address']; ?></td>
                                    <td style="text-align: right;">R <?php echo $row['Loan']; ?></td>
                                    <td>
                                        <a href="#edit_<?php echo $row['id']; ?>" class="btn btn-success btn-sm" data-toggle="modal"><span class="glyphicon glyphicon-edit"></span> Edit</a>
                                        <a href="#delete_<?php echo $row['id']; ?>" class="btn btn-danger btn-sm" data-toggle="modal"><span class="glyphicon glyphicon-trash"></span> Delete</a>
                                    </td>
                                    <?php include('edit_delete_modal.php'); ?>
                                </tr>
                                <?php 
                            }
                        }
                        catch(PDOException $e){
                            echo "There is some problem in connection: " . $e->getMessage();
                        }

                        //close connection
                        $database->close();

                    ?>
                </tbody>
                <tfoot>
            <tr>
            <th></th>
            <th></th>
            <th></th>
                <th>Total:</th>
                <th></th>
                <th></th>
            </tr>
        </tfoot>
            </table>
        </div>
    </div>
</div>
<?php include('add_modal.php'); ?>
<script src="jquery.min.js"></script>
<script src="bootstrap/js/bootstrap.min.js"></script>
<script type="text/javascript" language="javascript">
        $(document).ready(function() {
    $('#order_data').DataTable( {
        "footerCallback": function ( row, data, start, end, display ) {
            var api = this.api();
 
            // Remove the formatting to get integer data for summation
            var intVal = function ( i ) {
                return typeof i === 'string' ?
                    i.replace(/[\R,]/g, '')*1 :
                    typeof i === 'number' ?
                        i : 0;
            };
 
            // Total over all pages
            total = api
                .column( 5 )
                .data()
                .reduce( function (a, b) {
                    return intVal(a) + intVal(b);
                }, 0 );
 
            // Total over this page
            pageTotal = api
                .column( 5, { page: 'current'} )
                .data()
                .reduce( function (a, b) {
                    return intVal(a) + intVal(b);
                }, 0 );
 
            // Update footer
            $( api.column( 5 ).footer() ).html(
                'R'+pageTotal +' ( R'+ total +' total)'
            );
        }
    } );
} );
    </script>

kthorngren

i.replace(/[\R,]/g, '')*1

This looks wrong. Try this instead:

i.replace(/[R\s,]/g, '')*1 

This should replace R or any white space (\s) or , with ''. A good place to learn regex is here:
https://regex101.com/

If you still need help please provide a simple test case with an example of your data so we can work with your actual data.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

colin

It would be worth looking at Editor as those buttons in the table could be handled seamlessly - please see example here: https://editor.datatables.net/examples/simple/inTableControls.html

Colin

Shamaine

@Kevin @colin, thank you for your remarks. I will look into them.