时间戳

首页 > 解决方案 > 使用 CodeIgniter 的日期范围搜索数据表

php - 使用 CodeIgniter 的日期范围搜索数据表

问题描述

我需要搜索特定日期范围内的数据。这就是我所做的

这是我的 模型

public function rangeDate($start_date, $end_date){

    $query = $this->db->select($this->tables['invent_inv'].'.id,'.$this->tables['invent_inv'].'.inv_type,'.$this->tables['invent_inv'].'.inv_cno,'.$this->tables['invent_cust'].'.id as custid,'.$this->tables['invent_cust'].'.cust_name,'.$this->tables['invent_inv'].'.inv_price, '.$this->tables['invent_inv'].'.inv_tax,'.$this->tables['invent_inv'].'.pay_due, SUM('.$this->tables['invent_sales'].'.paid_amnt) as payment,'.$this->tables['invent_inv'].'.timestamp')
        ->join($this->tables['invent_cust'], $this->tables['invent_inv'].'.cust_id='.$this->tables['invent_cust'].'.id','LEFT')
        ->join($this->tables['invent_sales'], $this->tables['invent_inv'].'.inv_cno='.$this->tables['invent_sales'].'.inv_cno','LEFT')
        ->where($this->tables['invent_inv'].'.status !=', 1)
        ->where($this->tables['invent_inv'].'.status !=', 0)
        ->where($this->tables['invent_inv'].'.timestamp >=', $start_date)
        ->where($this->tables['invent_inv'].'.timestamp <=', $end_date)
        ->group_by($this->tables['invent_sales'].".inv_cno")
        ->group_by($this->tables['invent_inv'].".inv_cno")
        ->group_by($this->tables['invent_inv'].".timestamp")
        ->get($this->tables['invent_inv']);

    return $query;
}

这是我的 控制器

public function rangeDates(){
    $start_date = $_POST['start_date'];
    $end_date = $_POST['end_date'];

    $return = $this->ion_auth->rangeDate($start_date,$end_date);

    echo json_encode($return);
}

这是ajax JS

$('.input-daterange').datepicker({
todayBtn: 'linked',
format: "yyyy-mm-dd",
autoclose: true
});

function fetch_data(start_date='', end_date=''){
var dataTable = $('#table-sales').DataTable({
    "processing": true,
    "serverSide": true,
    "order":[],
    "ajax":{
        url: "sales/rangeDates",
        type: "POST",
        data:{
            start_date:start_date, end_date:end_date
        },
        success:function(data){
            $('#table-sales').html(data);
        }
    }
});
}

$('#search').click(function(){

var inv_type = $('#invType').val();
var start_date = $('#start_date').val();
var end_date = $('#end_date').val();

if(start_date != '' && end_date != '' && inv_type != 0){
    $('#table-sales').DataTable().destroy();
    fetch_data(start_date,end_date);
}else{
    alert('Both Date is Required and Choose what to show!');
}

});

现在我的问题是，在我的屏幕上，当我单击视图上的搜索按钮时，它会弹出一个处理对话框，并且控制台上没有错误。所以我试着查看网络，这就是我发现的。

在此处输入图像描述

它们都是空的，我不知道为什么。有人可以指出我在这件事上做错了什么。先感谢您 。

更多信息：这是我的

看法

