YADCF Server Side

karlie

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!

kthorngren

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

kthorngren

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

karlie

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.

kthorngren

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

Kevin

karlie

Thanks anyway Kevin.

allan

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

karlie

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?

kthorngren

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

karlie

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'
            }
        ]);

karlie

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.

karlie

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

allan

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

karlie

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\

karlie

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

<?php
>
```
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.

allan

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

karlie

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

daniel_r

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

karlie

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.

karlie

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.

kthorngren

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

karlie

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

kthorngren

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

kthorngren

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

karlie

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();

kthorngren

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

karlie

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

allan

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

Allan

karlie

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?

allan

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

karlie

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.