DataTables
Advanced interaction
features for your tables.
Editor
Comprehensive editing
library for DataTables.
Manual
Download
≡ Show site navigation

Error Undefined index: draw, start, lenght, order, column, search, allcount

Error Undefined index: draw, start, lenght, order, column, search, allcount

alvinkhanalvinkhan Posts: 2Questions: 1Answers: 0
in Free community support

hi, i want to ask how to make server side datatables using costum filter on codeigniter.

this my model.php:

 function getUsers($postData = null)
  {
    $response = array();
    $draw = isset($postData['draw']);
    $start = isset($postData['start']);
    $rowperpage = isset($postData['length']); // Rows display per page
    $columnIndex = isset($postData['order'][0]['column']); // Column index
    $columnName = isset($postData['columns'][$columnIndex]['data']); // Column name
    $columnSortOrder = isset($postData['order'][0]['dir']); // asc or desc
    $searchValue = isset($postData['search']['value']); // Search value

    // Custom search filter 
    $searchDepartement = $postData['searchDepartement'];
    $searchCategory = $postData['searchCategory'];
    $searchMERGE_ITEM = $postData['searchMERGE_ITEM'];

    ## Search 
    $search_arr = array();
    $searchQuery = "";
    if ($searchValue != '') {
        $search_arr[] = " (departement like '%" . $searchValue . "%' or 
     category like '%" . $searchValue . "%' or 
     MERGE_ITEM like'%" . $searchValue . "%' ) ";
    }
    if ($searchDepartement != '') {
        $search_arr[] = "departement='" . $searchDepartement . "' ";
    }
    if ($searchCategory != '') {
        $search_arr[] = "category='" . $searchCategory . "' ";
    }
    if ($searchMERGE_ITEM != '') {
        $search_arr[] = "MERGE_ITEM like '%" . $searchMERGE_ITEM . "%' ";
    }
    if (count($search_arr) > 0) {
        $searchQuery = implode(" and ", $search_arr);
    }

    ## Total number of records without filtering
    $percobaan = "SELECT
    TCS.departement, 
    TCS.category,
    m.mnth AS CURR_MONTH,
    MI.SIZCA0,
    MI.CHNCA0,
    MI.TAPCA0,
    CONCAT(MI.SIZCA0,' ',MI.CHNCA0,MI.TAPCA0) AS MERGE_ITEM,
    COALESCE(SUM(CASE 
            WHEN BE.DFROM IN ('SEWING', 'WEAVING', 'FORMING', 'DYE', 'HSM', 'INSPECTION', 'WOVEN') AND BE.DFROM = BE.DTO
                THEN BE.QTY
            END), 0.00) AS TOTAL_LOSS_PROSES,
    COALESCE(SUM(CASE
        WHEN  DFROM != 'SEWING' THEN QTY 
        WHEN  DFROM != 'WEAVING'THEN QTY 
        WHEN  DFROM != 'FORMING'THEN QTY 
        WHEN  DFROM != 'DYE'THEN QTY 
        WHEN  DFROM != 'HSM'THEN QTY 
        WHEN  DFROM != 'INSPECTION'THEN QTY 
        WHEN  DFROM != 'WOVEN'THEN QTY
        ELSE 0.00
    END), 0.00) AS TOTAL_LOSS_SEMUA
    FROM tbl_category_sewing TCS
    CROSS JOIN (
    SELECT '2022-04' AS mnth        
        UNION ALL 
        SELECT '2022-05'     
        UNION ALL 
        SELECT '2022-06'
        UNION ALL 
        SELECT '2022-07'  
        UNION ALL 
        SELECT '2022-08'  
    ) AS m
    LEFT OUTER JOIN  
    B_ENTRYLOSS BE ON TCS.category = BE.LOSSCAT AND FORMAT(BE.DT, 'yyyy-MM') = m.mnth
    LEFT OUTER JOIN
    masteritem MI ON BE.ITEMCD = MI.ITMCA0
    WHERE departement = 'WEAVING' 
    GROUP BY TCS.departement, TCS.category, m.mnth, MI.SIZCA0, MI.CHNCA0, MI.TAPCA0, CONCAT(MI.SIZCA0,' ',MI.CHNCA0,MI.TAPCA0)
    ORDER BY category ASC, CURR_MONTH ASC, SIZCA0 ASC;";
    $this->db->select('count(*) as allcount');
    $records = $this->db->query($percobaan)->result();
    $totalRecords = $records[0]->allcount;

    ## Total number of record with filtering
    $percobaan = "SELECT
    TCS.departement, 
    TCS.category,
    m.mnth AS CURR_MONTH,
    MI.SIZCA0,
    MI.CHNCA0,
    MI.TAPCA0,
    CONCAT(MI.SIZCA0,' ',MI.CHNCA0,MI.TAPCA0) AS MERGE_ITEM,
    COALESCE(SUM(CASE 
            WHEN BE.DFROM IN ('SEWING', 'WEAVING', 'FORMING', 'DYE', 'HSM', 'INSPECTION', 'WOVEN') AND BE.DFROM = BE.DTO
                THEN BE.QTY
            END), 0.00) AS TOTAL_LOSS_PROSES,
    COALESCE(SUM(CASE
        WHEN  DFROM != 'SEWING' THEN QTY 
        WHEN  DFROM != 'WEAVING'THEN QTY 
        WHEN  DFROM != 'FORMING'THEN QTY 
        WHEN  DFROM != 'DYE'THEN QTY 
        WHEN  DFROM != 'HSM'THEN QTY 
        WHEN  DFROM != 'INSPECTION'THEN QTY 
        WHEN  DFROM != 'WOVEN'THEN QTY
        ELSE 0.00
    END), 0.00) AS TOTAL_LOSS_SEMUA
    FROM tbl_category_sewing TCS
    CROSS JOIN (
    SELECT '2022-04' AS mnth        
        UNION ALL 
        SELECT '2022-05'     
        UNION ALL 
        SELECT '2022-06'
        UNION ALL 
        SELECT '2022-07'  
        UNION ALL 
        SELECT '2022-08'  
    ) AS m
    LEFT OUTER JOIN  
    B_ENTRYLOSS BE ON TCS.category = BE.LOSSCAT AND FORMAT(BE.DT, 'yyyy-MM') = m.mnth
    LEFT OUTER JOIN
    masteritem MI ON BE.ITEMCD = MI.ITMCA0
    WHERE departement = 'WEAVING' 
    GROUP BY TCS.departement, TCS.category, m.mnth, MI.SIZCA0, MI.CHNCA0, MI.TAPCA0, CONCAT(MI.SIZCA0,' ',MI.CHNCA0,MI.TAPCA0)
    ORDER BY category ASC, CURR_MONTH ASC, SIZCA0 ASC;";
    $this->db->select('count(*) as allcount');
    if ($searchQuery != '')
        $this->db->where($searchQuery);
    $records = $this->db->query($percobaan)->result();
    $totalRecordwithFilter = $records[0]->allcount;

    ## Fetch records
    $percobaan = "SELECT
    TCS.departement, 
    TCS.category,
    m.mnth AS CURR_MONTH,
    MI.SIZCA0,
    MI.CHNCA0,
    MI.TAPCA0,
    CONCAT(MI.SIZCA0,' ',MI.CHNCA0,MI.TAPCA0) AS MERGE_ITEM,
    COALESCE(SUM(CASE 
            WHEN BE.DFROM IN ('SEWING', 'WEAVING', 'FORMING', 'DYE', 'HSM', 'INSPECTION', 'WOVEN') AND BE.DFROM = BE.DTO
                THEN BE.QTY
            END), 0.00) AS TOTAL_LOSS_PROSES,
    COALESCE(SUM(CASE
        WHEN  DFROM != 'SEWING' THEN QTY 
        WHEN  DFROM != 'WEAVING'THEN QTY 
        WHEN  DFROM != 'FORMING'THEN QTY 
        WHEN  DFROM != 'DYE'THEN QTY 
        WHEN  DFROM != 'HSM'THEN QTY 
        WHEN  DFROM != 'INSPECTION'THEN QTY 
        WHEN  DFROM != 'WOVEN'THEN QTY
        ELSE 0.00
    END), 0.00) AS TOTAL_LOSS_SEMUA
    FROM tbl_category_sewing TCS
    CROSS JOIN (
    SELECT '2022-04' AS mnth        
        UNION ALL 
        SELECT '2022-05'     
        UNION ALL 
        SELECT '2022-06'
        UNION ALL 
        SELECT '2022-07'  
        UNION ALL 
        SELECT '2022-08'  
    ) AS m
    LEFT OUTER JOIN  
    B_ENTRYLOSS BE ON TCS.category = BE.LOSSCAT AND FORMAT(BE.DT, 'yyyy-MM') = m.mnth
    LEFT OUTER JOIN
    masteritem MI ON BE.ITEMCD = MI.ITMCA0
    WHERE departement = 'WEAVING' 
    GROUP BY TCS.departement, TCS.category, m.mnth, MI.SIZCA0, MI.CHNCA0, MI.TAPCA0, CONCAT(MI.SIZCA0,' ',MI.CHNCA0,MI.TAPCA0)
    ORDER BY category ASC, CURR_MONTH ASC, SIZCA0 ASC;";
    if ($searchQuery != '')
        $this->db->where($searchQuery);
    $this->db->order_by($columnName, $columnSortOrder);
    $this->db->limit($rowperpage, $start);
    $records = $this->db->query($percobaan)->result();

    $data = array();

    foreach ($records as $record) {

        $data[] = array(
            "departement" => $record->departement,
            "category" => $record->category,
            "MERGE_ITEM" => $record->MERGE_ITEM,
            "TOTAL_LOSS_PROSES" => $record->TOTAL_LOSS_PROSES,
            "TOTAL_LOSS_RETURN" => $record->TOTAL_LOSS_SEMUA - $record->TOTAL_LOSS_PROSES,
            "TOTAL_LOSS_SEMUA" => $record->TOTAL_LOSS_SEMUA
        );
    }

    ## Response
    $response = array(
        "draw" => intval($draw),
        "iTotalRecords" => $totalRecords,
        "iTotalDisplayRecords" => $totalRecordwithFilter,
        "aaData" => $data
    );

    return $response;
}:

