Return the result of raw SQL query as extra variable with JSON object

Khalid Teli

Hi,
I am trying to return the result of raw SQL query with the JSON object as an additional parameter. The idea is to get this result and populate the dropdown menu in column filtering for server side = true,in this example
https://datatables.net/examples/api/multi_filter_select.html

In the code belwo, on $_REQUEST['draw']) == 1) , I am usingSQL query to return distinct values for a column.

              <?php
        include( "../../lib/DataTables.php" );
        use
            DataTables\Editor,
            DataTables\Editor\Field,
            DataTables\Editor\Format,
            DataTables\Editor\Mjoin,
            DataTables\Editor\Options,
            DataTables\Editor\Upload,
            DataTables\Editor\Validate,
            DataTables\Editor\ValidateOptions,
              DataTables\Editor\SearchPaneOptions;

        $editor  =  Editor::inst( $db, 'crg_contracts', 'contract_id' )
            ->field(
                Field::inst( 'crg_products.product_code', 'product_code'),
                Field::inst( 'crg_contracts.member_name' ,'member_name')
                                                     ->searchPaneOptions( SearchPaneOptions::inst() ),
                Field::inst( 'crg_contracts.start_date' ,'start_date')
                                                             ->searchPaneOptions( SearchPaneOptions::inst() ),
                Field::inst( 'crg_contracts.end_date' ,'end_date')
                                                             ->searchPaneOptions( SearchPaneOptions::inst() ),
                Field::inst( 'supplier.supplier_name' ,'supplier_name'),
                Field::inst( 'crg_contracts.contract_prod' ,'contract_prod')
                ->searchPaneOptions( SearchPaneOptions::inst() ),
        );

             if (intval($_REQUEST['draw']) == 1) {

                  $rawquery = "SELECT DISTINCT contract_prod as contractProd FROM crg_contracts";
           $data = $db->sql( $rawquery )->fetchAll();
        echo json_encode( [
          "data" => $data
        ] );


             }
         $editor

             ->leftJoin( 'crg_products', 'crg_products.product_id', '=', 'crg_contracts.product_id_fk' )
                         ->leftJoin( 'supplier', 'supplier.supplier_id', '=', 'crg_products.supplier_id_fk' )
            ->process( $_POST )
            ->json();

** This it is returning two separate JSON Objects and hence giving parse error. How can I pass the result of SQL query with the JSON object as an additional parameter to the client side and access the variable from there ?**

Thank you

allan

Yes, you can't echo two different JSON objects, you need to just have one.

Rather than using ->json() you want to do:

$json = $editor
  ->leftJoin(...)
  ->process(...)
  ->data();

That will give you the array of data that Editor would normally echo back to the client-side in the $json variable. You can then manipulate that as you need - e.g.

$json['extraData'] = $db->sql( $rawquery )->fetchAll();

Then:

echo json_encode( $json );

Allan

Khalid Teli

@allan
Happy New Year and thank you. That worked perfectly.

Echo'd data back to the client side and received the data inside initcomplete function and the populated the dropdown with this data using:

        initComplete: function(settings, json) {
    var json = json.dropdowndata;
        var one = document.getElementById('col3_filter');

        for (var i = 0; i < json.length; i++) {
            one.innerHTML = one.innerHTML +
                '<option value="' + json[i]['contractProd'] + '">' + json[i]['contractProd'] + '</option>';

        }

  },

Khalid Teli

@allan
while I was implementing this using the example given on the website : https://datatables.net/examples/api/multi_filter_select.html

First thing is I got extra data from serverside 'dropdowndata'using
if (isset($_POST['draw']) && intval($_REQUEST['draw']) == 1) {

    $rawquery = "SELECT DISTINCT contract_prod as contractProd, start_date as startDate FROM contracts";

   $json['dropdowndata'] = $db->sql( $rawquery )->fetchAll();

     }

