add/edit/delete on dataTables.

nageshjoshi

Hello,

I am working on dataTables from last 15days. I have posted 2,3 questions but didn't get any response.

Let me brief you about my application:
I am making one web-service in php with the help of knockoutjs and some other libraries.
Now, I have exposed api for each application which provides me the array of json objects. I want to implement the dataTable for that data(which I am getting by sending ajax get request to respective api url). Also, I want to add/edit/delete that data without refreshing the page. I am using knockout to achieve this purpose. But unable to use knockout observable array with dataTables properly.

Can anybody please guide me through this?

I have mentioned my json response from ajax get request and method with which I am loading that data to knockout observable array. Please let me know if I am missing some parameters from my json response.

{"sEcho":"1","iTotalRecords":3,"iTotalDisplayRecords":3,"aaData":[{"cat_clientId":"000","categoryId":"333","categoryName":"INDIVIDUAL","categoryDescription":"","cat_last_activity":"01\/08\/2014 12:54:11 admin"},{"cat_clientId":"000","categoryId":"334","categoryName":"FIRM","categoryDescription":"","cat_last_activity":"01\/08\/2014 14:04:28 admin"},{"cat_clientId":"000","categoryId":"335","categoryName":"PRIVATE LIMITED COMPANY","categoryDescription":"","cat_last_activity":"01\/08\/2014 13:03:42 admin"}]}

http://jsfiddle.net/43huY/

allan

I'd suggest doing a search on the forum for "Knockout" - there are a few integration efforts might might be of some help, but there isn't an "official" integration option yet.

Allan

nageshjoshi

Thanks allan for your response but can you please guide me through various problems that I am facing with dataTables?
Is there anything wrong with my json object or parameters?

allan

The JSON looks fine - but I would suggest that you should use columns.data to tell DataTables what properties to read from the data source.

Allan

nageshjoshi

Thanks a lot Allan.. It is working as desired.
But how can I place edit/delete link for each row within the dataTable?
I went through the documentation of columns.data() and columns.render() but it didn't work out.
Here is my jsfiddle link:

http://jsfiddle.net/xD3t3/4/

Also, when I use server side processing(commented code in the above link) every time I search through the table nothing appears except processing message.
Do I need to use separate settings for it?

Thanks again.

allan

The columns.defaultContent would probably be used, unless the links need to be dynamic, in which case columns.render would be used.

One of the Editor examples uses the columns.defaultContent method: https://editor.datatables.net/examples/simple/inTableControls.html .

Allan

nageshjoshi

thank you very much allan.. working perfectly fine.. But what about server side processing? I can see the table when I am using server side processing. But search and other capabilities are not working.

Thanks again!

allan

Have you fully implement search and sort capabilities in the server-side script, as per the manual?

Allan

nageshjoshi

I read the server side example and its parameter details. But couldn't able to figure out what should I do for searching and sorting. I haven't made any capabilities other than providing ajax data source. Can you please guide me through this?

tester1

If u using any JOIN then u have to check " where / and " condition properly in u r search and join query ...

allan

Can you please guide me through this?

The manual explains everything needed. Specially, look at the parameters sent and those returned. You need to process the parameters being sent (which includes the search information) to return the required data.

Allan

nageshjoshi

Hello Allan,
I have seen those parameter, my response array contains all the parameters mentioned in the provided link.
But I am really confused about parameters sent to the server as mentioned by you.
Where can I get the search.value or length parameter?
Also, how can I specify those parameters?

http://jsfiddle.net/xD3t3/6/

is this the correct way?

allan

Where can I get the search.value or length parameter?

They are sent by DataTables as HTTP parameters. So you would use whatever method your server-side environment has to read those variables.

Question: Do you actually need server-side processing? Are you working with a table that has 50,000 or more rows in it?

Allan

nageshjoshi

Yes Allan,

I am using 4 dataTables in my whole project. On rest of the cases I have using client side processing but for transaction table there could be more than 100000 records.
That is why I need to use server side processing there.

Thanks

allan

Okay good - then you really need to follow the protocol describe in the manual then.

Allan

nageshjoshi

One last doubt allan,

The search value parameter in server sent parameters is the value that I am entering in the dataTable search box right?
So, how can I get that value for dynamically generated dataTable.
Thanks.

allan

I don't see why it would make any difference if the DataTable was dynamically generated or statically? The server wouldn't see any difference.

Allan

nageshjoshi

Allan, can you please provide me one example of server side processing with all the necessary parameters for searching and sorting.
The example mentioned here has only 3 parameters:

$(document).ready(function() {
$('#example').dataTable( {
"processing": true,
"serverSide": true,
"ajax": "ajax url"
} );
} );

Thanks in advance.

allan

I've linked you to the documentation which includes all of the parameters that are sent to the server several times.

The parameters you list are DataTables initialisation options.

There is also a live example, showing server-side processing in action here: http://datatables.net/examples/server_side/simple.html

Allan

nageshjoshi

Thanks a lot allan for your support.
Thank you once again.

nageshjoshi

Hey Allan, thanks a lot.. I have successfully implemented server side processing with all the functionalities such as searching and sorting.

But there is a slight problem in my case,
The records I want to display in the dataTable are not from on database Table, I am performing join,concat,alias on 3 database tables and forming one result array.

As we need to provide database connection details, database table name, column name in server side processing, I cannot achieve the desired goal through it.

How can I achieve this?
Is there any provision that, instead of giving database details in server side processing parameters can I provide my query result array?

Thanks

allan