please help me

Answers

  • alvinkhanalvinkhan Posts: 2Questions: 1Answers: 0

    this my controller.php

    public function weaving()
  {
    $this->load->helper('url');
    $cat = $this->Mod_weaving->getCategory();
    $data['category'] = $cat;
    $this->load->view('templates/header', $data);
    $this->load->view('chain/weaving', $data);
    $this->load->view('templates/footer', $data);
 }

public function userList()
{
    $postData = $this->input->post();
    $data = $this->Mod_weaving->getUsers($postData);
    echo json_encode($data);
}

    this is my view:

      <div>
            <!-- Departement -->
            <select id='sel_departement'>
                <option value=''>-- Select Departement --</option>
                <option value='WEAVING'>WEAVING</option>
            </select>

            <!-- Category -->
            <select id='sel_category'>
                <option value=''>-- Select Category --</option>
                <?php
                foreach ($category as $cat) {
                    echo "<option value='" . $cat . "'>" . $cat . "</option>";
                }
                ?>

            </select>

            <!-- Name -->
            <input type="text" id="searchName" placeholder="Search ITEM">
        </div>

        <!-- Table -->
        <table id='userTable' class='display dataTable'>

            <thead>
                <tr>
                    <th>Departement</th>
                    <th>Category</th>
                    <th>Item Name</th>
                    <th>Total Loss Process</th>
                    <th>Total Loss Return</th>
                    <th>Total Loss Semua</th>
                </tr>
            </thead>

        </table>
  <div>

    this my html script:

       $(document).ready(function() {
    var userDataTable = $('#userTable').DataTable({
        'processing': true,
        'serverSide': true,
        'serverMethod': 'POST',
        //'searching': false, // Remove default Search Control
        'ajax': {
            'url': '<?= base_url() ?>chain/userList',
            'data': function(data) {
                data.searchDepartement = $('#sel_departement').val();
                data.searchCategory = $('#sel_category').val();
                data.searchMERGE_ITEM = $('#searchName').val();
            }
        },
        'columns': [{
                data: 'departement'
            },
            {
                data: 'category'
            },
            {
                data: 'MERGE_ITEM'
            },
            {
                data: 'TOTAL_LOSS_PROSES'
            },
            {
                data: 'TOTAL_LOSS_RETURN'
            },
            {
                data: 'TOTAL_LOSS_SEMUA'
            },
        ]
    });

    $('#sel_departement,#sel_category').change(function() {
        userDataTable.draw();
    });
    $('#searchName').keyup(function() {
        userDataTable.draw();
    });
});
  • allanallan Posts: 63,498Questions: 1Answers: 10,471 Site admin

    DataTables uses a GET request by default, but your server-side script appears to be looking for POST data.

    Add:

    type: 'post'

    to your ajax object.

    Allan

Sign In or Register to comment.

DataTables

DataTables designed and created by SpryMedia Ltd.
© 2007-2023 MIT licensed. Privacy policy. Supporters.
SpryMedia Ltd is registered in Scotland, company no. SC456502.