how to make server side work with Codeigniter

how to make server side work with Codeigniter

under5hellunder5hell Posts: 1Questions: 0Answers: 0
edited May 2012 in DataTables 1.9
I have about 3000 rows of record to be displayed in a table (i'm using postgresql). But i don't want the entire records to be loaded in the first time (it will slow the page load down). Instead. I prefer to make it separated into several pages and load it according to user action when user hit the next button. I have struggle with some code I made, but it still display an entire records with "Showing 1 to NaN of NaN entries (filtered from NaN total entries)" at the botom of the table.
This is my code.

The Controller:
[code]
<?php

class Testdata extends CI_Controller
{
function __consrtuct()
{
parent::__consrtuct();
}

function index()
{
$this->load->view('vtestdata');
}

function getData()
{

$this->load->model('mtestdata');
$result = $this->mtestdata->getDatax();
echo json_encode($result);

}
}
[/code]

The Model:
[code]
<?php

class Mtestdata extends CI_Model
{
function __construct()
{
parent::__construct();
}


function getDatax()
{

//output
$output = array(
"aData" => array()
);
$this->db->select('nik, user_name');
$query=$this->db->get('t_mtr_user');
$output["aData"] = $query->result();
return $output;
}
}
[/code]

The View:
[code]






$( document ).ready( function() {

$('#testTable').dataTable({


"bJQueryUI": true, //UI
"sAjaxSource": "<?=base_url();?>index.php/testdata/getData", //datasource
"sAjaxDataProp": "aData", //menentukan array/json dibaca dari mana
"bServerSide": true, //serverside , ini yg bermasalah, kalo di delete beres gak ada error, tapi gak SSP
"bProcessing": true,
"aoColumns":[ //tentukan kolom pd tabel dan value nya
{"mDataProp": "nik", "sTitle": "NIK"},
{"mDataProp": "user_name", "sTitle": "Username"}
]


});
});








test


<!--

nik
User name

-->


<!--


-->




[/code]

Any advice guys? I really appreciate your help. :-)

