搜索中的jQuery日期字段

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!!

1个回答



任何回读这个的人的答案是首先使用正确的数据类型,例如</ p>

< pre> $ sql =“
SELECT [仓库]
,[产品]
,[analysis_b]
,[描述]
,[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]
“;
</ code> </ pre>
</ div>

展开原文

原文

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]
  ";

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