Why html loads completely before loading datatable?

0
votes

For downloading button I had refer page: https://www.datatables.net/extensions/buttons/examples/initialisation/export.html

For server side I had refer page: https://datatables.net/examples/data_sources/server_side.html

I had refer above page for making datatable to get data from database and download in different format. But the main problem is it first load the html table. after completely loading html table it shows the datatable which is noticeable for large no of rows. I have to get large no of rows to show in datatable and export the file in excel. Can anyone help me how can i load datatable without loading html table.

Here is my code

<!DOCTYPE html>
<html>
<head>
<title></title>
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>

<!-- for button -->
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.15/css/jquery.dataTables.min.css">
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/buttons/1.3.1/css/buttons.dataTables.min.css">
<!-- for button -->
</head>
<body>
<table id="example" class="display nowrap" cellspacing="0" width="100%">
    <thead>
                <tr>
                  <th style="text-align: center;">Model No</th>
                  <th style="text-align: center;">BIll NO</th>
                  <th style="text-align: center;">Name Of Party</th>
                  <th style="text-align: center;">Bill Miti</th>
                  <th style="text-align: center;">Invoice Date</th>  
                  <th style="text-align: center;">IMEI No</th> 
                  <th style="text-align: center;">Price</th> 
                  <th style="text-align: center;"><div id="section_notTo_Print"> <a href="#"> Edit </a></div></th>         
                </tr>
    </thead>
    <tbody>
     <?php
    include 'dbconnect.php';
                //get records from database
                $query = $con->query("SELECT * FROM salesreport ORDER BY id DESC");
                if($query->num_rows > 0){ 
                     while($row = $query->fetch_assoc()){ ?> 
                <tr>
                  <td style="text-align: center;"><?php echo $row['modelno']; ?></td>
                  <td style="text-align: center;"><?php echo $row['billno']; ?></td>
                  <td style="text-align: center;"><?php echo $row['nameOfParty']; ?></td>
                  <td style="text-align: center;"><?php echo $row['billMiti']; ?></td>
                  <td style="text-align: center;"><?php echo $row['invoiceDate']; ?></td>
                  <td style="text-align: center;"><?php echo $row['IMEINo']; ?></td>
                  <td style="text-align: center;"><?php echo $row['price']; ?></td>

                  <td style="text-align: center;"><div id="section_notTo_Print"> <a href="edit_saleList.php?id=<?php echo $row['id']; ?>">Edit</a> </div></td>
                   <!-- <td><?php //echo $row['Quantity']; ?></td> -->
                  <!-- <td><?php //echo ($row['status'] == '1')?'Active':'Inactive'; ?></td> -->
                </tr> 
                <?php } }else{ ?>
                <tr><td colspan="5">No distributor found.....</td></tr>
                <?php } ?>
    </tbody>
</table>

<!-- for exporting -->


<script type="text/javascript" src="https://code.jquery.com/jquery-1.12.4.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/1.10.15/js/jquery.dataTables.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/buttons/1.3.1/js/dataTables.buttons.min.js">
</script>
<script type="text/javascript" src="https://cdn.datatables.net/buttons/1.3.1/js/buttons.flash.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script>
<script type="text/javascript" src="https://cdn.rawgit.com/bpampuch/pdfmake/0.1.27/build/pdfmake.min.js">
</script>
<script type="text/javascript" src="https://cdn.rawgit.com/bpampuch/pdfmake/0.1.27/build/vfs_fonts.js">
</script>
<script type="text/javascript" src="https://cdn.datatables.net/buttons/1.3.1/js/buttons.html5.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/buttons/1.3.1/js/buttons.print.min.js"></script>

<script>
    $(document).ready(function() {
$('#example').DataTable( {
    "processing": true,
     // "serverSide": true,
     // "ajax": "datatable2.php",
    dom: 'lBfrtip',
    buttons: [
        {
            extend: 'collection',
            text: 'Export',
            buttons: [
                'copy',
                'excel',
                'csv',
                'pdf',
                'print'
            ]
        }
    ]
} );
} );

</script>
<!-- for exporting -->
</body>
</html>
1
phpjquerydatatable
you need to call data from database using ajax – Reena Mori
but doing that it will download only the shown data in table. it will not download the whole search data even that is not showing in datatable. – Sushank Pokharel
you load all data direct using query make using ajax – Reena Mori
Reena Mori can you say me how to do that? Can you provide me a example code or something like that that will help me. – Sushank Pokharel
are you check my answer? – Reena Mori

1 Answers

1
votes

