hzq hzq - 4 months ago 57
MySQL Question

Pagination with search/filter using form

I am new to codeigniter framework and I'm trying to do pagination with a search filter.

I've come across answers such as this (which has no answer though) and this which also has no ticked answer so I'm not sure if it's the correct way to follow, moreover confused.

What I have is by default the page would show all result of a table using pagination.

Now I'm stuck and gotten into quite a mess so do pardon me if there are a few obvious mistakes.

So what I'm trying to do here is when the user selects a value in the drop down box and submits let's say Customer A, I'm expecting rows containing

Customer A
from column
customer
only.

However after submitting I'm getting all the results and worse, in plain text without my header and footer (separate views). I understand that's because I didn't call them but still it doesn't display those with
Customer A
only.

Been trying to find a simple solution where after a form submit, paginate query would execute according to the value gotten from the form, and display the selected rows. Can't seem to find any other than the two links, so I'm not sure if I'm filtering the right way.

View

<form method="POST" action='<?php echo base_url("index.php/Home/load_lot_table")?>' class="form-inline">
<select id="cust_drop_down" name="cust_drop_down" class="form-control input-mini">
<option value="all">All</option>
<option value="custA">Customer A</option>
<option value="custB">Customer B</option>
</select>

<input type="submit" class="btn btn-primary purple_button" value="Search">
</form>


Controller

public function on_hold_lot() // Default function to display result
{
$data['title'] = 'Search System';

$this->load->view('templates/normal_header', $data);
$this->populate_customer_dropdown(); // private
$this->load_lot_table(); // public
$this->load->view('templates/legend_footer', $data);
}

public function load_lot_table() // Main pagination function
{

if(isset($this->input->post))
{
$search = array(
'customer' => $this->input->post('cust_drop_down')
);
}
else
{
$search = array(
'customer' => 'all',
'stage' => 'all',
'lot_status' => 'all'
);
}
$config = array();
$config['base_url'] = base_url()."index.php/Home/on_hold_lot";
$config['total_rows'] = $this->home_model->record_count();
$config['per_page'] = 10;
$config['uri_segment'] = 3;
$config['next_link'] = 'Next';
$config['prev_link'] = 'Previous';

$this->pagination->initialize($config);

$page = ($this->uri->segment(3)) ? $this->uri->segment(3) : 0;
$results = $this->home_model->fetch_lots($config['per_page'], $page, $search);

$data['disp_rows'] = $results;
$data['links'] = $this->pagination->create_links();

return $this->load->view('home/lot_disposition', $data);
}


Model

public function record_count()
{
return $this->db->count_all('disp_dummy');
}

public function fetch_lots($limit, $start, $search = null)
{
$this->db->limit($limit, $start);

if($search != null && $search['customer'] != 'all')
{
$this->db->where('customer', $search['customer']);
}

$query = $this->db->get('disp_dummy');

if($query->num_rows() > 0)
{
foreach($query->result() as $row)
{
$data[] = $row;
}
return $data;
}
else
{
return false;
}
}

Answer

You will need to make some changes to your code :

First of all use GET request to submit your search as the you wont get the posted parameters in second page for pagination :

VIEW :

<form method="GET" action='<?php echo base_url("index.php/Home/load_lot_table")?>' class="form-inline">
     <select id="cust_drop_down" name="cust_drop_down" class="form-control input-mini">
         <option value="all">All</option>
         <option value="custA">Customer A</option>
         <option value="custB">Customer B</option>
     </select>

     <input type="submit" class="btn btn-primary purple_button" value="Search">
</form>

In your controller You will have to check the get value and not the post values. Also in the $config['total_rows'] you need to pass the total number of rows that are present in your current query and not in the table. So it would be something like this :

CONTROLLER :

 public function load_lot_table() // Main pagination function
{

    if($this->input->get('cust_drop_down'))
    {
        $search = array(
        'customer' => $this->input->get('cust_drop_down')
        );
    }
    else
    {
        $search = array(
        'customer' => 'all',
        'stage' => 'all',
        'lot_status' => 'all'
        );
    }
    $config = array();
    $config['base_url'] = base_url()."index.php/Home/on_hold_lot";
    $config['total_rows'] = $this->home_model->fetch_lots($search)->num_rows();
    $config['per_page'] = 10;
    $config['uri_segment'] = 3;
    $config['next_link'] = 'Next';
    $config['prev_link'] = 'Previous';

    $this->pagination->initialize($config);

    $page = ($this->uri->segment(3)) ? $this->uri->segment(3) : 0;
    $results = $this->home_model->fetch_lots($search, $config['per_page'], $page)->result_array();

    $data['disp_rows'] = $results;
    $data['links'] = $this->pagination->create_links();

    return $this->load->view('home/lot_disposition', $data);
}

In your Model make modificaiton in the search function as below :

Model

public function fetch_lots($search = null, $limit = null, $start = 0)
{
   if($limit != null){
       $this->db->limit($limit, $start);
    }


    if($search != null && $search['customer'] != 'all')
    {
        $this->db->where('customer', $search['customer']);
    }

    $query = $this->db->get('disp_dummy');

    if($query->num_rows() > 0)
    {
       return $query;
    }
    else
    {
        return false;
    }
}