0

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... :)

Code Lover
  • 8,099
  • 20
  • 84
  • 154

2 Answers2

2

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();
Karan Thakkar
  • 1,492
  • 2
  • 17
  • 24
  • Thanks.. but when I tried it is giving me `Message: Trying to get property of non-object` error for every column like `$data->employee_id` etcc.. – Code Lover Jun 19 '14 at 09:54
  • @CodeLover its an array (`$query->result_array();`) so you must use `$data['employee_id']` instead of `$data->employee_id` – Karan Thakkar Jun 19 '14 at 09:55
  • Okay now this is fine but when I am not on the first page than it is ignoring filter if that item is on not that page. Is there anyway where it will check in entire table rather than just only on the page? It is also showing pagination if there is only one record found in filter result – Code Lover Jun 19 '14 at 10:01
  • I am sorry but couldn't get you. :( – Code Lover Jun 19 '14 at 10:09
  • reason for faulty pagination : `row_count()` of your `employees_model`considers the filters ? or just fetches all rows `(which is wrong, you must consider filters for getting $config['total_rows'])` – Karan Thakkar Jun 19 '14 at 10:23
  • Oh I see, so how can I consider filters row for that? I am just confuse at this point, since first time at the stage on pagination, filter using the framework – Code Lover Jun 19 '14 at 10:24
  • 2 ways for this. but the efficient one is `SQL_CALC_FOUND_ROWS ` with `FOUND_ROWS();` to follow. go through this http://stackoverflow.com/questions/2439829/how-to-count-all-rows-when-using-select-with-limit-in-mysql-query you can easily implement this in your `fetch_rows` function itself `fetch_rows returns an array` eg `$qt = $this->db->query('SELECT FOUND_ROWS() AS num_results'); $data=array('result'=>$query->result_array();,'count'=>$qt->row()->num_results)` – Karan Thakkar Jun 19 '14 at 10:31
1

Change your model like this:

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

Due to any reason if query unable to load the data, the $data is consider as undefined. Because the foreach is not executed.

Eranda
  • 868
  • 1
  • 10
  • 27
  • Your code is also working but I want to affect pagination based on the filter result. So when the number of result row is less than pagination per_page than the pagination should not be visible so on – Code Lover Jun 19 '14 at 10:05
  • if so I think your problem comes with `$this->uri->segment(4)` part. Make it sure you were passed correct values to the model through `fetch_rows` method. – Eranda Jun 19 '14 at 10:35
  • It is quite okay and getting everything works but just a filter stuffs on another page. – Code Lover Jun 19 '14 at 12:02