YADCF Server Side

YADCF Server Side

karliekarlie Posts: 83Questions: 17Answers: 0

I know this is a third party plugin, but can anybody show me an example of YADCF being implemented with Editors PHP script? I've seen the example page here http://yadcf-showcase.appspot.com/server_side_source.html but the penny just isn't dropping for me. The YADCF filters show for me, but obviously only show the current page because Server Side isn't configured correctly. I've Googled so much on this but still can't find an example of it being implemented with an Editor PHP script. I'm seriously starting to doubt my own intelligence here, as I'm just not getting it!

«1

Replies

  • kthorngrenkthorngren Posts: 20,150Questions: 26Answers: 4,736

    Doesn't look like the example is pulling all the options for column 0. In the example code there is this comment:

    //In case that you want to populate your select / auto_complete filters with values
    //you have to add to your current JSON the following attributes yadcf_data_0 / yadcf_data_1 / etc'
    //where each attribute contains a list of strings
    //For example: 
    //"yadcf_data_0":["KHTML","Webkit","Trident","Misc","Other browsers","Tasman","Presto","Gecko"],
    

    Looks like, for server side, you will need to populate attribues named yadcf_data_0, where 0 is the column number, with all the options to search that column. It looks like theyadcf_data_0attributes are returned along with theRecordsTotal`, etc.

    Also checkout the "Server-side processing API" section of the docs:
    https://github.com/vedmack/yadcf/blob/master/src/jquery.dataTables.yadcf.js

    Unfortunately I don't have an easy way to try it.

    Kevin

  • kthorngrenkthorngren Posts: 20,150Questions: 26Answers: 4,736
    edited May 2018

    I managed to create a server side example using dataFilter to add the column 3 options. You will do this with your server code. Look at the Office column.

    http://live.datatables.net/tomimadu/1/edit

    Kevin

  • karliekarlie Posts: 83Questions: 17Answers: 0

    Thanks Kevin, it's much appreciated. It's just I don't understand how the PHP needs to be put together for the server side. The doc on Github read

    * From server to client:
    * In order to populate the filters with data from server (select / auto_complete / range_number_slider (min and max values), you should add to your current json respond the following properties:
    * lets say for first column you add yadcf_data_0 filled with array of values, for column second column yadcf_data_1 and so on...
    

    I notice the author tweeted for people to add their own examples of PHP code, which gave me hope, but there was only 1 response, which wasn't that helpful.

  • kthorngrenkthorngren Posts: 20,150Questions: 26Answers: 4,736

    Sorry, I don't use PHP so can't help there but I'm sure someone else will be able to help.

    Kevin

  • karliekarlie Posts: 83Questions: 17Answers: 0

    Thanks anyway Kevin.

  • allanallan Posts: 61,453Questions: 1Answers: 10,055 Site admin

    From an Editor lib point of view you could do something like:

    $data = Editor::inst( $db, 'staff' )
        ->fields(
            Field::inst( 'first_name' ),
            Field::inst( 'last_name' ),
            Field::inst( 'position' ),
            Field::inst( 'email' ),
            Field::inst( 'office' )
        )
        ->process( $_POST )
        ->data();
    
    $data['yadcf_data_0'] = ...;
    
    echo json_encode( $data );
    

    The "trick" then is to get the data from the database. You could use the Editor db libraries for that as well:

    $data['yadcf_data_0'] = $db
      ->selectDistinct( "staff", [ "first_name" ], null, "first_name" )
      ->fetchAll();
    

    Full documentation for the database methods are available here.

    Allan

  • karliekarlie Posts: 83Questions: 17Answers: 0
    edited May 2018

    OK I'm not much closer to a solution. If I put the following code in /php/lib/Editor/Editor.php it throws an error for invalid json response

    $data = Editor::inst( $db, 'mytable' )
        ->fields(
            Field::inst( 'type' ),
            Field::inst( 'category' ),
            Field::inst( 'material_grade' ),
            Field::inst( 'shape_cut' ),
            Field::inst( 'size' )
        )
        ->process( $_POST )
        ->data();
     
    $data['yadcf_data_5'] = $db
      ->selectDistinct( "mytable", [ "type" ], null, "type" )
      ->fetchAll();
     
    echo json_encode( $data );
    

    I want distinct values from 'type', amongst others. Any ideas where I'm going wrong?

  • kthorngrenkthorngren Posts: 20,150Questions: 26Answers: 4,736

    Did you follow the troubleshooting steps provided in the link of the Invalid JSON Response error?
    https://datatables.net/manual/tech-notes/1

    If you are unable to resolve the problem following the steps then please post the response you are getting.

    Kevin

  • karliekarlie Posts: 83Questions: 17Answers: 0
    edited May 2018

    The console error says "datatables xhr.dt event came back with null as data (nothing for yadcf to do with it)."

    The JS looks like this

    var myTable = $('#stones').DataTable();
        yadcf.init(myTable, [
                {
                    column_number: 5,
                    filter_container_id: 'searchmodal',
                    filter_type: "multi_select",
                    select_type: 'chosen'
                }
            ]);
    
  • karliekarlie Posts: 83Questions: 17Answers: 0

    I found this thread https://stackoverflow.com/questions/41507443/yadcf-datatables-server-side-populate-select-with-php which has given me some idea. I now have this code in a separate php file which is then requested by the YADCF js

    <?php
    $db = sql_connect( "my connection details" );
    $stmt1 = $db->prepare( 'SELECT DISTINCT type FROM mytable' );
    $stmt1->execute();
    $data['yadcf_data_6'] = $stmt3->fetchAll(PDO::FETCH_COLUMN, 6);
    

    As I understand it I will need to make separate queries for each YADCF filter. I'm getting some results back, but they're all junk, not even what's in the DB so far as I can tell! I'm seeing no errors in chrome dev tools.

  • karliekarlie Posts: 83Questions: 17Answers: 0

    OK so I have this in my JS file

    var myTable = $('#stones').DataTable();
        yadcf.init(myTable, [
                {
                    column_number: 6,
                    filter_container_id: 'search_modal',
                    filter_type: "multi_select",
                    select_type: 'chosen',
                    filter_default_label: 'Type',
                    data: 'path/to/my/phpfile/yadcf.php'
                }
            ]);
    

    Then my PHP file that I referenced as the data contains the following

    <?php
    $con=mysqli_connect("my connetion details");
    // Check connection
    if (mysqli_connect_errno())
      {
      echo "Failed to connect to MySQL: " . mysqli_connect_error();
      }
    
    $sql="SELECT DISTINCT type FROM mytable";
    $result=mysqli_query($con,$sql);
    

    But all I get back is a load of junk data

  • allanallan Posts: 61,453Questions: 1Answers: 10,055 Site admin

    Can you show us the JSON return from the Editor server-side script please. The link Kevin gave gives instructions on how to get that information in your browser.

    Allan

  • karliekarlie Posts: 83Questions: 17Answers: 0

    This is the response when one of the options is clicked

    {"data":[],"options":[],"files":{"files":{"47":{"id":"47","filename":"2263-a93a6e52aef5281a924e3a7ded81313b.jpg","filesize":"42150","web_path":"\/dtgem\/stonelist\/upload\
    
  • karliekarlie Posts: 83Questions: 17Answers: 0

    Sorry to bang on about this, but I'm going a bit crazy! I have a PHP file which gets the data I need

    <?php
    $servername = "localhost";
    $username = "my_username";
    $password = "my_password";
    
    try {
        $conn = new PDO("mysql:host=$servername;dbname=myDBname", $username, $password);
        // set the PDO error mode to exception
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        echo "Connected successfully"; 
        }
    catch(PDOException $e)
        {
        echo "Connection failed: " . $e->getMessage();
        }
    $stmt = $conn->prepare( 'SELECT DISTINCT type FROM stones' );
    $stmt->execute();
    $data['yadcf_data_6'] = $stmt->fetchAll(PDO::FETCH_COLUMN, 0);
    
    
    echo json_encode($data);
    ?>
    

    If I visit this file in a browser it returns

    {"yadcf_data_6":["PCT-SO","PCT","EACH","","GRAM","BOX",null]}
    

    In my JS I have the following

    $(document).ready(function(){
      $('#stones').dataTable().yadcf([
            {column_number: 6, filter_container_id: 'searchmodal', filter_type: "select", select_type: 'chosen', filter_default_label: 'Type', data: 'path/to/my/php/file/yadcf.php'}]);
    });
    

    Yet I get a crazy response in the browser Ajax response, which is related to image names I use in a separate table! I'm not even referencing the images anywhere in this query!

    {"data":[],"options":[],"files":{"files":{"61":{"id":"61","filename":"trott_poster.jpg","filesize":"125381","web_path":"\/stonelist\/upload\/61.jpg","system_path":"\/home\/server\/office.mydomainname.com\/stonelist\/upload\/61.jpg"},"60":{"id":"60","filename":"3110-e6280a125dd4f38944e981de572d2831.jpg","filesize":"49618","web_path":"\/stonelist\/upload\/60.jpg","system_path":"\/home\/server\/mydomainname.com\/stonelist\/upload\/60.jpg"}}},"draw":2,"recordsTotal":"42720","recordsFiltered":"0"}
    
    

    If this is beyond forum support I understand, but it's just not making any sense to me.

  • allanallan Posts: 61,453Questions: 1Answers: 10,055 Site admin

    If you could link to the page that would really help with my ability to offer some help.

    The JSON data above is incomplete - that might explain an error message, or it might just be that it has been truncated for brevity here.

    {"yadcf_data_6":["PCT-SO","PCT","EACH","","GRAM","BOX",null]}

    That looks fine for YADCF, but there is no data for the DataTable to display there and there is no Ajax configuration for DataTables.

    I would suggest going back to your Editor PHP script and giving me a link to the page so I can take a look at it.

    Allan

  • karliekarlie Posts: 83Questions: 17Answers: 0

    I've PM'd you all the details Allan. Thanks again.

  • daniel_rdaniel_r Posts: 460Questions: 4Answers: 67
    edited May 2018

    just a side note, there is no need to write down data: 'path/to/my/php/file/yadcf.php' in your column config of yadcf, when datatables is configures to work with serverSide yadcf will grab the data from the server automatically (just make sure you are adding it yadcf_data_6 / yadcf_data_N# ...

    and also look at this 1 https://github.com/vedmack/yadcf/issues/206

  • karliekarlie Posts: 83Questions: 17Answers: 0

    Thanks Daniel, I had seen that link from your tweet. Only thing was that I couldn't see any reference to yadcf in the code on that page? (It's probably me missing it).

    I have Editor, and what I'm struggling with is:
    How the query should be structured and where it should be placed, particularly as I will be using 7 or 8 filters and I will need to do a separate query for each.

    I couldn't get it to work so went off at a tangent trying to fetch the data using a query in a separate php file. If I can get it to work using Editor then that's what I want to do ideally.

  • karliekarlie Posts: 83Questions: 17Answers: 0
    edited May 2018

    Allan has very kindly helped me with the server side source for YADCF, however all of my filters are filled with 'undefined'. The browser is receiving the following response in this format:

    "yadcf_data_6":[{"type":null},{"type":""},{"type":"BOX"},{"type":"EACH"},{"type":"GRAM"},{"type":"PCT"},{"type":"PCT-SO"}],
    

    My JS looks like this:

    {
                        column_number: 6,
                        filter_container_id: 'searchtype',
                        filter_type: "multi_select",
                        filter_reset_button_text: "Clear",
                        cumulative_filtering: "true",
                        column_data_type: "text",
                        select_type: 'select2',
                        select_type_options: {
                        'width': '25em',
                        },
                        filter_default_label: 'Type'
                    },
    

    My serverside code looks like this

    $data['yadcf_data_6'] = $db
      ->selectDistinct( "stones", [ "type" ], null, "type" )
      ->fetchAll();
    

    Any ideas why my filters are being filled with undefined?

    When I inspect the dev tools -> Network - > entrys_table_server_side_source of this example http://yadcf-showcase.appspot.com/server_side_source.html I can't see any reference to the yadcf filters I only see this response

    {"data":[{"date":"12/29/2017","number":"1","engine":"Trident","browser":"Internet Explorer 4.0","platform":"Win 95+"},{"date":"9/13/2017","number":"2","engine":"Trident","browser":"Internet Explorer 5.0","platform":"Win 95+"},{"date":"12/11/2017","number":"3","engine":"Trident","browser":"Internet Explorer 5.5","platform":"Win 95+"},{"date":"5/23/2017","number":"4","engine":"Trident","browser":"Internet Explorer 6","platform":"Win 98+"},{"date":"10/1/2017","number":"5","engine":"Trident","browser":"Internet Explorer 7","platform":"Win XP SP2+"},{"date":"9/24/2017","number":"4","engine":"Trident","browser":"AOL browser (AOL desktop)","platform":"Win XP"},{"date":"10/16/2016","number":"3","engine":"Gecko","browser":"Firefox 1.0","platform":"Win 98+ / OSX.2+"},{"date":"5/21/2017","number":"2","engine":"Gecko","browser":"Firefox 1.5","platform":"Win 98+ / OSX.2+"},{"date":"11/3/2017","number":"1","engine":"Gecko","browser":"Firefox 2.0","platform":"Win 98+ / OSX.2+"},{"date":"4/28/2016","number":"2","engine":"Gecko","browser":"Firefox 3.0","platform":"Win 2k+ / OSX.3+"}],"draw":"1","recordsTotal":57,"recordsFiltered":10}
    

    Any advice would be appreciated. Thanks.

  • kthorngrenkthorngren Posts: 20,150Questions: 26Answers: 4,736

    Instead of an array of objects:

    "yadcf_data_6":[{"type":null},{"type":""},{"type":"BOX"},......

    I think you want an array of values, like this:
    "yadcf_data_6":[null,"","BOX", "EACH", "GRAM", "PCT",.....

    You can see this in the example I posted above:
    http://live.datatables.net/tomimadu/1/edit

    Kevin

  • karliekarlie Posts: 83Questions: 17Answers: 0

    Thanks Kevin, but the author of the plugin says array of objects here: https://stackoverflow.com/questions/29347919/passing-from-server-select-values-and-labels

  • kthorngrenkthorngren Posts: 20,150Questions: 26Answers: 4,736

    Then it sounds like you need to use value/label format as shown in his example:

    yadcf_data_0: [{value: "Trident", label: "Trident Eng'"}, {value: "Tasman", label: "Tasman Eng'"},…]

    Kevin

  • kthorngrenkthorngren Posts: 20,150Questions: 26Answers: 4,736
    edited May 2018

    I updated my example to show both an array of values and an array of objects using label / value. Seems like either works.
    http://live.datatables.net/tomimadu/4/edit

    Kevin

  • karliekarlie Posts: 83Questions: 17Answers: 0

    Thanks Kevin, I get it from your example when it's written into the JS, but I just can't work out how to implement it when I'm pulling the data from serverside using this:

    $data['yadcf_data_6'] = $db
      ->selectDistinct( "stones", [ "type" ], null, "type" )
      ->fetchAll();
    
  • kthorngrenkthorngren Posts: 20,150Questions: 26Answers: 4,736
    edited May 2018

    Like I said before I'm not familiar with PHP nor the provided libraries. But taking the example in this thread:
    https://datatables.net/forums/discussion/comment/62043/#Comment_62043

    Maybe you can try something like this:

    $data['yadcf_data_6'] = $db
      ->selectDistinct( "stones", "type as value, type as label", null, "type" )
      ->fetchAll();
    

    Hopefully someone can post a more efficient way for you to get the data structure needed.

    Kevin

  • karliekarlie Posts: 83Questions: 17Answers: 0

    Kevin, you're an absolute star, not sure if that's the most efficient way, but it works. Thank you!

  • allanallan Posts: 61,453Questions: 1Answers: 10,055 Site admin

    Yup - that's about as efficient as it gets :).

    Allan

  • karliekarlie Posts: 83Questions: 17Answers: 0
    edited May 2018

    Many thanks Allan, Kevin and especially Daniel_R, they're amazing when dealing with such a large amount of data. Any plans to make these a core part of Datatables? Or is this where you see SearchPane going?

  • allanallan Posts: 61,453Questions: 1Answers: 10,055 Site admin

    Agreed - YADFC is a fantastic component of the DataTables ecosystem. I have plans ti improve DataTables ability to do filtering (better APIs) which might be of use to YADFC and other plug-ins, but at the moment there is no plan to integrate YADFC into DataTables core.

    Allan

  • karliekarlie Posts: 83Questions: 17Answers: 0
    edited May 2018

    I can't seem to get cumulative_filtering to work. I have followed the example here:
    http://yadcf-showcase.appspot.com/cumulative_filtering.html

    My JS looks like this

      // YADCF filters Modal    
            $(document).ready(function () {
        'use strict';
     
        var oTable;
        oTable = $('#stones').DataTable();
     
        yadcf.init(oTable,
            [{
                        column_number: 6,
                        filter_container_id: 'searchtype',
                        filter_type: "select",
                        filter_reset_button_text: "Clear",
                        select_type: "chosen",
                        select_type_options: {
                        'width': '50em',
                        },
                        filter_default_label: 'Type'
                    },
                    {
                        column_number: 7,
                        filter_container_id: 'searchcategory',
                        filter_type: "select",
                        filter_reset_button_text: "Clear",                  
                        select_type: "chosen",
                        select_type_options: {
                        'width': '50em',
                        },
                        filter_default_label: 'Category'
                    },
                    {
                        column_number: 8,
                        filter_container_id: 'searchmaterial',
                        filter_type: "select",
                        filter_reset_button_text: "Clear",
                        select_type: "chosen",
                        select_type_options: {
                        'width': '50em',
                        },
                        filter_default_label: 'Material'
                    },
                    {
                        column_number: 9,
                        filter_container_id: 'searchgrade',
                        filter_type: "select",
                        filter_reset_button_text: "Clear",
                        select_type: "chosen",
                        select_type_options: {
                        'width': '50em',
                        },
                        filter_default_label: 'Grade'
                    },
                    {
                        column_number: 10,
                        filter_container_id: 'searchshape',
                        filter_type: "select",
                        filter_reset_button_text: "Clear",
                        select_type: "chosen",
                        select_type_options: {
                        'width': '50em',
                        },
                        filter_default_label: 'Shape'
                    },
                    {
                        column_number: 11,
                        filter_container_id: 'searchcut',
                        filter_type: "select",
                        filter_reset_button_text: "Clear",
                        select_type: "chosen",
                        select_type_options: {
                        'width': '50em',
                        },
                        filter_default_label: 'Cut'
                    },
                    {
                        column_number: 12,
                        filter_container_id: 'searchsize',
                        filter_type: "text",
                        filter_reset_button_text: "Clear",
                        select_type: "chosen",
                        filter_match_mode: 'exact',
                        select_type_options: {
                        'width': '50em',
                        },
                        filter_default_label: 'Size'
                    },
                    {
                        column_number: 29,
                        filter_container_id: 'searchpairs',
                        filter_type: "select",
                        filter_reset_button_text: "Clear",
                        select_type: "chosen",
                        select_type_options: {
                        'width': '50em',
                        },
                        filter_default_label: 'Pairs'
                    },
                    {
                        column_number: 31,
                        filter_container_id: 'searchset',
                        filter_type: "select",
                        filter_reset_button_text: "Clear",
                        select_type: "chosen",
                        select_type_options: {
                        'width': '50em',
                        },
                        filter_default_label: 'Sets'
                    }
                ],
            {
                cumulative_filtering: true
            }
        );
         
    });
    

    The filters all populate and I can filter the data just fine, however none of the other filters update when a value is selected in another

    YADCF version 0.9.3 With Chosen 1.8.5 Have tried without Chosen as well in case there was a conflict, to no avail.

This discussion has been closed.