CodeIgniter: Pagination with Filter Rows

Question

I am trying to make data table with some filter options and pagination. (Also later want to add sorting column).

If I tried to filter data on page one than it is working fine. But when I am on another page/s and try to filter the data than it is giving error invalid argument supplied for foreach()

Model

public function fetch_rows($limit, $start)
{
    $emp_id = $this->input->post('emp_id');
    $this->db->like('employee_id',$emp_id);

    $emp_fname = $this->input->post('emp_fname');
    $this->db->like('first_name',$emp_fname);

    $emp_login = $this->input->post('emp_login');
    $this->db->like('login_id',$emp_login);

    $emp_position = $this->input->post('emp_position');
    $this->db->like('position',$emp_position);

    $this->db->limit($limit, $start);
    $query = $this->db->get($this->_table_name);

    var_dump($query);

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

Controller

public function employees()
{
    $this->data['title'] = '<i class="fa fa-users"></i> ' . lang('emp_all');

    $config                = array();
    $config['base_url']    = base_url() . 'admin/hr/employees';
    $config['total_rows']  = $this->employees_model->row_count();
    $config['per_page']    = 10;
    $config['uri_segment'] = 4;

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

    $page                  = ($this->uri->segment(4)) ? $this->uri->segment(4) : 0;
    $this->data['results'] = $this->employees_model->fetch_rows($config['per_page'], $page);
    $this->data['links']   = $this->pagination->create_links();

    $this->load->view('hr/employees/index', $this->data);
}

View

<div class="form-group">
    <div class="row">
        <?= form_open(); ?>
        <div class="col-xs-1">
            <?= form_input(array('name' => 'emp_id', 'id' => 'emp_id', 'class' => 'form-control', 'placeholder' => 'Employee ID')); ?>                                
        </div>
        <div class="col-xs-2">
            <?= form_input(array('name' => 'emp_fname', 'id' => 'emp_fname', 'class' => 'form-control', 'placeholder' => 'First Name')); ?>                                
        </div>
        <div class="col-xs-1">
            <?= form_input(array('name' => 'emp_login', 'id' => 'emp_login', 'class' => 'form-control', 'placeholder' => 'Login Id')); ?>                                
        </div>
        <div class="col-xs-2">
            <?= get_positions('dropdown', 'emp_position'); ?>                                
        </div> 
        <div class="col-xs-1">
            <?= form_submit('filters', 'Go', 'class="btn-default btn"'); ?>
        </div>
        <div class="col-xs-2">

        </div>

        <?= form_close(); ?>
    </div>
</div><!-- End filter form -->

<table cellpadding="0" cellspacing="0" border="0" class="table table-striped table-bordered datatables" id="">
    <thead>
        <tr class="active">
            <?= is_system_admin() ? '<th class="center">ID<span>*</span></th>' : NULL; ?>
            <th>Photo</th>
            <th>Employee ID</th>
            <th>Name</th>
            <th>Position</th>
            <th>Login ID</th>
            <th>Email</th>
            <th>Status</th>
        </tr>
    </thead>
    <tbody>
        <?php
        if (count($results) > 0): foreach ($results as $data):                    
                echo '<tr>';
                echo is_system_admin() ? '<td class="center">' . $data->id . '</td>' : NULL;
                echo '<td><img src="' . get_employee_snap($data->employee_id, $data->employee_snap) . '" alt="snap" width="64"/></td>';
                echo '<td>' . $data->employee_id . '</td>';
                echo '<td>' . $data->first_name . ' ' . $data->last_name . '</td>';
                echo '<td>' . get_position_by_code($data->position) . '</td>';
                echo '<td>' . $data->login_id . '</td>';
                echo '<td>' . $data->email . '</td>';
                echo '<td>' . $data->employment_status . '</td>';
                echo '</tr>';
            endforeach;
        else:
            echo show_alert('No Postions record found in database.', FALSE, 'warning');
        endif;
        ?>                                     
    </tbody>
</table><!--end table-->
<?= $links; ?>

How to solve this issue where I can filter no matter on which page I am, I also want to make column sortable as well.

Note: I also want to update pagination number as per the filter result too.

Million thanks... :)

Which foreach did you get error? in model or view file? Did you try dump $query->result before foreach loop to sure not empty? — Bora

Karan Thakkar Karan Thakkar · Accepted Answer · 2014-06-19T09:49:32

instead of

        foreach ($query->result() as $row)
        {
            $data[] = $row;
        }
        return $data;

you can simply use

    return $query->result_array();

and i found that $this->data['results'] will be false if no result is found and FALSE will cause error in foreach since its not an array.

Note : if the result set is empty it will give a blank array;

so in model instead of

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

just use

return $query->result_array();

CodeIgniter: Pagination with Filter Rows

2 Answers