once I receive this data , I use this data to manually populate two of my columns (3 and 5) in the table using:

 initComplete: function(settings, json) {

    var json = json.dropdowndata;

      for (var i in columnsToSearch ) {
                  var api = this.api();
                  var selectone = $('<select><option value=""></option></select>')
                    .appendTo( $('#' + columnsToSearch[3]).empty().text(columnsToSearch[3] + ': ') )
                    .attr('data-col', 3)
                    .on( 'change', function () {
                        var val = $.fn.dataTable.util.escapeRegex(
                            $(this).val()
                        );
                  api.column($(this).attr('data-col'))
                    .search(this.value)
                    .draw();

                  } );

                var selecttwo = $('<select><option value=""></option></select>')
                    .appendTo( $('#' + columnsToSearch[5]).empty().text(columnsToSearch[5] + ': ') )
                    .attr('data-col', 5)
                    .on( 'change', function () {
                        var val = $.fn.dataTable.util.escapeRegex(
                            $(this).val()
                        );
                  api.column($(this).attr('data-col'))
                    .search(this.value)
                    .draw();

                  } );


             for (var i = 0; i < json.length; i++) {
          selectone.append('<option value="' + json[i]['contractProd'] + '">' + json[i]['contractProd'] + '</option>');

      }      
                for (var i = 0; i < json.length; i++) {
          selecttwo.append('<option value="' + json[i]['startDate'] + '">' + json[i]['startDate'] + '</option>');

      }          
                }

  },

It works as expected. However, the issue is that I want to implement depedent/cascase filtering.

Currently, when I select a product it gives me the list of all start dates. However, I want to only display the start dates for the selected product.

Please give me some suggestions, how should I proceed with that.

Thank you

Khalid Teli

Hi @allan
Please ignore my previous comments.

I did some research and almost reached to destination.
What I did is
1) from client side: got the value (contract product ) from dropdown and passed it to the server using

"data": function ( d ) {
                d.id =  $('#col3_filter option:selected').val() ;
                id = d.id;

            }

2) used raw SQL query to get the start_date and end_date for this product and echoded data backto client side using
if (isset($_POST['id']))
{

$abc = $_POST['id'] ;

  $rawquerystart = "SELECT DISTINCT start_date as start_date FROM contracts where contract_prod = '$abc' ";
  $rawqueryend = "SELECT DISTINCT end_date as end_date FROM contracts where contract_prod = '$abc' ";

    $json['dropdowndatastart'] = $db->sql( $rawquerystart )->fetchAll();
     $json['dropdowndataend'] = $db->sql( $rawqueryend )->fetchAll();

}

3) on the client side used drawcallbackfunction , to get the start_date and end_date for each product selected vis dropdown and passed on to server

      "drawCallback": function () {
          var json = table.ajax.json();
        var jsonone = json.dropdowndataone;
        var jsontwo = json.dropdowndatastart;
        var jsonthree = json.dropdowndataend;

         console.log(jsonone);
         console.log(jsontwo);
         console.log(jsonthree);


            var one = document.getElementById('col3_filter');
            var two = document.getElementById('col5_filter');
            var three = document.getElementById('col6_filter');

            for (var i = 0; i < jsonone.length; i++) {
                one.innerHTML = one.innerHTML +
                    '<option value="' + jsonone[i]['contractProd'] + '">' + jsonone[i]['contractProd'] + '</option>';

            }
            for (var i = 0; i < jsontwo.length; i++) {

                 two.innerHTML = two.innerHTML +
                     '<option value="' + jsontwo[i]['dropdowndatastart'] + '">' + jsontwo[i]['dropdowndatastart'] + '</option>';

            }
            for (var i = 0; i < jsonthree.length; i++) {

                              three.innerHTML = three.innerHTML +
                     '<option value="' + jsonthree[i]['dropdowndataend'] + '">' + jsonthree[i]['dropdowndataend'] + '</option>';


            }

                   },

4) the output of console.log(jsonone) ,console.log(jsontwo) ,console.log(jsonthree), looks like following

5) I want to take the output of var jsonone and populate var jsontwo and jsonthree based on this selection.
However, the problem I am facing is that after each draw the dropdown menu list is reset. How can I populate the dropdown with these variables after each draw?

Thank you

allan

Good luck with the cascade. We found that to be easily the most complicated part of SearchPanes!

However, the problem I am facing is that after each draw the dropdown menu list is reset. How can I populate the dropdown with these variables after each draw?

I don't quite understand the question I'm afraid. It looks like you are adding extra HTML to the options already? Although I would strongly suggest not using innerHTML for it - add a new Option to it. See the MDN documentation. It looks like you might also need to remove all existing options first?

Allan