This code for your reference it`s sample code how to load table record from database in datatable

Viewfile

    <table id="placeTable" class="table table-striped table-bordered responsive nowrap">
        <thead class="">
            <tr>
                <th>Name</th>
                <th>Location</th>
                <th>Status</th>
                <th>Action</th>
                <th></th>
            </tr>
        </thead>
    </table> 
</div><!--table-responsive-->
<script>


    jQuery(document).ready(function () {

        jQuery('#placeTable').DataTable({
            "oLanguage": {
                "sProcessing": '<img alt src="<?php echo base_url('assets/images/loaders/CustomLoader.gif'); ?>" style="opacity: 1.0;filter: alpha(opacity=100);">'
            },
            "processing": true,
            "serverSide": true,
            "responsive": true,
            "order": [[4, "DESC"]],
            "ajax": {
                url: "<?php echo site_url('admin/place/getPlace'); ?>",
            },

            "columns": [

                {"taregts": 0, "data": "name"},
                {"taregts": 1, "data": "location"},
                {"taregts": 2,
                    "searchable": false,
                    "orderable": false,
                    "sClass": "text-center",
                    "render": function (data, type, row) {

                        var id = row.id;

                        if (row.is_active === '1') {

                            var out = '&nbsp;<a class="btn btn-success statusenable" href="#" \n\  \n\
                                   title="Click to Change status" data-href="<?php echo site_url('admin/place/change_status') ?>/' + id + '"  data-toggle="modal" data-target="#confirm-status"  title="Confirm">\n\
                               Enable</i></a> ';
                            return out;
                        } else if (row.is_active === '0') {
                            var out = '&nbsp;<a class="btn btn-danger statusenable" href="#" \n\  \n\
                                   title="Click to Change status" data-href="<?php echo site_url('admin/place/change_status') ?>/' + id + '"  data-toggle="modal" data-target="#confirm-status" title="Confirm">\n\
                               Disable</i></a> ';

                            return out;
                        }
                    }
                },
                {"taregts": 3, "searchable": false,
                    "orderable": false,
                    "sClass": "text-center",
                    "render": function (data, type, row) {
                        var id = row.id;
                        return '<a class="actioncol cursor view" href=\'<?php echo site_url('admin/place/view'); ?>/' + id + '\'\n\
                  title="View"><i class="glyphicon glyphicon-eye-open"></i></a>\n\<a class="actioncol cursor view" href=\'<?php echo site_url('admin/place/edit'); ?>/' + id + '\'\n\
                  title="Edit"><i class="glyphicon glyphicon-edit"></i></a>';
                    }
                },
                {"taregts": 4, "visible": false,

                    render: function (data, type, row) {
                        return row.id;
                    }

                }
            ]
        });

    });
</script>

Controller Place.php file

//get all place
    public function getPlace() {

        $columns = array('name', 'location','is_active','id','id');
        $request = $this->input->get();
        $condition = array();

        $getfiled = "name,is_active,location,id";
        echo $this->db_common->getDataTableSource('place', $columns, $condition, $getfiled, $request);
        die();
    }

model for database query common.php

 /*
 * This function is to create the data source of the Jquery Datatable
 * 
 * @param $tablename Name of the Table in database
 * @param $datatable_fields Fields in datatable that avalable for filtering
 * @param $condition_array conditions for Query 
 * @param $data The field set tobe return to datatables
 * @param $request The Get or Post Request Sent from Datatable
 * @param $join_str Join array for Query
 * @return JSON data for datatable
 */

function getDataTableSource($tablename, $datatable_fields = array(), $conditions_array = array(), $data = '*', $request, $join_str = array()) {
    $output = array();
    //Fields tobe display in datatable
    $this->db->select($data);
    //Making Join with tables if provided
    if (!empty($join_str)) {
        foreach ($join_str as $join) {
            if (!isset($join['join_type'])) {
                $this->db->join($join['table'], $join['join_table_id'] . '=' . $join['from_table_id']);
            } else {
                $this->db->join($join['table'], $join['join_table_id'] . '=' . $join['from_table_id'], $join['join_type']);
            }
        }
    }
    //COnditions for Query
    if (!empty($conditions_array)) {
        $this->db->where($conditions_array);
    }
    //Total record in query tobe return
    $output['recordsTotal'] = $this->db->count_all_results($tablename, FALSE);

    //Filtering based on the datatable_fileds
    if ($request['search']['value'] != '') {
        $this->db->group_start();
        for ($i = 0; $i < count($datatable_fields); $i++) {
            if ($request['columns'][$i]['searchable'] == true) {

                $this->db->or_like($datatable_fields[$i], $request['search']['value']);
            }
        }
        $this->db->group_end();
    }

    //Total number of records return after filtering not no of record display on page.
    //It must be counted before limiting the resultset.
    $output['recordsFiltered'] = $this->db->count_all_results(NULL, FALSE);

    //Setting Limit for Paging
    $this->db->limit($request['length'], $request['start']);

    //ordering the query
    if (isset($request['order']) && count($request['order'])) {
        for ($i = 0; $i < count($request['order']); $i++) {
            if ($request['columns'][$request['order'][$i]['column']]['orderable'] == true) {
                $this->db->order_by($datatable_fields[$request['order'][$i]['column']] . ' ' . $request['order'][$i]['dir']);
            }
        }
    }

    $query = $this->db->get();
    $output['draw'] = $request['draw'];
    $output['data'] = $query->result_array();

    return json_encode($output);
}`enter code here`