i made a datable using ajax in PHP Codeigniter ,using custom filter button, which is not working

  ajax, codeigniter-3, datatable, mysqli, php

Image

this is the view of my table , there’s a product filter option on the top that i want to run as per this sql query

SELECT * FROM warehouses_products WHERE expiry < SYSDATE() OR quantity < 15;

now can u please tell me the code i have to code in view , model and
controller to run this filter button

this is my view.php

`

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

this is my 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();
}

}
`

this is my 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);
}

}
`

Source: Ask PHP

LEAVE A COMMENT