POST values to SSP and get return results

POST values to SSP and get return results

shrogersshrogers Posts: 11Questions: 2Answers: 0

I have a form with 3 input fields that I would like to send to ssp.class a filter and return the results. I do not want to load DataTables on page load as it does right now.

Any idea how to approach or directions to samples. I pretty new to Datatables.

Replies

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

    See if deferLoading does what you want.

    Kevin

  • shrogersshrogers Posts: 11Questions: 2Answers: 0

    Thank you but that doesn't do what I need.

    When I enter the date and status in the form and submit, it suppose to bring back results that fit the two parameters.

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

    You can use ajax.data to send parameters to the server. There are a couple examples of this using functions in the docs.

    Kevin

  • shrogersshrogers Posts: 11Questions: 2Answers: 0
    edited September 2020

    I tried ajax.data but still returning all the data. Is there something wrong in my code?

    <script type="text/javascript" class="init">
        
        
    $(document).ready(function() {
    
        $('#carddetails').DataTable( {
            "processing": true,
            "serverSide": true,
            "ajax": {
                "url": "../processing/reportsearch.php",
                "data": {
                    "CardStat": 01
                }
            },
            "columnDefs": [ 
            { "bVisible": false, 
              "aTargets": [8] 
            },
                {
                "targets": -1,
                "data": null,
                "defaultContent": '<div class="btn-group"> <form action="../cardman/cardDetails?is=yes" method="post"><input name="cPan" type="hidden" id="cPan" value=""/><button type="submit" id="search" class="btn btn-info btn-xs dt-view" style="margin-right:16px;"><span class="glyphicon glyphicon-eye-open glyphicon-info-sign" aria-hidden="true"></span></button></form>  <form><button type="button" class="btn btn-primary btn-xs dt-edit" style="margin-right:16px;"><span class="glyphicon glyphicon-print" aria-hidden="true"></span></button></form></div>'
            }
            ],
            //"aoColumnDefs": [{ "bVisible": false, "aTargets": [8] }]
            "dom": 'Bfrtip',
            "buttons": [
                               {
                                   "extend": 'excel',
                                   "text": '<i class="fa fa-file-excel-o" style="color: green;"></i>',
                                   "titleAttr": 'Excel',                               
                                   "action": newexportaction
                               },
                               {
                                   "extend": 'csv',
                                   "text": '<i class="fa fa-file-text-o" style="color: green;"></i>',
                                   "titleAttr": 'CSV',                               
                                   "action": newexportaction
                               },
                               {
                                   "extend": 'pdf',
                                   "text": '<i class="fa fa-file-pdf-o" style="color: green;"></i>',
                                   "titleAttr": 'PDF',                               
                                   "action": newexportaction
                               }
                            ]
        } );
    } );
    </script>
    

    Edited by Colin - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    It looks like you're just sending a single constant in ajax.data, so it won't filter between two values. You would need to send both values, and then have the server script use the values in its query.

    Colin

  • shrogersshrogers Posts: 11Questions: 2Answers: 0

    Thank you Colins.
    I'm new to this and not clear. I followed one of the examples under ajax.data.

    Can you elaborate a little or guide me to samples?

  • shrogersshrogers Posts: 11Questions: 2Answers: 0
    edited September 2020

    Need some assistance if possible.
    I have this code but when I load my page comes up blank.
    Is there something I missing?

    <script type="text/javascript" class="init">
    
    $(document).ready(function() {
        
    $("#filter-button").click(function() {
     $(this).toggleClass("active");
      $('#filter-body').slideToggle(100);
    });
    
    //$('#carddetails').DataTable();
    fill_datatable();
        
    function fill_datatable(bin = '', branch = '')
        {
            var dataTable = $('#carddetails').DataTables({
                "processing": true,
                "serverSide": true,
                "ajax": {
                     url: "../processing/reportsearch.php",
                    type: "POST",
                    data:{
                        bin:bin, branch:branch
                    }
                },
                "columnDefs": [
                    { 
                        "bVisible": false, 
                        "aTargets": [0] 
                    },
                        {
                        "targets": -1,
                        "data": null,
                        "defaultContent": '<div class="btn-group"> <form action="../cardman/cardDetails?is=yes" method="post"><input name="cPan" type="hidden" id="cPan" value=""/><button type="submit" id="search" class="btn btn-info btn-xs dt-view" style="margin-right:16px;"><span class="glyphicon glyphicon-eye-open glyphicon-info-sign" aria-hidden="true"></span></button></form>  <form><button type="button" class="btn btn-primary btn-xs dt-edit" style="margin-right:16px;"><span class="glyphicon glyphicon-print" aria-hidden="true"></span></button></form></div>'
                    }
                ],
                "dom": 'Bfrtip',
                "buttons": [
                               {
                                   "extend": 'excel',
                                   "text": '<i class="fa fa-file-excel-o" style="color: green;"></i>',
                                   "titleAttr": 'Excel',                               
                                   "action": newexportaction
                               },
                               {
                                   "extend": 'csv',
                                   "text": '<i class="fa fa-file-text-o" style="color: green;"></i>',
                                   "titleAttr": 'CSV',                               
                                   "action": newexportaction
                               },
                               {
                                   "extend": 'pdf',
                                   "text": '<i class="fa fa-file-pdf-o" style="color: green;"></i>',
                                   "titleAttr": 'PDF',                               
                                   "action": newexportaction
                               }
                            ]
            });
        }
    
        $('#btnsearch').click(function(){
            var cardNum = $('#cardNum').val();
            var bin = $('#bin').val();
            if(cardNum != '' && bin != '')
            {
                
                $('#carddetails').DataTable().destroy();
                fill_datatable(cardNum, bin);
                
            }else{
                
                alert('Select both filter option');
                $('#carddetails').DataTable().destroy();
                fill_datatable();
            }
        });
    } );
    </script>
    
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Can you use the debugger (https://debug.datatables.net) on your page to give me a trace please - click the Upload button and then let me know what the debug code is.

    Or even better would be a link to the page showing the issue.

    Thanks,
    Allan

  • shrogersshrogers Posts: 11Questions: 2Answers: 0

    Thank you Allan.

    See the URL below. Also added the reportsearch.php file used for getting the ajax data.

    http://www.meimeisfashion.com/cardgen/reports/cardsearch.php

    <?php if(isset($_POST['btnSearch'])){ $bin = $_POST['bin']; $branch = $_POST['branch']; $SearchString = ""; //Branch if(!empty($_POST['branch'])){ $SearchString.= " AND substring(CIFPan, 7, 2) = '".$_POST['branch']."'"; } //Bin if(!empty($_POST['bin'])){ $SearchString.= " AND substring(CIFPan, 0, 7) = '".$_POST['bin']."'"; } if($SearchString != ""){ // DB table to use $table = <<<EOT (SELECT T1.RecordId,T1.CIFPan,T1.CIFAcctNum1,T1.CIFFName,T1.CIFLName, CASE WHEN T1.CIFCardStat = 00 THEN 'Good' WHEN T1.CIFCardStat = 01 THEN 'Hot' WHEN T1.CIFCardStat = 02 THEN 'Warm' WHEN T1.CIFCardStat = 03 THEN 'Closed' WHEN T1.CIFCardStat = 04 THEN 'Issue' WHEN T1.CIFCardStat = 05 THEN 'Do Not Honor' WHEN T1.CIFCardStat = 06 THEN 'Stolen' WHEN T1.CIFCardStat = 07 THEN 'Lost' WHEN T1.CIFCardStat = 08 THEN 'Retain' WHEN T1.CIFCardStat = 09 THEN 'Restricted' WHEN T1.CIFCardStat = 10 THEN 'Other' WHEN T1.CIFCardStat = 11 THEN 'IBanking Approved' WHEN T1.CIFCardStat = 12 THEN 'IBanking Pending' WHEN T1.CIFCardStat = 12 THEN 'Pending Activation' ELSE 'No Available Status' END as CIFCardStat, CONVERT(DATE, CIFExpDate,101) as CIFExpDate, CONVERT(DATE, CIFUpdateDate,101) as CIFUpdateDate, DATEDIFF(day,GETDATE(),CIFExpDate) as daysToExpire, CASE WHEN SUBSTRING((CIFPan), 7, 2) = 00 THEN 'B Street' WHEN SUBSTRING((CIFPan), 7, 2) = 01 THEN 'Vieu' WHEN SUBSTRING((CIFPan), 7, 2) = 02 THEN 'GFL' WHEN SUBSTRING((CIFPan), 7, 2) = 03 THEN 'Rod' WHEN SUBSTRING((CIFPan), 7, 2) = 05 THEN 'Mar' WHEN SUBSTRING((CIFPan), 7, 2) = 06 THEN 'Mercu' WHEN SUBSTRING((CIFPan), 7, 2) = 07 THEN 'Ro' WHEN SUBSTRING((CIFPan), 7, 2) = 09 THEN 'Jann' ELSE 'No Available Branch' END AS branch FROM zCIFRecord T1 WHERE CIFExpDate != '00/00/0000' AND CIFUpdateDate != '00/00/0000' AND T1.CIFDateTime = (SELECT MAX(CIFDateTime) FROM zCIFRecord T2 WHERE T1.CIFPan = T2.CIFPan) AND CIFUpdateActivity<>'Z' $SearchString ) temp EOT; }else{ $table = <<<EOT (SELECT T1.RecordId,T1.CIFPan,T1.CIFAcctNum1,T1.CIFFName,T1.CIFLName, CASE WHEN T1.CIFCardStat = 00 THEN 'Good' WHEN T1.CIFCardStat = 01 THEN 'Hot' WHEN T1.CIFCardStat = 02 THEN 'Warm' WHEN T1.CIFCardStat = 03 THEN 'Closed' WHEN T1.CIFCardStat = 04 THEN 'Issue' WHEN T1.CIFCardStat = 05 THEN 'Do Not Honor' WHEN T1.CIFCardStat = 06 THEN 'Stolen' WHEN T1.CIFCardStat = 07 THEN 'Lost' WHEN T1.CIFCardStat = 08 THEN 'Retain' WHEN T1.CIFCardStat = 09 THEN 'Restricted' WHEN T1.CIFCardStat = 10 THEN 'Other' WHEN T1.CIFCardStat = 11 THEN 'IBanking Approved' WHEN T1.CIFCardStat = 12 THEN 'IBanking Pending' WHEN T1.CIFCardStat = 12 THEN 'Pending Activation' ELSE 'No Available Status' END as CIFCardStat, CONVERT(DATE, CIFExpDate,101) as CIFExpDate, CONVERT(DATE, CIFUpdateDate,101) as CIFUpdateDate, DATEDIFF(day,GETDATE(),CIFExpDate) as daysToExpire, CASE WHEN SUBSTRING((CIFPan), 7, 2) = 00 THEN 'Bridge Street' WHEN SUBSTRING((CIFPan), 7, 2) = 01 THEN 'Vieux Fort' WHEN SUBSTRING((CIFPan), 7, 2) = 02 THEN 'GFL Charles' WHEN SUBSTRING((CIFPan), 7, 2) = 03 THEN 'Rodney Bay Mall' WHEN SUBSTRING((CIFPan), 7, 2) = 05 THEN 'Marigot' WHEN SUBSTRING((CIFPan), 7, 2) = 06 THEN 'Mercury Court' WHEN SUBSTRING((CIFPan), 7, 2) = 07 THEN 'Rodney Bay Marina' WHEN SUBSTRING((CIFPan), 7, 2) = 09 THEN 'Jannou Credit Union' ELSE 'No Available Branch' END AS branch FROM zCIFRecord T1 WHERE CIFExpDate != '00/00/0000' AND CIFUpdateDate != '00/00/0000' AND T1.CIFDateTime = (SELECT MAX(CIFDateTime) FROM zCIFRecord T2 WHERE T1.CIFPan = T2.CIFPan) AND CIFUpdateActivity<>'Z' ) temp EOT; } } echo $Table; // Table's primary key $primaryKey = 'RecordId'; // Array of database columns which should be read and sent back to DataTables. // The `db` parameter represents the column name in the database, while the `dt` // parameter represents the DataTables column identifier. In this case simple // indexes $columns = array( array( 'db' => 'RecordId', 'dt' => 0 ), array( 'db' => 'CIFPan', 'dt' => 1 ), array( 'db' => 'CIFCardStat', 'dt' => 2 ), array( 'db' => 'CIFAcctNum1', 'dt' => 3 ), array( 'db' => 'CIFFName', 'dt' => 4 ), array( 'db' => 'CIFLName', 'dt' => 5 ), array( 'db' => 'CIFExpDate', 'dt' => 6 ), array( 'db' => 'CIFUpdateDate', 'dt' => 7 ), array( 'db' => 'Branch', 'dt' => 8 ) ); // SQL server connection information // SQL server connection information $sql_details = array( 'user' => '', 'pass' => '', 'db' => '', 'host' => '' ); /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * If you just want to use the basic configuration for DataTables with PHP * server-side, there is no need to edit below this line. */ require( 'scripts/ssp.class.php' ); echo json_encode( SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns ) ); ?>
  • tangerinetangerine Posts: 3,342Questions: 35Answers: 394

    First, your page shows numerous errors. Check your browser console.

    Second, you are sending POST but expecting GET.

    "ajax": {
    url: "../processing/reportsearch.php",
    type: "POST",

    echo json_encode( SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns ) );

  • shrogersshrogers Posts: 11Questions: 2Answers: 0

    Thank you for your input.

    I clear the errors and update to POST but still issue still there. Not sure what's happening.

  • tangerinetangerine Posts: 3,342Questions: 35Answers: 394

    Your page still has errors.

    DataTables warning: table id=carddetails - An error occurred while connecting to the database. The error reported by the server was: could not find driver

    jquery.dataTables.min.js:39 Uncaught TypeError: Cannot read property 'length' of undefined

  • shrogersshrogers Posts: 11Questions: 2Answers: 0
    edited September 2020

    Thank you. I'm having a little trouble connecting to the database from remote hosting.

    I made the changes locally was able to post manually.

    When I try posting on click of a button, it doesn't filter and returns all value.

    Can you please point me to my mistake and what needs to be done to address?

    <script type="text/javascript" class="init">
        
    $(document).ready(function(){
        
    $("#filter-button").click(function() {
     $(this).toggleClass("active");
      $('#filter-body').slideToggle(100);
    });
    
    
    fill_datatable();
    function fill_datatable(cardNum = '', omniCardStatus = '', branch = '', addedStart = '', addedEnd = '', updateStart = '', updateEnd = '', bin = '', btnSearch = ''){
        var dataTable = $('#carddetails').DataTable({
            //destroy: true,
            "processing": true,
            "serverSide": true,
            "ajax": {
                     url: "../processing/reportsearch",
                    type: "POST",
                    data:{cardNum:cardNum, omniCardStatus:omniCardStatus, branch:branch, addedStart:addedStart, addedEnd:addedEnd, updateStart:updateStart, updateEnd:updateEnd, bin:bin, btnSearch:btnSearch,
                    }
            },
            "columnDefs": [
                    { 
                        "bVisible": false, 
                        "aTargets": [0] 
                    },
                        {
                        "targets": -1,
                        "data": null,
                        "defaultContent": '<div class="btn-group"> <form action="../cardman/cardDetails?is=yes" method="post"><input name="cPan" type="hidden" id="cPan" value=""/><button type="submit" id="search" class="btn btn-info btn-xs dt-view" style="margin-right:16px;"><span class="glyphicon glyphicon-eye-open glyphicon-info-sign" aria-hidden="true"></span></button></form>  <form><button type="button" class="btn btn-primary btn-xs dt-edit" style="margin-right:16px;"><span class="glyphicon glyphicon-print" aria-hidden="true"></span></button></form></div>'
                    }
                ],
               "dom": 'Bfrtip',
               "buttons": [
                               {
                                   "extend": 'excel',
                                   "text": '<i class="fa fa-file-excel-o" style="color: green;"></i>',
                                   "titleAttr": 'Excel',                               
                                   "action": newexportaction
                               },
                               {
                                   "extend": 'csv',
                                   "text": '<i class="fa fa-file-text-o" style="color: green;"></i>',
                                   "titleAttr": 'CSV',                               
                                   "action": newexportaction
                               },
                               {
                                   "extend": 'pdf',
                                   "text": '<i class="fa fa-file-pdf-o" style="color: green;"></i>',
                                   "titleAttr": 'PDF',                               
                                   "action": newexportaction
                               }
                            ]
        });
    
    $('#btnSearch').click(function(){
        
        var cardNum = $('#cardNum').val();
        var omniCardStatus = $('#omniCardStatus').val();
        var branch = $('#branch').val();
        var addedStart = $('#addedStart').val();
        var addedEnd = $('#addedEnd').val();
        var updateEnd = $('#updateStart').val();
        var updateEnd = $('#updateEnd').val();
        var bin = $('#bin').val();
        var btnSearch = 'yes';
    
        if (cardNum != '' || omniCardStatus != '' || branch != '' || addedStart != '' || addedEnd != '' || updateStart != '' || updateEnd != '' || bin != '' || btnSearch != ''){
            
            //alert(btnSearch);
            $('#carddetails').DataTable().destroy();
            fill_datatable(cardNum, omniCardStatus, branch, addedStart, addedEnd, updateStart, updateEnd, bin, btnSearch);
            
        }else{
            
            //alert('Select Option');
            $('#carddetails').DataTable().destroy();
            fill_datatable();
        }
        
    });
    
        
    } });
    </script>
    

    Edited by Colin - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    I just looked at your page (http://www.meimeisfashion.com/cardgen/reports/cardsearch.php) and it's giving a 404

    Colin

This discussion has been closed.