Codeigniter pagination with my own query

Question

first be gentle im a begginer.

I have a problem with codeogniter pagination and im totally clules.

I watched a lot of tuts on the net and never got any aswer.

My problem is, im building a real-estate site, it has 2 types of estate, for sale and for rent.

My query looks like this

function for_sale()
{
    $query = $this->db->query(" SELECT city, rand_id, price, image FROM estate WHERE type = 'for_sale' ");
    if ($query->num_rows() > 0)
       {
       foreach ($query->result() as $f)
         {
            $for_sale[] = $f;
        }
        return $for_sale;
        }
    }

my controller

function index()
            {
                $this->load->view("header");
                $this->load->model('estate_model');
                $for_sale['result'] = $this->estate_model->for_sale();
                $this->load->library('pagination');
                $config['base_url'] = 'http://localhost/kpi/for_sale/index';
                $config['total_rows'] = 22;
                $config['per_page'] = 9; 
                $config['num_links'] = 19; 

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

            //  $data['records'] = $this->db->get('ingatlan', $config['per_page'], $this->uri->segment(3)); 

                $this->load->view("for_sale_view",  $for_sale);
                $this->load->view("footer");
            }

and my view

foreach($results as $r) {
        echo '<div class="grid_4">';
            echo '<p class="title">Estate for sale</p>';
            echo '<div class="thumbnail">
            <div class="info">
                <p class="bar">'.$r->city.'</p>
                <p></p>
            </div>
            <a href="#">'.$r->image.'</a></div>';
            echo '<div class="more"><a href="#">View details</a></div>';
        echo '</div>';
    }

So my problem is i cant figure this out how to use it with my query, i watched lots of tuts, that i need to load in the table library, and pass a data array like this

$data['records'] = $this->db->get('estate', $config['per_page'], $this->uri->segment(3));

anf give the per page this result `$this->db->get('estate')->num_rows(); but i would like this with my query, and not with a table.

So can someone hive me a hint?

Thank you

you can build your function that contains your query in controller and call that function from controller to get result from your query. — Punit
You have to be careful when dealing with index(). It gets called when either you call the controller with no second parameter or if you explicitly define it (my_controller/index). Have you tried setting the pagination base url like, $base_url = base_url().'for_sale/index/'; (I assume you base url is localhost/kpi) and setting the uri_segment to be 3? — Rooneyl

mwallisch mwallisch · Accepted Answer · 2011-11-30T10:16:00

... i need to load in the table library, and pass a data array ...

how you display your data is totally up to you. the html-table class can help you with that, but it is not necessary to use it. your view should work just fine except for the fact that you want to iterate over $results which will be empty because you put your data into $for_sale['result'] (results != result).

in order for the pagination library to work with your query, you have to pass your query 2 parameters:

how many rows/records should be loaded from your db (= $config['per_page'])
how many rows/records should be skipped at the beginning (also known as 'offset', this value will be provided by the pagination library = $this->uri->segment(3))

So instead of:

$for_sale['result'] = $this->estate_model->for_sale();

Model:

function for_sale()
{
    $query = $this->db->query(" SELECT city, rand_id, price, image FROM estate WHERE type = 'for_sale' ");
    ...
}

you should try:

$for_sale['result'] = $this->estate_model->for_sale($config['per_page'],$this->uri->segment(3));

Model:

function for_sale($per_page, $offset) {
   $query = $this->db->query(" SELECT city, rand_id, price, image FROM estate WHERE type = 'for_sale' LIMIT $offset, $per_page");
   ...
   return $query->result();
}

One last thing: the pagination library has to be initialized BEFORE you query the database.

And another last thing: $config['total_rows'] = 22; you will probably want to replace that with something like $this->estate_model->get_for_sale_total_rows()

Hope this helps.

Codeigniter pagination with my own query

1 Answers