Problem on Display Length 10 was exceed also Pagination on the last page is no records

romeo03

Link to test case:
Debugger code (debug.datatables.net):
Error messages shown:
Description of problem:

I'm using Datatables server-side, I have 3 databases with 3 different tables, I put the result in one array, then loop in array to show the result. By default length to display is 10, but on the table rows on the page the total shows are 30, the problem once I click the Pagination on the last page, there is no result. Can you help me with this problem? I will attach a document so that you will understand what I mean.

kthorngren

In order to help debug and understand what you have we minimally need to see your code that looping through the data and displaying the results. Also need the Datatables initialization code. Better is a link to your page or a test case showing the issue so we can see how the code behaves.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

romeo03

Hi Kevin,
Please see the code below.

 * SQL queries
 * Get data to display
*/  
    // Paging
    $sLimit = "";
    if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' ) {
        $sLimit = "LIMIT ".intval( $_GET['iDisplayStart'] ).", ".intval( $_GET['iDisplayLength'] );
        //$sLimit = "LIMIT 0, 20";
    }else{
        $sLimit = "LIMIT 0, 10";
    }       
//CMS Query 
if($_GET["cms_equip"]=='1'){    

        $sQuery = "SELECT SQL_CALC_FOUND_ROWS m_equip.company,m_equip.gage_sn as cal_id,m_equip.manufacturer, m_equip.model_num as model,m_equip.gage_descr as description,m_equip.attribute2 as size, m_equip.gage_cost, m_equip.gage_notes, m_event.EVENT_DATE as date, m_comp.custom3
                    FROM microcle.gages as m_equip
                    JOIN microcle.events AS m_event ON m_event.COMPANY=m_equip.COMPANY AND m_event.GAGE_SN=m_equip.GAGE_SN
                    JOIN microcle.company AS m_comp ON m_event.COMPANY=m_comp.company_name
                    WHERE m_equip.isactive ='1' AND m_event.EVENT_TYPE LIKE 'CALIBRATION%' AND  
                    ".$where." ".$sOrder." ".$sLimit;

        $statement = $conn_cms->prepare($sQuery);

        $statement->execute();
        $rResult = $statement->fetchAll();  
        //$iFilteredTotal = $statement->rowCount();
        $iFilteredTotal_cms = current($conn_cms->query('SELECT FOUND_ROWS()')->fetch());

        $sQuery = "SELECT COUNT(m_equip.GAGE_SN)                    
                    FROM microcle.gages as m_equip
                    JOIN microcle.events AS m_event ON m_event.COMPANY=m_equip.COMPANY AND m_event.GAGE_SN=m_equip.GAGE_SN
                    JOIN microcle.company AS m_comp ON m_event.COMPANY=m_comp.company_name
                    WHERE m_equip.isactive ='1' AND m_event.EVENT_TYPE LIKE 'CALIBRATION%' AND  
                    ".$where;
                    //echo $sQuery;
        $iTotal_cms = current($conn_cms->query($sQuery)->fetch());  
        // Output
        $output = array(
            "sEcho" => intval($_GET['sEcho']),
            "iTotalRecords" => $iTotal_cms,
            "iTotalDisplayRecords" => $iFilteredTotal_cms,
            "aaData" => array()
        );


        foreach ( $rResult as  $f ){    
            $cnt++;
            $resultArr[$cnt]["database"]      = "<p style='color:blue'><b>CMS Equip.</b></p>";
            $resultArr[$cnt]["company"]       = $f["company"];
            $resultArr[$cnt]["cal_id"]        = $f["cal_id"];
            $resultArr[$cnt]["manufacturer"]  = $f["manufacturer"];
            $resultArr[$cnt]["model"]         = $f["model"];
            $resultArr[$cnt]["description"]   = $f["description"];
            $resultArr[$cnt]["size"]          = $f["size"];
            $resultArr[$cnt]["sup_cost"]      = $sup_cost;
            $resultArr[$cnt]["date"]          = date('Y-M-d',strtotime($f["date"]));
            $resultArr[$cnt]["serv_remarks"]  = $f["gage_notes"];
            $resultArr[$cnt]["cal_cost"]      = $f["gage_cost"];
            $resultArr[$cnt]["currency"]      = "USD";
            $resultArr[$cnt]["division"]      = $divNameArr[$division];                  
        }
    }

    //CRM Query
    if($_GET["crm_quote"]=='2'){                                                        
        $sQuery = "SELECT SQL_CALC_FOUND_ROWS r_qitem.it_id as id, r_qitem.it_calCtrl, r_qitem.it_serial as cal_id,  r_qitem.it_manufac as manufacturer, r_qitem.it_model as model, r_qitem.it_description as description,r_qitem.it_remarks,r_qitem.it_costUnit, r_quote.q_acct as company, r_quote.q_dateQuoted as date, m_comp.custom3 as division, r_quote.q_curr  
                    FROM (sales.quote_item AS r_qitem 
                    JOIN sales.quote AS r_quote ON r_qitem.it_qid=r_quote.quote_id)
                    JOIN microcle.company AS m_comp ON r_quote.q_acct = m_comp.company_name
                    WHERE ".$where." AND r_quote.q_acct!='' ".$sOrder." ".$sLimit;                  
        $statement = $conn_sales->prepare($sQuery);
        $statement->execute();
        $rResult = $statement->fetchAll();

        $iFilteredTotal_crm = current($conn_sales->query('SELECT FOUND_ROWS()')->fetch());

        // Get total number of rows in table
        $sQuery1 = "SELECT COUNT(r_quote.quote_id) 
                        FROM (sales.quote_item AS r_qitem 
                        JOIN sales.quote AS r_quote ON r_qitem.it_qid=r_quote.quote_id)
                        JOIN microcle.company AS m_comp ON r_quote.q_acct = m_comp.company_name
                        WHERE ".$where." AND r_quote.q_acct!=''";

        $iTotal_crm = current($conn_sales->query($sQuery1)->fetch());

        $output = array(
            "sEcho" => intval($_GET['sEcho']),
            "iTotalRecords" => $iTotal_crm,
            "iTotalDisplayRecords" => $iFilteredTotal_crm,
            "aaData" => array()
        );          

        foreach ( $rResult as  $f ){    
            $cnt++;             
            $resultArr[$cnt]["database"]      = "<p style='color:green;'><b>CRM Quote</b></p>";
            $resultArr[$cnt]["company"]       = $f["company"];
            $resultArr[$cnt]["cal_id"]        = $cal_id;
            $resultArr[$cnt]["manufacturer"]  = $f["manufacturer"];
            $resultArr[$cnt]["model"]         = $f["model"];
            $resultArr[$cnt]["description"]   = $f["description"];
            $resultArr[$cnt]["size"]          = "";
            $resultArr[$cnt]["sup_cost"]      = "";
            $resultArr[$cnt]["date"]          = date("Y-M-d", $f["date"]);
            $resultArr[$cnt]["serv_remarks"]  = $f["it_remarks"];
            $resultArr[$cnt]["cal_cost"]      = $f["it_costUnit"];
            //$resultArr[$cnt]["currency"]        = $f["q_curr"];
            $resultArr[$cnt]["division"]      = $divNameArr[$division];             
        }
    }
    //Pricing Query
    if($_GET["crm_price"]=='3'){

        $sQuery = "SELECT SQL_CALC_FOUND_ROWS r_equip.equip_id as id,r_equip.model as model,r_equip.manufacturer as manufacturer,r_equip.description as description,r_price.commercial,r_price.z540,r_price._17025 as seven025
                   FROM repository.equipments as r_equip 
                   JOIN repository.pricing AS r_price ON r_equip.equip_id=r_price.equip_id
                   WHERE $where ".$sOrder." ".$sLimit;          
        $statement = $conn_repo->prepare($sQuery);

        $statement->execute();
        $rResult = $statement->fetchAll();

        $iFilteredTotal_repo = current($conn_repo->query('SELECT FOUND_ROWS()')->fetch());

        $sQuery1 = "SELECT COUNT(r_equip.equip_id) 
                    FROM repository.equipments as r_equip 
                    JOIN repository.pricing AS r_price ON r_equip.equip_id=r_price.equip_id
                    WHERE $where ";

        $iTotal_repo = current($conn_repo->query($sQuery1)->fetch());
        $output = array(
            "sEcho" => intval($_GET['sEcho']),
            "iTotalRecords" => $iTotal_repo,
            "iTotalDisplayRecords" => $iFilteredTotal_repo,
            "aaData" => array()
        );          

        foreach ( $rResult as  $f ){    
            $cnt++;
            $resultArr[$cnt]["database"]      = "<p style='color:orange;'><b>CRM Price Guide</b></p>";
            $resultArr[$cnt]["company"]       = "";
            $resultArr[$cnt]["cal_id"]        = "";
            $resultArr[$cnt]["manufacturer"]  = $f["manufacturer"];
            $resultArr[$cnt]["model"]         = $f["model"];
            $resultArr[$cnt]["description"]   = $f["description"];
            $resultArr[$cnt]["size"]          = "";
            $resultArr[$cnt]["sup_cost"]      = "";
            $resultArr[$cnt]["date"]          = "";
            $resultArr[$cnt]["serv_remarks"]  = "";
            $resultArr[$cnt]["cal_cost"]      = $cal_cost;
            $resultArr[$cnt]["currency"]      = "USD";
            $resultArr[$cnt]["division"]      = "";             
        }
    }       


