Server-Side Processing PHP MSSQL - Date Column coming through as [object Object]

Caldaga

I am using server-side processing with MSSQL and PHP. My data is being displayed in my table properly, but the problem is that my date/time column is coming through as [object Object] instead of the date/time. I understand I need to format it or convert it to a string to resolve this issue but I'm not sure of the best way of doing this, and would prefer to do it on the server side without breaking sorting/filtering on that column if possible.

Here is what my json looks like:

{
    "draw":0,
    "recordsTotal":21877,
    "recordsFiltered":21877,
    "data":
    [
        {
        "Action":"Rename Folder",
        "TimeOccurred":{"date":"2017-04-26 10:08:00","timezone_type":3,"timezone":"America\/Tegucigalpa"},
        "UserName":"Username",
        "IPv4From":"ipaddress",
        "ShareName":"",
        "FullFilePath":"C:\\ProgramData\\USOShared\\Logs",
        "NewPathName":"C:\\ProgramData\\USOShared\\Logs\\UpdateUx.100.etl",
        "FromServer":"HOSTNAME"
        },
        {
        "Action":"Delete Confirmed for File",
        "TimeOccurred":{"date":"2017-04-26 10:08:00","timezone_type":3,"timezone":"America\/Tegucigalpa"},
        "UserName":"username",
        "IPv4From":"ipaddress",
        "ShareName":"",
        "FullFilePath":"C:\\ProgramData\\USOShared\\Logs",
        "NewPathName":"",
        "FromServer":"hostname"
        },
        {
        "Action":"Create File",
        "TimeOccurred":{"date":"2017-04-27 10:37:00","timezone_type":3,"timezone":"America\/Tegucigalpa"},
        "UserName":"",
        "IPv4From":"ipaddress",
        "ShareName":"?share path?",
        "FullFilePath":"?share path?",
        "NewPathName":"",
        "FromServer":"hostname"
        },
    ]
}

Here is what my server side code looks like:

<?php
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* Easy set variables
*/

/* Array of database columns which should be read and sent back to DataTables. Use a space where
* you want to insert a non-database field (for example a counter or static image)
*/
// add your columns here!!!

// Setup Date Range Filter from Custom Query
    $dateArray = explode(" ", $_POST['datefilter']);
    $startDate = ($dateArray[0]." ".$dateArray[1]);
    $endDate = ($dateArray[3]." ".$dateArray[4]);
//  $dateQuery = " AND (TimeOccurred >= "."'".$startDate."'"." AND TimeOccurred <= "."'".$endDate."')";

// add your columns here!!!
    $aColumns = array( 'Action', 'TimeOccurred', 'UserName', 'IPv4From', 'ShareName', 'FullFilePath', 'NewPathName', 'FromServer' );

    $server = "";
    $database = array("Database" => "");
    $conn = sqlsrv_connect($server, $database); 
    if ($conn === false) die("<pre>".print_r(sqlsrv_errors(), true));



/* Indexed column (used for fast and accurate table cardinality) */
    $sIndexColumn = "GUID";

/* DB table to use */
    $sTable = $_POST['table'];

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* If you just want to use the basic configuration for DataTables with PHP server-side, there is
* no need to edit below this line
*/

/*
* Local functions
*/
    function fatal_error ( $sErrorMessage = '' ) {
        header( $_SERVER['SERVER_PROTOCOL'] .' 500 Internal Server Error' );
        die( $sErrorMessage );
    }

/* Ordering */
    $sOrder = "";
    if ( isset( $_POST['order'] ) ) {
        $sOrder = "ORDER BY ";
        if ( $_POST['columns'][0]['orderable'] == "true" ) {
            $sOrder .= "".$aColumns[ intval( $_POST['order'][0]['column'] ) ]." ".
            ($_POST['order'][0]['dir']==='asc' ? 'asc' : 'desc');
        }
    }

/* escape function */
    function mssql_escape($data) {
        if(is_numeric($data))
        return $data;
        $unpacked = unpack('H*hex', $data);
        return '0x' . $unpacked['hex'];
    }

/* Filtering */
    $sWhere = "";
    if ( isset($_POST['search']['value']) && $_POST['search']['value'] != "" ) {
        $sWhere = "WHERE (";
        for ( $i=0 ; $i<count($aColumns) ; $i++ ) {
            $sWhere .= $aColumns[$i]." LIKE '%".addslashes( $_POST['search']['value'] )."%' OR ";
        }
        $sWhere = substr_replace( $sWhere, "", -3 );
        $sWhere .= ')';
    }

/* Individual column filtering */
    for ( $i=0 ; $i<count($aColumns) ; $i++ ) {
        if ( isset($_POST['columns'][$i]) && $_POST['columns'][$i]['searchable'] == "true" && $_POST['columns'][$i]['search']['value'] != '' ) {
            if ( $sWhere == "" ) {
                $sWhere = "WHERE ";
            }
            else {
                $sWhere .= " AND ";
            }
            $sWhere .= $aColumns[$i]." LIKE '%".addslashes($_POST['columns'][$i]['search']['value'])."%' ";
        }
    }

//  if ( $sWhere == "" ) {
//      $sWhere = "WHERE (TimeOccurred >= "."'".$startDate."'"." AND TimeOccurred <= "."'".$endDate."')";
//  }
//  else {
//      $sWhere .= " AND (TimeOccurred >= "."'".$startDate."'"." AND TimeOccurred <= "."'".$endDate."')";
//  }

