Monday, August 29, 2011

server_processing.php for DataTables

This content has moved


  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 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.


  2. Edit:

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

    row should be column...


  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

  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

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

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

  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,

    I hope this helps!

  7. FBas I need your help, my server side individual column filtering still don't work. I think only you can help...

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

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

  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 . "
    $rResult = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());

  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).

    B.option AS element_4_value,
    C.option AS element_22_value
    (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