Monday, August 29, 2011

server_processing.php for DataTables

This content has moved

http://sugar.armed.us/2013/06/14/server_processing-php-for-datatables/

11 comments:

  1. The PHP is self-explanatory, my problem comes from getting the filter select lists to be filled with distinct list from the DB.

    If I edit sServer_0= to sServer_2=test, the script returns only those records where the 2nd row is LIKE test. No problem at all, I understand the concept... but I just don't know HOW you populate the select boxes so that a user can select a filter... just doesn't click with me.

    I have read over the discussions, and have posted a new discussion on this, and haven't received a reply (although datatables.net is down atm). I have gathered that there should be an array in the JSON named select. I have tried hardcoding, for testing purposes, an array into the JSON that is generated by server_processing.php, but I can't seem to figure out the format.

    Can you post a thread about the JSON returns and how they are supposed to be formatted?

    The main site is still down, so I am not sure if I have received a response to my forum post, so I figured I would comment here.

    Thanks,
    Drew

    ReplyDelete
  2. Edit:

    "those records where the 2nd row is LIKE test"

    row should be column...

    Drew

    ReplyDelete
  3. If I understand your question properly, what you probably want to do is get all unique values for a column (field) so it can be used in a drop-down list filter on the client side.

    You would run a separate query such as:
    "Select distinct $columnname from $table " and maybe sort it with " order by $columnname";

    the results can be glommed in a loop similar to the loop in the existing code to fill out an array of values.

    that array can either be added to your $output object as is, or you can convert it directly to HTML code as the select tag.

    if your list is created in array $aList:
    $output = array(
    "sEcho" => intval($_GET['sEcho']),
    "iTotalRecords" => $iTotal,
    "iTotalDisplayRecords" => $iFilteredTotal,
    "aaData" => array(),
    "aList" => $aList
    );

    ReplyDelete
  4. or if you want to turn it into HTML
    $select = "";
    foreach($aList as $listitem) {
    $select .= "<option value='$listitem'>$listitem</option>";
    }

    $output = array(
    "sEcho" => intval($_GET['sEcho']),
    "iTotalRecords" => $iTotal,
    "iTotalDisplayRecords" => $iFilteredTotal,
    "aaData" => array(),
    "sSelect" => $select
    );
    on the client side, you can use the success callback in $.ajax to get data (either the array or formatted HTML) from the JSON response


    $.ajax( {
    "dataType": 'json',
    "type": "POST",
    "url": sSource,
    "data": aoData,
    "success": function(data, textStatus, jqXHR) {
    // data is the JSON response
    $('#whatever_id').html(data.sSelect);

    // call DataTable's callback function for everything else
    fnCallback(data, textStatus, jqXHR);
    }
    } );

    ReplyDelete
  5. (that last code is in the fnServerData callback)

    ReplyDelete
  6. FBas,

    I really appreciate your reply... I *finally* got my app working properly, and I posted back to my original thread on the datatables forum...

    For anyone who is having difficulty getting the select boxes to work with the server-side scripts, go to the thread here,

    http://www.datatables.net/forums/discussion/6350/server-side-individual-column-filtering#Item_5

    I hope this helps!
    Drew

    ReplyDelete
  7. FBas I need your help, my server side individual column filtering still don't work. I think only you can help... http://datatables.net/forums/discussion/6350/server-side-individual-column-filtering#Item_9

    ReplyDelete
  8. Driver, sorry I didn't see this sooner. I did reply in the thread.

    ReplyDelete
  9. How can I use a INNER JOIN to list data from other tables ??

    ReplyDelete
  10. @WDevel same as you would when writing SQL at command line or elsewhere. edit the $sQuery variable

    /*
    * SQL queries
    * Get data to display
    */
    $sQuery = "
    SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))."
    FROM $sTable
    INNER JOIN $sTable2
    ON " . $sTable . "." . $sJoinCol . "=" . $sTable2 . "." . $sJoinCol2 . "
    $sWhere
    $sOrder
    $sLimit
    ";
    $rResult = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());

    ReplyDelete
  11. Looks nice, but how to build a query in my case? I have a problem with A.*, which returns all columns from database, but not from array ($aColumns).

    SELECT
    A.*,
    B.option AS element_4_value,
    C.option AS element_22_value
    FROM
    (ap_form_3 AS A
    LEFT JOIN ap_element_options AS B
    ON A.element_4=B.option_id AND B.element_id=4 AND B.form_id=3)
    LEFT JOIN ap_element_options AS C
    ON A.element_22=C.option_id AND C.element_id=22 AND C.form_id=3
    ";

    ReplyDelete