I'm not sure I understand why you can't achieve what you want using server-side processing? It sounds like you just need to make the SQL that your script is generating more complex by adding the options you want.

Allan

tester1

<?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)
     */
    $aColumns = array( 'ajax.engine', 'ajax.browser', 'ajax2.platform', 'ajax2.version', 'ajax2.grade' );
    
    /* Indexed column (used for fast and accurate table cardinality) */
    $sIndexColumn = "ajax.id";
    
    /* DB table to use */
    $sTable = "ajax, ajax2";
    
    /* Join condition */
    $sJoin = "ajax.id = ajax2.id";
    
    /* Database connection information */
    $gaSql['user']       = "";
    $gaSql['password']   = "";
    $gaSql['db']         = "";
    $gaSql['server']     = "";
    
    
    /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     * If you just want to use the basic configuration for DataTables with PHP server-side, there is
     * no need to edit below this line
     */
    
    /* 
     * MySQL connection
     */
    $gaSql['link'] =  mysql_pconnect( $gaSql['server'], $gaSql['user'], $gaSql['password']  ) or
        die( 'Could not open connection to server' );
    
    mysql_select_db( $gaSql['db'], $gaSql['link'] ) or 
        die( 'Could not select database '. $gaSql['db'] );
    
    
    /* 
     * Paging
     */
    $sLimit = "";
    if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
    {
        $sLimit = "LIMIT ".intval( $_GET['iDisplayStart'] ).", ".
            intval( $_GET['iDisplayLength'] );
    }
    
    
    /*
     * Ordering
     */
    $sOrder = "";
    if ( isset( $_GET['iSortCol_0'] ) )
    {
        $sOrder = "ORDER BY  ";
        for ( $i=0 ; $i<intval( $_GET['iSortingCols'] ) ; $i++ )
        {
            if ( $_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == "true" )
            {
                $sOrder .= $aColumns[ intval( $_GET['iSortCol_'.$i] ) ]."
                    ".($_GET['sSortDir_'.$i]==='asc' : 'asc' : 'desc') .", ";
            }
        }
        
        $sOrder = substr_replace( $sOrder, "", -2 );
        if ( $sOrder == "ORDER BY" )
        {
            $sOrder = "";
        }
    }
    
    
    /* 
     * Filtering
     * NOTE this does not match the built-in DataTables filtering which does it
     * word by word on any field. It's possible to do here, but concerned about efficiency
     * on very large tables, and MySQL's regex functionality is very limited
     */
    $sWhere = "";
    if ( isset($_GET['sSearch']) && $_GET['sSearch'] != "" )
    {
        $sWhere = "WHERE (";
        for ( $i=0 ; $i<count($aColumns) ; $i++ )
        {
            $sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ";
        }
        $sWhere = substr_replace( $sWhere, "", -3 );
        $sWhere .= ')';
    }
    
    /* Individual column filtering */
    for ( $i=0 ; $i<count($aColumns) ; $i++ )
    {
        if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' )
        {
            if ( $sWhere == "" )
            {
                $sWhere = "WHERE ";
            }
            else
            {
                $sWhere .= " AND ";
            }
            $sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string($_GET['sSearch_'.$i])."%' ";
        }
    }
    
    // Add the join
    if ( $sWhere == "" ) {
        $sWhere = "WHERE $sJoin";
    }
    else {
        $sWhere = " AND $sJoin";
    }
    
    /*
     * Select list
     */
    $sSelect = "";
    for ( $i=0 ; $i<count($aColumns) ; $i++ )
    {
        $sSelect .= $aColumns[$i] .' as `'.$aColumns[$i].'`, ';
    }
    $sSelect = substr_replace( $sSelect, "", -2 );
  
    
    /*
     * SQL queries
     * Get data to display
     */
    $sQuery = "
        SELECT SQL_CALC_FOUND_ROWS $sSelect
        FROM   $sTable
        $sWhere
        $sOrder
        $sLimit
    ";
    // echo $sQuery;
    $rResult = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
    
    /* Data set length after filtering */
    $sQuery = "
        SELECT FOUND_ROWS()
    ";
    $rResultFilterTotal = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
    $aResultFilterTotal = mysql_fetch_array($rResultFilterTotal);
    $iFilteredTotal = $aResultFilterTotal[0];
    
    /* Total data set length */
    $sQuery = "
        SELECT COUNT(".$sIndexColumn.")
        FROM   $sTable
        WHERE  $sJoin
    ";
    $rResultTotal = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
    $aResultTotal = mysql_fetch_array($rResultTotal);
    $iTotal = $aResultTotal[0];
    
    
    /*
     * Output
     */
    $output = array(
        "sEcho" => intval($_GET['sEcho']),
        "iTotalRecords" => $iTotal,
        "iTotalDisplayRecords" => $iFilteredTotal,
        "aaData" => array()
    );
    
    while ( $aRow = mysql_fetch_array( $rResult ) )
    {
        $row = array();
        for ( $i=0 ; $i<count($aColumns) ; $i++ )
        {
            if ( $aColumns[$i] == "version" )
            {
                /* Special output formatting for 'version' column */
                $row[] = ($aRow[ $aColumns[$i] ]=="0") ? '-' : $aRow[ $aColumns[$i] ];
            }
            else if ( $aColumns[$i] != ' ' )
            {
                /* General output */
                $row[] = $aRow[ $aColumns[$i] ];
            }
        }
        $output['aaData'][] = $row;
    }
    
    echo json_encode( $output );

<?php
>
?>