$iTotal = $iTotal_cms + $iTotal_crm + $iTotal_repo;
$iFilteredTotal = $iFilteredTotal_cms + $iFilteredTotal_crm + $iFilteredTotal_repo;
$output = array(
    "sEcho" => intval($_GET['sEcho']),
    "iTotalRecords" => $iTotal,
    "iTotalDisplayRecords" => $iFilteredTotal,
    "aaData" => array()
);

for( $i=1; $i<=COUNT($resultArr); $i++ )    
{
    $row = array();

    $row[] = $resultArr[$i]["database"];
    $row[] = $resultArr[$i]["company"];
    $row[] = $resultArr[$i]["cal_id"];
    $row[] = $resultArr[$i]["manufacturer"];
    $row[] = $resultArr[$i]["model"];
    $row[] = $resultArr[$i]["description"];
    $row[] = $resultArr[$i]["size"];
    $row[] = $resultArr[$i]["sup_cost"];
    $row[] = $resultArr[$i]["date"];
    $row[] = $resultArr[$i]["serv_remarks"];
    $row[] = $resultArr[$i]["cal_cost"];
    //$row["currency"] = $keyval["currency"];
    $row[] = $resultArr[$i]["division"];    

    $output['aaData'][] = $row;
}   

echo json_encode( $output );