/* Paging */
    $top = (isset($_POST['start']))?((int)$_POST['start']):0 ;   
    $limit = (isset($_POST['length']))?((int)$_POST['length'] ):5;
    $sQuery = "SELECT TOP $limit ".implode(", ",$aColumns)." FROM $sTable $sWhere ".(($sWhere=="")?" WHERE ":" AND ")." $sIndexColumn NOT IN ( SELECT TOP $top $sIndexColumn FROM $sTable $sOrder ) $sOrder";
    $rResult = sqlsrv_query($conn, $sQuery);
    if($rResult === false){
        die(sqlsrv_errors(SQLSRV_ERR_ERRORS));
    }

/* Data set length after filtering */
    $sQueryCnt = "SELECT * FROM $sTable $sWhere";
    $rResultCnt = sqlsrv_query($conn, $sQueryCnt, array(), array("Scrollable" => SQLSRV_CURSOR_KEYSET));
    $iFilteredTotal = sqlsrv_num_rows( $rResultCnt );

/* Total data set length */
    $sQuery = "SELECT COUNT(GUID) FROM $sTable";
    $rResultTotal = sqlsrv_query($conn, $sQuery, array(), array("Scrollable" => SQLSRV_CURSOR_KEYSET));
    $aResultTotal = sqlsrv_fetch_array($rResultTotal, SQLSRV_FETCH_NUMERIC);
    $iTotal = $aResultTotal[0];

/* Output */
    $output = array(
        "draw" => intval($_POST['draw']),
        "recordsTotal" => $iTotal,
        "recordsFiltered" => $iFilteredTotal,
        "data" => array()
    );

    while ( $aRow = sqlsrv_fetch_array( $rResult, SQLSRV_FETCH_ASSOC) ) {
        $row = array();
        for ( $i=0 ; $i<count($aColumns) ; $i++ ) {
            $row[$aColumns[$i]] = $aRow[ $aColumns[$i] ];
        }
        $output['data'][] = $row;
    }

    echo json_encode( $output );

?>

And finally what my javascript looks like:

    <script type="text/javascript">
        $(document).ready(function() {
            $('#myTable').DataTable( {
                dom: 'Bfrti',
                buttons: [ 'colvis' ],
                deferRender: true,
                scroller: { loadingIndicator: true },
                scrollX: true,
                scrollY: 700,
                processing: true,
                serverSide: true,
                ajax: {
                    url: '../php/queryresults.php',
                    type: "POST",
                    data: 
                    { 
                        table: '<?php echo $_POST['table']; ?>',
                        daterange: '<?php echo $_POST['datefiler']; ?>',
                        selectedcolumns: '<?php echo $_POST['selectedcolumns']; ?>'
                    },
                },
                <?php
                $myColumns = $_POST['selectedcolumns'];
                echo "columns: [";
                foreach ($myColumns as $col) {
                    echo '{ "data": "'.$col.'" },';
                    unset($col);
                }
                echo "]";
                ?>
            } );
        } );
    </script>

I would like this to show up in the TimeOccurred column (see JSON) : 2017-04-27 10:37:00 instead I see this : [object Object]. Any assistance in fixing this server side or client side would be greatly appreciated, but it would be great if I could fix it server side.

kthorngren

To extract the date from the object you can use columns.render. You can define it in columnDefs. This example may work:

      columnDefs: [{targets: [1], 
                      render: function ( data, type, full, meta ) {
                        return data.date;
                      }
                   }
                  ]

Since you are dynamically creating the columns you will need to determine the column number and use that variable for your targets list.

Kevin

allan

Use data: 'TimeOccurred.date' to pick out the date object for that column.

See the manual for more information about using obejcts as a data source.

Allan

Caldaga

Thank you Allan that resolved my problem. Appreciate the link to more information as well.

Caldaga

I can open a separate forum thread for this if necessary but wanted to ask this as well. I am new to HTML, CSS, PHP, and Javascript which is basically every language that I am using for this project. I am kind of learning as I go how to do each thing I need to do.

It would go a long way toward "teaching a man to fish" if I could figure out a way to print values of variables in my server-side script to the screen so I could troubleshoot why certain things aren't working.

For example I have a filter for date range in the server side report. I let the end user use a datepicker to get the values then I process that in the background.

Right now I get "no results found" unless I comment that part of the code out. If I just replace the variable with a hardcoded date/time group the way I expect it to be formatted in the variable, it works exactly as expected. This is an instance where being able to print my date/time variables and see that they are messed up or something along those lines would help immensely and in the long term probably decrease the amount of help I need on these forums.

tangerine

var_dump() is probably the simplest way to see your variable values, inserted into your server-side script where required.
I would also recommend learning to use your browser's console facilities. How to start will vary slightly according to which browser you favour, but Google will explain it all.

Caldaga

I prefer chrome. I will look into it thank you.

allan

var_dump() in PHP is awesome for figuring out what a variable contains as @tangerine suggests - particularly for complex data structures.

Another approach I like myself is to be able to include trace debug messages - typically I use file_put_contents() something like this:

file_put_contents( '/tmp/php-debug', 'I am here!', FILE_APPEND );

The second argument is a string, so you can include variables and the like as you need.

Then on your terminal do tail -f /tmp/php-debug and as you load the page in your browser, any debug messages will be shown in your console.

Just remember to remove them before deploying .

Allan