codeigniter CRUD operations with search and user defined per_page

0
votes

I'm designing a page which will have a facility to search and display result into a table with pagination and user has given a freedom to chose number of rows to be displayed per page.

Controller contain following function:

settings_admin.php

    function view_company(){
        $this->check_logged_in();
        $this->load->library('table');
        $this->load->library('pagination');
        $fields = $this->db->list_fields('company');
        $this->table->set_heading($fields);

        $field_to_search = 'all';
        if(isset($_POST['company_cols'])){
            $field_to_search = $_POST['company_cols'];
        }

        if(isset($_POST['search_text'])){
            $first=true;
            $search_text = $_POST['search_text'];
            if($field_to_search == 'all' && $search_text!=''){
                foreach ($fields as $field){
                    if($first){
                        $this->db->like($field,$search_text);
                        $first=false;
                    }else{
                        $this->db->or_like($field,$search_text);
                    }
                }
            }else if($search_text!=''){
                $this->db->like($field_to_search,$search_text);
            }
        }

        if(isset($_POST['num_of_rows'])){
            $config['per_page'] = $_POST['num_of_rows'];
            $this->session->set_userdata('rows_per_page', $_POST['num_of_rows']);
        }else if($this->session->userdata('rows_per_page')!=null){
            $config['per_page'] = $this->session->userdata('rows_per_page');
        }else{
            $config['per_page'] = 10;
        }
        $result = $this->db->get('company',$config['per_page'], $this->uri->segment(3));

        $config['total_rows'] = $this->db->get('company')->num_rows();
        //$config['total_rows'] = $result->num_rows();

        $config['base_url'] = site_url('/settings_admin/company');
        $config['num_links'] = 5;
        $config['full_tag_open'] = '<div id="pagination">';
        $config['full_tag_close'] = '</div>';
        $config['records'] = $result;
        $this->pagination->initialize($config);
        //var_dump($config);
        //var_dump($_POST);
        $config['num_of_rows'] = $config['per_page'];
        $this->load->view('admin/view/company',$config);
    }

View is as follows:

company.php

<?php
    $data = array(
        'title' => 'Company',
        'form_action' => 'settings_admin/company'
    );
?>

<?php $this->load->view('includes/header',$data); ?>
<?php $this->load->view('sidebar_admin_controls'); ?>

<div class="content">
    <h3>Companies</h3>
    <div align='right'>
        Search:
        <input style="width:15em;" type="text" name="search_text"
        value="<?php echo set_value('search_text'); ?>" placeholder="search company details"/>
        in
        <select name="company_cols">
            <option value='all'>all</option>
            <?php
                $fields = $this->db->list_fields('company');
                foreach ($fields as $field){
                    echo "<option value='$field'>$field</option>";
                }
            ?>
        </select>
        <input type="submit" name="search" value="Search"/>
    </div>
    <div align='left'>
        <?php
            if(!isset($num_of_rows)){
                $num_of_rows = 10;
            }
        ?>
        Rows per page: <input style="width:4em;" type="text" name="num_of_rows" value=<?php echo $num_of_rows;?> placeholder="rows"/>
        <input type="submit" name="search" value="Go"/>
    </div>
    <div class="view_table" id="view_table">
        <?php
            echo $this->table->generate($records);
        ?>
    </div>
    <?php
        echo "<br/>";
        echo $this->pagination->create_links(); // can run and show me the ok..
        // when i press on page link, it goes back to default #of rows per page (10)
    ?>
    <br/>
    <div align="center" id="option_buttons">
        <input type="submit" name="add" value="Add New"/>
        <input type="submit" name="update" value="Update"/>
        <input type="submit" name="delete" value="Delete"/>
        <input type="submit" name="print" value="Print"/>
    </div>

</div><!-- end .content -->
<?php $this->load->view('includes/footer'); ?>

when I enter number of rows and press GO button, it return with desired pagination but number of page links is not equal to desired ones. e.g. if search returns only one row, it shouls be displayed in table without any page link.But in my case it's displaying single row (search result) along with all page links (equal to total_rows/per_page).

This may be due to the fact that total_rows is been set as $config['total_rows'] = $this->db->get('company')->num_rows(); which should actually be number of search result.

But how can I set it if I need to add query parameters as given in code (e.g. 'where' clause)?

3
phpmysqlcodeigniterpagination

3 Answers

0
votes

in this case you can use this code. first add this library to construct in your controller.

See the demo-

class welcome extends CI_Controller {

function __construct()
{
    parent::__construct();
    $this->load->model('common_model', 'Common_model', true);
    $this->load->library('pagination');
}

then in you create any function, here i create one function name "bill_list"

public function bill_list()
{
    /******** pagination ********/
      $pageNo = $this->uri->segment(2, 0);
      $data['page_no'] = $pageNo;
      $config['uri_segment'] = 2;
      $config['base_url'] = BASEURL . 'bill_list';

      //get the total count list
      $config['total_rows'] = $this->Common_model->count_list('lading_bill');
      $config['per_page'] = 20; //per page limit
      $this->pagination->initialize($config);
      $data['pagination'] = $this->pagination->create_links();
      $startLimit = ($pageNo) ? $pageNo : 0;
      $per_page = $config['per_page'];
    /******** pagination ********/

      //get the  list with start limit and per page.

      // here 'lading_bill' is table name
     $data['bill_list'] = $this->Common_model>get_all_list('lading_bill',$startLimit, $per_page);


     $data['mainContent'] = $this->load->view('bill_list', $data, true);
     $this->load->view('template', $data);
}

then just add $pagination variable to you bill_list view page.

you can copy paste this code. and please check that pagination library available or not in library folder. thats it.

0
votes

For this you need two function in common model -

// return total row result.
function count_list($table)
{
    $sql = "SELECT COUNT(*) AS result_count FROM $table";
    $result = $this->db->query($sql)->row_array();
    return $result['result_count'];
}



//Search Total  Result Using Pagination
function get_all_list($table_name, $startLimit = NULL, $per_page = NULL)
{
    if (isset($startLimit))
        $this->db->limit($per_page, $startLimit);

    $result = $this->db->get($table_name)->result_array();
    return $result;
}
0
votes

Try to give a chance to GroceryCRUD: http://www.grocerycrud.com/