搜索中的jQuery日期字段

Question

Afternoon All,

I have just started using some jQuery code combined with html tables (The html tables are generated from PHP) so please excuse me if this is something basic as most of the code is copy and pasted!

the jQuery scripts are

<script src="../plugins/DataTables/DataTables-1.10.12/js/jquery-1.12.3.js"></script>
<script src="../plugins/DataTables/DataTables-1.10.12/js/jquery.dataTables.min.js"></script>
<link rel="stylesheet" type="text/css" href="../plugins/DataTables/DataTables-1.10.12/css/jquery.dataTables.min.css"/>

And the JavaScript is

<script>
$(document).ready(function() {
    // Setup - add a text input to each footer cell
    $('#template tfoot th').each( function () {
        var title = $(this).text();
        $(this).html( '<input type="text" placeholder="Search '+title+'" />' );
    } );

    // DataTable
    var table = $('#template').DataTable();

    // Apply the search
    table.columns().every( function () {
        var that = this;

        $( 'input', this.footer() ).on( 'keyup change', function () {
            if ( that.search() !== this.value ) {
                that
                    .search( this.value )
                    .draw();
            }
        } );
    } );
} );
</script>

The table structure is nice and simple:

<table width="100%" class="display" id="template" cellspacing="0">
    <thead>
        <tr>
            <th>W/h</th>
            <th>Product</th>
            <th>Description</th>
            <th>Negative Free Stock</th>
            <th>On Order Qty</th>
            <th>Make or Buy</th>
            <th>Last Transaction Date</th>
            <th>Last Transaction Type</th>
            <th>Analysis B</th>
            <th>Next Order No</th>
            <th>Next On Order Qty</th>
            <th>Next Date required</th>
        </tr>
    </thead>
    <tfoot>
        <tr>
            <th>W/h</th>
            <th>Product</th>
            <th>Description</th>
            <th>Negative Free Stock</th>
            <th>On Order Qty</th>
            <th>Make or Buy</th>
            <th>Last Transaction Date</th>
            <th>Last Transaction Type</th>
            <th>Analysis B</th>
            <th>Next Order No</th>
            <th>Next On Order Qty</th>
            <th>Next Date required</th>
        </tr>
    </tfoot>
    <tbody>

<?php
//BUILD SQL QUERY
$sql = "
SELECT [warehouse]
      ,[product]
      ,[analysis_b]
      ,[description]
      ,[negative_free_stock]
      ,[on_order_qty]
      ,[make_or_buy]
      ,[last_transaction_date]
      ,[last_transaction_type]
      ,[next_order_no]
      ,[next_on_order_qty]
      ,[next_date_required]
  FROM [dbo].[negative_stock]
  ";
$stmt = sqlsrv_query( $conn, $sql );
if( $stmt === false) {
    die( print_r( sqlsrv_errors(), true) );
}

// START LOOP
while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) ) {

// POPULATE TABLE DATA
        echo'
        <tr>
            <td>'.$row['warehouse'].'</td>
            <td>'.$row['product'].'</td>
            <td>'.$row['description'].'</td>
            <td>'.$row['negative_free_stock'].'</td>
            <td>'.$row['on_order_qty'].'</td>
            <td>'.$row['make_or_buy'].'</td>
            <td>'.'</td>
            <td>'.$row['last_transaction_type'].'</td>
            <td>'.$row['analysis_b'].'</td>
            <td>'.$row['next_order_no'].'</td>
            <td>'.$row['next_on_order_qty'].'</td>
            <td>'.'</td>
        </tr>';
}
?>

All of the above code is from: https://www.datatables.net/examples/api/multi_filter.html

Now all of the above code works together fine.

However notice I have left 2 x tags empty these are meant for the 2 date fields e.g. [last_transaction_date] and [next_date_required]

When I add in the date fields into the two empty TD rows it breaks the code somewhere and all im presented with on the page is the headers twice in a row and it does not write out any of the tables or the CSS

The data is coming from a Microsoft SQL server, one of the date fields is "Date" format and one is "DateTime" format.

if I echo the Date field it appears: 2016-08-30 if I echo the DateTime field it appears: 2016-08-30 00:00:00.000

If I try one at a time I get the same results.

I believe it may be something to do with the search bars generated by the JS??? e.g.

$(document).ready(function() {
    // Setup - add a text input to each footer cell
    $('#template tfoot th').each( function () {
        var title = $(this).text();
        $(this).html( '<input type="text" placeholder="Search '+title+'" />' );
    } );

Any help would be much appreciated!!

Answer 1

The answer for anybody reading back on this is to use correct data types in the first place e.g.

$sql = "
SELECT [warehouse]
      ,[product]
      ,[analysis_b]
      ,[description]
      ,[negative_free_stock]
      ,[on_order_qty]
      ,[make_or_buy]
      ,CONVERT(VARCHAR(10), [last_transaction_date], 103) as [last_transaction_date]
      ,[last_transaction_type]
      ,[next_order_no]
      ,[next_on_order_qty]
      ,CONVERT(VARCHAR(10), [next_date_required], 103) as [next_date_required]
FROM [dbo].[negative_stock]
  ";