<div class="box-body">
              <div class="table-responsive">
                  
                  <div class="row">
                  <div class="input-daterange">
                    <div class="col-md-4">
                      <div class="form-group start-date">
                        <label>From</label>
                        <div class="input-group date">
                          <div class="input-group-addon">
                            <i class="fa fa-calendar"></i>
                          </div>
                          <input type="text" class="form-control pull-right" id="start_date" name="start_date">
                        </div>
                        <!-- /.input group -->
                      </div>
                    </div>
                    <div class="col-md-4">
                      <div class="form-group end-date">
                        <label>To</label>
                        <div class="input-group date">
                          <div class="input-group-addon">
                            <i class="fa fa-calendar"></i>
                          </div>
                          <input type="text" class="form-control pull-right" id="end_date" name="end_date">
                        </div>
                        <!-- /.input group -->
                      </div>
                    </div>
                    <div class="col-md-2">
                      <label>Select</label>
                      <select class="form-control select2" id="invType" name="invType" style="width: 100%;">
                        <option value="0" />Select</option>
                        <option value="1">Department Store</option>
                        <option value="2">Local</option>
                        <option value="3">Provincial</option>
                        <option value="4">UNITOP</option>
                        <option value="5">GAISANO</option>
                      </select>
                    </div>
                    <div class="col-md-2">
                      <div class="form-group">
                          <label>Go </label>
                          <div class="input-group date">
                            <button class="btn btn-warning btn-md btn-search" name = "search" id="search">Search!</button> 
                          </div>
                          <!-- /.input group -->
                      </div>
                    </div>
                    </div>
                  </div>


                <table id="table-sales" class="table table-bordered table-striped">
                  <thead>
                    <tr>
                      <th>DR</th>
                      <th>Date</th>
                      <th>Customer</th>
                      <th>Price</th>
                      <th>Paid Amnt</th>
                      <th>Balance</th>
                      <th>Receipt</th>
                      <th>Remarks</th>
                      <th>Items</th>
                      <th style="width:55px;">Action</th>
                    </tr>
                  </thead>
                  <tbody>
                    <?php foreach ($sales_inv as $inv) {
                        $balance = $inv->inv_price-$inv->payment;
                        $status = "";
                        $payment = $inv->payment;
                        
                        if ($inv->inv_type==1) {
                          $status = "Department Store";
                        }elseif($inv->inv_type==2){
                          $status = "Local";
                        }elseif($inv->inv_type==3){
                          $status = "Provincial";
                        }elseif($inv->inv_type==4){
                          $status = "UNITOP";
                        }elseif($inv->inv_type==5){
                          $status = "GAISANO";
                        }
                    ?>
                      <tr class="row-<?php echo $inv->id; ?>">
                        <td><a href="#" class="btn-inv-show" data-value="<?php echo $inv->inv_cno; ?>@<?php echo $inv->id; ?>"><?php echo $inv->inv_cno; ?></a></td>
                        <td><?php echo date("M d, Y", strtotime($inv->timestamp)); ?></td>
                        <td><?php echo $inv->cust_name; ?></td>
                        <td class="price-<?php echo $inv->inv_cno; ?>">Php <?php echo number_format($inv->inv_price,2); ?></td>
                        <td class="payment-<?php echo $inv->inv_cno; ?>">Php <?php echo number_format($payment,2); ?></td>
                        <td class="bal-<?php echo $inv->inv_cno; ?>">Php <?php echo number_format($balance,2); ?></td>
                        <td><?php echo $status; ?></td>
                        <td>
                          <button class="btn btn-info btn-sm btn-rem" data-value="<?php echo $inv->id; ?>">View Remarks
                          </button>
                        </td>
                        <td>
                          <button class="btn btn-success btn-sm btn-item" data-value="<?php echo $inv->inv_cno; ?>">View Item
                          </button>
                        </td>
                        <td>
                          <button class="btn btn-primary btn-xs btn-print" data-value="<?php echo $inv->inv_cno; ?>"><i class="fa fa-print"></i></button>
                          <button class="btn btn-warning btn-xs btn-add-pay" data-value="<?php echo $inv->inv_cno; ?>@<?php echo $inv->custid; ?>@<?php echo $inv->id; ?>"><i class="fa fa-credit-card"></i></button>
                          <button class="btn btn-danger btn-xs btn-edit-pay" data-value="<?php echo $inv->inv_cno; ?>@<?php echo $inv->custid; ?>@<?php echo $inv->id; ?>"><i class="fa fa-edit"></i></button>
                        </td>
                      </tr>
                    <?php } ?>
                  </tbody>
                  <tfooter>
                    <tr>
                      <th>DR</th>
                      <th>Date</th>
                      <th>Customer</th>
                      <th>Price</th>
                      <th>Paid Amnt</th>
                      <th>Balance</th>
                      <th>Receipt</th>
                      <th>Remarks</th>
                      <th style="width:55px;">Action</th>
                    </tr>
                  </tfooter>
                </table>
              </div>
              <!-- /.table-responsive -->
            </div>
          </div>
          <!-- /.box -->
        </section>
        <!-- right col -->
      </div>

时间戳格式

在此处输入图像描述

处理图像

在此处输入图像描述

标签： phpmysqlajaxcodeigniter

解决方案

在根据日期范围从数据库中获取数据时，我遇到了类似的问题。我通过添加00:00:00开始日期和23:59:59结束日期解决了这个问题。通过添加23:59:59到结束日期意味着它将实际获取具有最后日期和时间的数据。检查这是否解决了您的问题。在您的模型中执行此操作

public function rangeDate($start_date, $end_date){
    $start_date = $start_date .' 00:00:00';
    $end_date = $end_date . ' 23:59:59';

    $query = $this->db->select($this->tables['invent_inv'].'.id,'.$this->tables['invent_inv'].'.inv_type,'.$this->tables['invent_inv'].'.inv_cno,'.$this->tables['invent_cust'].'.id as custid,'.$this->tables['invent_cust'].'.cust_name,'.$this->tables['invent_inv'].'.inv_price, '.$this->tables['invent_inv'].'.inv_tax,'.$this->tables['invent_inv'].'.pay_due, SUM('.$this->tables['invent_sales'].'.paid_amnt) as payment,'.$this->tables['invent_inv'].'.timestamp')
        ->join($this->tables['invent_cust'], $this->tables['invent_inv'].'.cust_id='.$this->tables['invent_cust'].'.id','LEFT')
        ->join($this->tables['invent_sales'], $this->tables['invent_inv'].'.inv_cno='.$this->tables['invent_sales'].'.inv_cno','LEFT')
        ->where($this->tables['invent_inv'].'.status !=', 1)
        ->where($this->tables['invent_inv'].'.status !=', 0)
        ->where($this->tables['invent_inv'].'.timestamp >=', $start_date)
        ->where($this->tables['invent_inv'].'.timestamp <=', $end_date)
        ->group_by($this->tables['invent_sales'].".inv_cno")
        ->group_by($this->tables['invent_inv'].".inv_cno")
        ->group_by($this->tables['invent_inv'].".timestamp")
        ->get($this->tables['invent_inv']);

    return $query;
}

推荐阅读