时间戳

首页 > 解决方案 > 在 PHP Codeigniter 中使用 ajax json 的数据表，我的自定义过滤器按钮不起作用

php - 在 PHP Codeigniter 中使用 ajax json 的数据表，我的自定义过滤器按钮不起作用

问题描述

查看图片

这是我的表的视图，顶部有一个产品过滤器选项，我想按照这个 sql 查询运行

SELECT * FROM warehouses_productsWHERE expiry < SYSDATE() 或数量 < 15；

现在你能告诉我我必须在视图、模型和控制器中编写的代码来运行这个过滤器按钮吗

这是我的 view.php

<div class="container">
       
        <br>
        <div class="panel panel-default">
            <div class="panel-heading">
                <h3 class="panel-title" >Product Filter : </h3>
            </div>
            <div class="panel-body">
            <form  id="filter" class="form-horizontal" method="post" action="<?php echo base_url('product/inStock');?>">
                <div class="form-group" class="col-md-3">

                   
                    <label for="product">Product</label>
                    <select class="form-control select2" id="filter" name="filter"  style="width: 30%;">
                      <option  value="">Select</option>
                      <option name="quantity" id="quantity" > Low Quantity </option>
                      <option name="expiry" id="expiry" > Expired  </option>
                     
                     
                    </select>
                    
                  </div>
                    
                    
                    <div class="form-group">
                        <label for="LastName" class="col-sm-2 control-label"></label>
                        <div class="col-sm-4">
                            <button type="button" id="filter" class="btn btn-primary">Filter</button>
                            <button type="button" id="btn-reset" class="btn btn-default">Reset</button>
                        </div>
                    </div>
                </form>
            </div>
        </div>

这是我的model.php

