How to paginate search results from a select form in PHP

Question

I got a form with 3 different dropdowns, the idea is to paginate the search results, which works fine on the first page, but when I get two or more pages it displays the entire database and not only my search.

[edit]: okay I updated my code with CI's paginator class. I'm still having trouble with a similar issue. Now it paginates correctly, but when I click on the link it tells me this: Unknown column ' IS NULL LIMIT 9, 9' in 'where clause'

[edit2]: just in case anybody out there finds this, I was able to fix my problem using this method here http://net.tutsplus.com/tutorials/php/codeigniter-from-scratch-search-results-without-query-strings-2/

Here is my code.

Model: (I got two functions, one for giving back the entire search results, and the other one to give back results with limit and offset)

function buscarHotel($dpto, $ciudad, $id_precio, $limit = 10, $offset = 0)
{

    $w=array();
        if (!empty($dpto)) 
        {
             $w[]="cod_ciudad IN (SELECT cod_ciudad FROM ciudad WHERE departamento_id = $dpto)"; 
        }
        if (!empty($ciudad)) 
        {
             $w[]="cod_ciudad IN (SELECT cod_ciudad FROM ciudad WHERE cod_ciudad = $ciudad)"; 
        }
        if (!empty($id_precio)) 
        {
             $w[]="des_precio_referencial IN (SELECT rango FROM preciohotel WHERE id = $id_precio)"; 
        }

    if (count($w)) $where="WHERE ".implode(' AND ',$w); else $where='';
    $query = $this->db->query("select id, nom_comercial, cod_ciudad, des_foto 
                               FROM hotel 
                               $where");
                               #ORDER BY id desc
                               #LIMIT $limit
                               #OFFSET $offset ");

    return $query->result();

}

function buscarHotelLimit($dpto, $ciudad, $id_precio, $limit)
{

    $w=array();
        if (!empty($dpto)) 
        {
             $w[]="cod_ciudad IN (SELECT cod_ciudad FROM ciudad WHERE departamento_id = $dpto)"; 
        }
        if (!empty($ciudad)) 
        {
             $w[]="cod_ciudad IN (SELECT cod_ciudad FROM ciudad WHERE cod_ciudad = $ciudad)"; 
        }
        if (!empty($id_precio)) 
        {
             $w[]="des_precio_referencial IN (SELECT rango FROM preciohotel WHERE id = $id_precio)"; 
        }

    if (count($w)) $where=implode(' AND ',$w); else $where='';
    /*$query = $this->db->query("select id, nom_comercial, cod_ciudad, des_foto 
                               FROM hotel 
                               $where");*/
                $this->db->select('id, nom_comercial, cod_ciudad, des_foto');
                $this->db->where("$where");
                $query = $this->db->get('hotel', $limit, $this->uri->segment(3));

    return $query->result();

}

Controller:

function buscar()
{
    $dpto = (int)$this->input->post('departamentos');
    $ciudad = (int)$this->input->post('ciudades');
    $precio = (int)$this->input->post('precios');   

    $this->load->model('mhotel');
            $this->load->model('mciudad');

            $total = (int)count($this->mbuscador->buscarHotel($dpto, $ciudad, $precio));
    $limit = 9;


    $this->load->library('pagination');

    $config['base_url'] = 'http://infohotelperu.com/buscador/buscar';
    $config['total_rows'] = $total;
    $config['per_page'] = $limit;
    $config['num_links'] = 5;

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


    $info = $this->mbuscador->buscarHotelLimit($dpto, $ciudad, $precio, $limit);        

    $results['info'] = $info;

    $this->load->view('resultados', $results);
}

And in my view I just got

<?php echo $this->pagination->create_links(); ?>

is there a reason why you are not using codeigniter's default pagination? — He Hui
Just starting to learn CI so I'm not really sure how to use it. I'll get more into it with time, but right now client needs the searcher to work. If, however, what I'm doing is so far off that it'd be easier to just learn CI's pagination, I guess I'll try that instead. — Ant100
oh, I forgot to mention I'm not working from scratch, this is how the old programmer did it, I just took the pagination code applied in another part of the site and put it in the search results page. — Ant100

Frederik Voordeckers Frederik Voordeckers · Accepted Answer · 2013-04-02T03:13:13

Here is the code from my controller:

$this->load->model("entries");

//pagination
$this->load->library('pagination');

$config['full_tag_open'] = '<div class="full_pagination">';
$config['full_tag_close'] = '</div>';

$config['next_tag_open'] = '<span class="prev_next_pagination">';
$config['next_tag_close'] = '</span>';

$config['prev_tag_open'] = '<span class="prev_next_pagination">';
$config['prev_tag_close'] = '</span>';

$config['num_tag_open'] = '<span class="num_pagination">';
$config['num_tag_close'] = '</span>';

$config['cur_tag_open'] = '<span class="cur_pagination">';
$config['cur_tag_close'] = '</span>';
$config['next_link'] = 'Next';
$config['prev_link'] = 'Vorige';
$config['base_url'] = base_url().'index.php/blog/index';

$config['total_rows'] = $this->entries->countAll();
$config['per_page'] = 3;
$config['num_links'] = 10;

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

//end pagination config

$entries = $this->entries->get_all($config['per_page']);

$data['entries'] = $entries;
$data['titel']="Blog - Frederik";
$data['main_content']='index';
$this->load->view('includes/template', $data);

And this is in my view:

foreach($entries as $entry){
     // display entry info here
}

<div id="pagination" align="center">
    <?php echo $this->pagination->create_links(); ?>
</div>

This should work! Don't forget to load the library! ;)

How to paginate search results from a select form in PHP

2 Answers