Replies

  • agilitygapagilitygap Posts: 5Questions: 0Answers: 0
    Not sure if it is relevant, but there is a typo in your controller's two "__construct()" terms. You have them misspelled as __consrtuct()
  • matthttammatthttam Posts: 40Questions: 2Answers: 0
    edited November 2012
    I won't be too much help but I can give you a little insight hopefully. I have 4 tables that total slightly less than 25 thousand records. I am using mysql on the backend. The way it works without having to load all 25 thousand records on page load is by changing the php code that gets the data so that it takes into account the elements that datatables passes to it.

    I found an example on this website using mysql that was very simple... I changed it so that it could handle table joins etc. Here is one of my php scripts. This one is for Workorders:

    *Posted in next post... alot of characters... Also I removed the comments at the beginning so here are those:
    [code]
    <?php
    include('qAuth.php');
    include('../../../sqlconn.php');
    /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
    * Easy set variables
    */

    /* Array of database columns which should be read and sent back to DataTables. Use a space where
    * you want to insert a non-database field (for example a counter or static image)
    */
    /* Generate data for the WO tab
    *
    * $aColumns is the short version of each column. Used to reference the associative array
    * $QueryaColumnsFull is the long version of every columnn including aliases
    * $aColumnsFull is the long version of every column and any alias is referenced as such
    * $selectLIsts determine which field should return a select list
    * $hiddenRowCount appends an empty row to each value set. Used for something...
    */
    $aColumns = array( 'WOID', 'status', 'dateSubmitted', 'dateCompleted', 'fname', 'lname', 'assetTag', 'commonProblem','grade','gradYear', 'school', 'uName', 'password','caseNum','repairID','IR','serialNumber','timestamp');
    $QueryaColumnsFull = array( 'workorders.WOID', 'workorders.status', 'workorders.dateSubmitted', 'workorders.dateCompleted', 'student.fname', 'student.lname', 'workorders.assetTag', 'workorders.commonProblem','workorders.grade','workorders.gradYear', 'workorders.school', 'student.uName','student.password','workorders.caseNum','workorders.repairID','workorders.IR', 'computer.serialNumber', 'workorders.dateSubmitted as \'timestamp\'');
    $aColumnsFull = array( 'workorders.WOID', 'workorders.status', 'workorders.dateSubmitted', 'workorders.dateCompleted', 'student.fname', 'student.lname', 'workorders.assetTag', 'workorders.commonProblem','workorders.grade','workorders.gradYear', 'workorders.school', 'student.uName','student.password','workorders.caseNum','workorders.repairID','workorders.IR', 'computer.serialNumber', '\'timestamp\'');
    $selectLists = array('workorders.status','workorders.grade', 'workorders.gradYear','workorders.school');
    $hiddenRowCount = 1; //Adds an empty row to each value set for hidden values used in the DT
    /* Indexed column (used for fast and accurate table cardinality) */
    $sIndexColumn = "WOID";
    [/code]

    HERE is an example of what it outputs. The names, usernames, and serial numbers have been changed for protection reasons...

    [code]
    {"sEcho":1,"iTotalRecords":"11400","iTotalDisplayRecords":"11400","
    select":[
    [],
    ["Apple Care","Awaiting Administration","Awaiting Box","Awaiting Pickup","Awaiting Tech Support","Being Reimaged","Digital Doc","In Progress","Needs Applecare","Pending","Resolved","With Mike","With Norman","Not Resolved"],
    [],
    [],
    [],
    [],
    [],
    [],
    ["05","06","07","08","09","10","11","12","2012","2013"],
    ["7 Hills","CO","none","OHS","OMS-N","OMS-S"],
    [],
    [],
    [],
    [],
    [],
    []],
    "aaData":
    [
    {"DT_RowId":"11585","0":"11585","1":"Needs Applecare","2":"11\/5\/2012","3":null,"4":"FirstName","4":"LastName","6":"22945","7":"Hardware > Charger > Freyed Cord","8":"07","9":"OMS-N","10":"username","11":"207439","12":null,"13":null,"14":null,"15":"S/N","16":""},
    {"DT_RowId":"11584","0":"11584","1":"Resolved","2":"11\/5\/2012","3":"11\/5\/2012","4":"FirstName","4":"LastName","6":"22701","7":"Software > Internet > Unable to Join","8":"08","9":"OMS-N","10":"username","11":"209322","12":null,"13":null,"14":null,"15":"S/N","16":""},
    {"DT_RowId":"11583","0":"11583","1":"Resolved","2":"11\/5\/2012","3":"11\/5\/2012","4":"FirstName","4":"LastName","6":"22945","7":"Software > Internet > Unable to Join","8":"07","9":"OMS-N","10":"username","11":"207439","12":null,"13":null,"14":null,"15":"S/N","16":""},
    {"DT_RowId":"11582","0":"11582","1":"Resolved","2":"11\/5\/2012","3":"11\/5\/2012","4":"FirstName","4":"LastName","6":"23185","7":"Software > Internet > Unable to Join","8":"06","9":"OMS-S","10":"username","11":null,"12":null,"13":null,"14":null,"15":"S/N","16":""},
    {"DT_RowId":"11581","0":"11581","1":"Resolved","2":"11\/5\/2012","3":"11\/5\/2012","4":"FirstName","4":"LastName","6":"22240","7":"Software > Internet > Unable to Join","8":"09","9":"OHS","10":"username","11":null,"12":null,"13":null,"14":null,"15":"S/N","16":""},
    {"DT_RowId":"11580","0":"11580","1":"Resolved","2":"11\/5\/2012","3":"11\/5\/2012","4":"FirstName","4":"LastName","6":"22243","7":"Software > Internet > Unable to Join","8":"09","9":"OHS","10":"username","11":null,"12":null,"13":null,"14":null,"15":"S/N","16":""},
    {"DT_RowId":"11579","0":"11579","1":"Resolved","2":"11\/5\/2012","3":"11\/5\/2012","4":"FirstName","4":"LastName","6":"23562","7":"Hardware > Charger > Duckhead","8":"05","9":"OMS-S","10":"username","11":null,"12":null,"13":null,"14":null,"15":"S/N","16":""},
    {"DT_RowId":"11578","0":"11578","1":"Resolved","2":"11\/5\/2012","3":"11\/5\/2012","4":"FirstName","4":"LastName","6":"21490","7":"Software > Internet > Unable to Join","8":"06","9":"OMS-S","10":"username","11":null,"12":null,"13":null,"14":null,"15":"S/N","16":""},
    {"DT_RowId":"11577","0":"11577","1":"Needs Applecare","2":"11\/5\/2012","3":null,"4":"FirstName","4":"LastName","6":"22634","7":"Hardware > Charger > Internals","8":"09","9":"OHS","10":"username","11":"204244","12":null,"13":null,"14":null,"15":"S/N","16":""},
    {"DT_RowId":"11576","0":"11576","1":"Resolved","2":"11\/5\/2012","3":"11\/5\/2012","4":"FirstName","4":"LastName","6":"21648","7":"Software > Internet > Unable to Join","8":"11","9":"OHS","10":"username","11":null,"12":null,"13":null,"14":null,"15":"S/N","16":""}
    ]
    }
    [/code]
  • matthttammatthttam Posts: 40Questions: 2Answers: 0
    edited November 2012
    [code]
    <?php
    include('qAuth.php');
    include('../../../sqlconn.php');
    $aColumns = array( 'WOID', 'status', 'dateSubmitted', 'dateCompleted', 'fname', 'lname', 'assetTag', 'commonProblem','grade','gradYear', 'school', 'uName', 'password','caseNum','repairID','IR','serialNumber','timestamp');
    $QueryaColumnsFull = array( 'workorders.WOID', 'workorders.status', 'workorders.dateSubmitted', 'workorders.dateCompleted', 'student.fname', 'student.lname', 'workorders.assetTag', 'workorders.commonProblem','workorders.grade','workorders.gradYear', 'workorders.school', 'student.uName','student.password','workorders.caseNum','workorders.repairID','workorders.IR', 'computer.serialNumber', 'workorders.dateSubmitted as \'timestamp\'');
    $aColumnsFull = array( 'workorders.WOID', 'workorders.status', 'workorders.dateSubmitted', 'workorders.dateCompleted', 'student.fname', 'student.lname', 'workorders.assetTag', 'workorders.commonProblem','workorders.grade','workorders.gradYear', 'workorders.school', 'student.uName','student.password','workorders.caseNum','workorders.repairID','workorders.IR', 'computer.serialNumber', '\'timestamp\'');
    $selectLists = array('workorders.status','workorders.grade', 'workorders.gradYear','workorders.school');
    $hiddenRowCount = 1; //Adds an empty row to each value set for hidden values used in the DT
    /* Indexed column (used for fast and accurate table cardinality) */
    $sIndexColumn = "WOID";

    $gaSql['link'] = $conn;


    /*
    * Paging
    */
    $sLimit = "";
    if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
    {
    $sLimit = "LIMIT ".mysql_real_escape_string( $_GET['iDisplayStart'] ).", ".
    mysql_real_escape_string( $_GET['iDisplayLength'] );
    }


    /*
    * Ordering
    */
    $sOrder = "";
    if ( isset( $_GET['iSortCol_0'] ) )
    {
    $sOrder = "ORDER BY ";
    for ( $i=0 ; $i $iFilteredTotal,
    "select" => array(),
    "aaData" => array()
    );

    while ( $aRow = mysql_fetch_array( $rResult ) )
    {
    $row = array();
    for ( $i=0 ; $i
This discussion has been closed.