<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Customers_model extends CI_Model {

    var $table = 'warehouses_products';
    var $column_order = array(null, 'product_id','warehouse_id','batch','expiry','quantity','modified_date','created_date'); //set column field database for datatable orderable
    var $column_search = array('product_id','warehouse_id','batch','expiry','quantity','modified_date','created_date'); //set column field database for datatable searchable 
    var $order = array('id' => 'asc'); // default order 

    public function __construct()
    {
        parent::__construct();
        $this->load->database();
    }

   

    private function _get_datatables_query()
    {
        
        $this->db->from($this->table);

        $i = 0;
    
        foreach ($this->column_search as $item) // loop column 
        {
            if($_POST['search']['value']) // if datatable send POST for search
            {
                
                if($i===0) // first loop
                {
                    $this->db->group_start(); // open bracket. query Where with OR clause better with bracket. because maybe can combine with other WHERE with AND.
                    $this->db->like($item, $_POST['search']['value']);
                }
                else
                {
                    $this->db->or_like($item, $_POST['search']['value']);
                }

                if(count($this->column_search) - 1 == $i) //last loop
                    $this->db->group_end(); //close bracket
            }
            $i++;
        }
        
        if(isset($_POST['order'])) // here order processing
        {
            $this->db->order_by($this->column_order[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
        } 
        else if(isset($this->order))
        {
            $order = $this->order;
            $this->db->order_by(key($order), $order[key($order)]);
        }
    }

    function get_datatables()
    {
        $this->_get_datatables_query();
        if($_POST['length'] != -1)
        $this->db->limit($_POST['length'], $_POST['start']);
        $query = $this->db->get();
        return $query->result();
    }
    

     

    function count_filtered()
    {
        $this->_get_datatables_query();
        $query = $this->db->get();
        return $query->num_rows();
    }

    public function count_all()
    {
        $this->db->from($this->table);
        return $this->db->count_all_results();
    }

}

这是我的controler.php

<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Customers extends CI_Controller {

    public function __construct()
    {
        parent::__construct();
        $this->load->model('customers_model','warehouses_products');
    }

    public function index()
    {
        $this->load->helper('url');
        $this->load->view('inStock_view');
    }
     
    function expiry(){
    $expiry = $this->input->GET('expiry');
    $data['search'] = $this->Voucher_m->check_expiry($expiry);
    $this->load->view('/VENDOR/'.country_code.'/Voucher',$data);
}  
        
    public function ajax_list()
    {
        $list = $this->warehouses_products->get_datatables();
        $data = array();
        $no = $_POST['start'];
        foreach ($list as $warehouses_products) {
            $no++;
            $row = array();
            $row[] = $no;
            $row[] = $warehouses_products->product_id;
            $row[] = $warehouses_products->warehouse_id;
            $row[] = $warehouses_products->batch;
            $row[] = $warehouses_products->expiry;
            $row[] = $warehouses_products->quantity;
            $row[] = $warehouses_products->modified_date;
            $row[] = $warehouses_products->created_date;
            
            $data[] = $row;
        }

        $output = array(
                        "draw" => $_POST['draw'],
                        "recordsTotal" => $this->warehouses_products->count_all(),
                        "recordsFiltered" => $this->warehouses_products->count_filtered(),
                        "data" => $data,
                );
        //output to json format
        echo json_encode($output);
    }

}

标签： phpajaxdatatablecodeigniter-3

解决方案

一些更改，例如数据获取 ny id，而不是通过在模型中声明变量然后通过 ajax 以数组格式打印

查看代码

                  <div class="form-group">
                    <label for="product">Product</label>
                    <select class="form-control select2" id="filter" name="filter" style="width: 100%;">
                      <option  value="">Select</option>
                      <option name="lowQty" id="lowQty" <?php if($filter == "Low Quantity" && !empty($filter)){echo "selected";} ?>> Low Quantity </option>
                      <option name="expired" id="expired" <?php if($filter == "Expired" && !empty($filter)){echo "selected";} ?>> Expired </option>
                    </select>
                    
                  </div>
                </div>
                <div class="col-md-3">
                  <div class="form-group">
                    <label for="warehouse">Search</label> <br />
                    <input type = "search" name="search" id = "search" value ="<?php if(!empty($search)){echo $search;} ?>" class = "form-control input-sm"> </input>
                  </div>
                </div>
                
                
                <div class="col-md-12">
                  <div class="box-footer">
                    <input type="hidden" name="<?php echo $this->security->get_csrf_token_name(); ?>" value="<?php echo $this->security->get_csrf_hash(); ?>">
                    <input type="submit" class="btn btn-info" id="submit" name="submit" value="search">
                    <button type="submit" class="btn btn-primary-info" name="submit">Submit</button>
                  </div>
                </div>
              </form>
            </div>
          </div>
        </div>
      </div>
    </section>

<!-- this is the second section-->
    <div class="col-md-12">
        <div class="box">
            <div class="box-header with-border">
              <h3 class="box-title">List </h3>
              
            </div></form>
            <!-- /.box-header -->
            <div class="box-body">
              <table id="index" class="table table-bordered table-striped ">
                <thead>
                <tr>
                  <th> No </th>
                  <th>Product</th>
                  <th>Quantity</th>
                  <th>Expiry</th>
                  <th>Location</th>
                  
                </tr>
                </thead>
                <tbody>
                  
                    <?php 
                        $no = 0;
                        foreach ($record as $record) {
                            $id= $record->product_id;

                      ?>
                      <tr>
                        <td><?php echo ++$no; ?></td>
                        <td><?php echo $record->product_id; ?> </td>
                        <td><?php echo $record->quantity; ?></td>
                        <td><?php echo $record->expiry; ?></td>
                      </tr>
                      <?php
                        }
                      ?>
                  </tbody>```


    model file


public function inStockFilter($filter = '',$search = ''){
    ///same for expiry need to write 
    $this->db->select('WP.product_id,WP.quantity,WP.expiry,W.warehouse_name,P.name');
    $this->db->from('warehouses_products AS WP');
    $this->db->join('warehouse as W','W.warehouse_id = WP.warehouse_id');
    $this->db->join('products as P','P.product_id = WP.product_id');

    if(!empty($filter)){
        if($filter == "Expired"){
            $this->db->where('WP.expiry <=',date("Y-m-d"));
            $this->db->order_by('WP.expiry',"DESC");
        }
        if($filter == "Low Quantity"){
            $this->db->where('P.quantity <','100');
        }
    }

    if(!empty($search)){
        $this->db->like('P.product_id', $filter);   
    }

    $query = $this->db->get();
    if($query->num_rows()>0)
    return $query->result();
    return false;```



control file 

    {
        $filter = "";
        $search = "";
        if(!empty($this->input->post('filter'))){
            $filter = $this->input->post('filter');
            $data['filter'] = $filter;
        }
        if(!empty($this->input->post('search'))){
            $search = $this->input->post('search');
            $data['search'] = $search;
        }

        $data['record'] = $this->product_model->inStockFilter($filter, $search);

        $this->load->view('product/instock',$data);
    }

推荐阅读