POST values to SSP and get return results

shrogers

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.

kthorngren

See if deferLoading does what you want.

Kevin

shrogers

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.

kthorngren

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

Kevin

shrogers

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.

colin

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

shrogers

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?

shrogers

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>

allan

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

shrogers

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

<?php > ?>

tangerine

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

shrogers

Thank you for your input.

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

tangerine

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

shrogers

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.

colin

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

Colin