Hi everybody, I’m looking for some help. I’ve been working in some querys and I need to display those results with pagination. My database is Postgres and I handle the querys without Active record, and the mostly examples on the web are made with Active record.
With my code is presented the first results of the query using limits, but when I press the next page with the following results it doesn’t find anything, but everything is correctly coded and works. I test it in some points and the results appear but not when I try the pagination.
I think the problem is with the URI but a cannot find the answer
This is my model:
<?php
class Buscador_model extends CI_Model {
function __construct() {
parent::__construct();
}
function buscar($frase) {
// Execute our SQL statement and return the result
$sql = "SELECT i.codigo_investigacion, i.titulo,m.nombre AS materia,t.nombre AS tema,cast(i.fecha as date),i.path ,i.palabras_claves,i.resumen
FROM scc_investigaciones i
INNER JOIN scc_materias m ON m.codigo_materia = i.codigo_materia
INNER JOIN scc_temas t ON t.codigo_tema = i.codigo_tema
WHERE (translate(lower(i.palabras_claves), 'áéíóúñÑ', 'aeiounn') ILIKE '%" . $frase . "%' OR translate(lower(i.titulo), 'áéíóúñÑ', 'aeiounn') ILIKE '%" . $frase . "%') AND (i.estado <>3)
ORDER BY i.fecha DESC";
$query = $this->db->query($sql);
if ($query->num_rows() > 0) {
return $query;
//return $query->result();
} else {
return FALSE;
}
}
function buscar_paginado($frase,$maximo,$inicio) {
$this->db->limit($maximo, $inicio);
// Execute our SQL statement and return the result
$sql = "SELECT i.codigo_investigacion, i.titulo,m.nombre AS materia,t.nombre AS tema,cast(i.fecha as date),i.path ,i.palabras_claves,i.resumen
FROM scc_investigaciones i
INNER JOIN scc_materias m ON m.codigo_materia = i.codigo_materia
INNER JOIN scc_temas t ON t.codigo_tema = i.codigo_tema
WHERE (translate(lower(i.palabras_claves), 'áéíóúñÑ', 'aeiounn') ILIKE '%" . $frase . "%' OR translate(lower(i.titulo), 'áéíóúñÑ', 'aeiounn') ILIKE '%" . $frase . "%') AND (i.estado <>3)
ORDER BY i.fecha DESC LIMIT " . $maximo . " OFFSET " . $inicio . "";
$query = $this->db->query($sql);
if ($query->num_rows() > 0) {
//return $query;
return $query->result();
} else {
return FALSE;
}
}
}
This is my controller:
function buscar_2() {
//$this->load->library('pagination');
if(!empty($_POST['frase'])){
$frase = $_POST['frase'];
$valido = $this->buscador_model->buscar($frase);
if (!$valido) {
$this->error_buscar_2();
}
else {
$inicio = $this->uri->segment(3);
$per_page = 2;
$total_rows = $this->buscador_model->buscar($frase)->num_rows();
if(empty($inicio)){
$inicio = 0;
}
$config['base_url'] = base_url().'index.php/buscador/buscar_2/';
$config['per_page'] = $per_page;
$config['prev_link'] = 'anterior';
$config['next_link'] = 'siguiente';
$config['first_link'] = '<<';
$config['last_link'] = '>>';
$config['total_rows'] = $total_rows;
$this->pagination->initialize($config);
$data['main_content'] = 'buscador_menu/buscar';
$data['investigaciones'] = $this->buscador_model->buscar_paginado($frase, $per_page, $inicio);
$data['link'] = $this->pagination->create_links();
$this->load->view('includes/template', $data);
}
}
else {
return $this->error_buscar();
}
}
}
This is my View:
<?=$this->db->last_query();?>
<pre><?=print_r($investigaciones);?></pre>
<?php foreach ($investigaciones as $investigacion): ?>
<table class="table table-bordered">
<thead>
<tr >
<th colspan="6">
<?=$investigacion->codigo_investigacion." - ".$investigacion->titulo ?>
</th>
</tr>
</thead>
<tbody>
<tr>
<th>Codigo:</th><th>Rama:</th><th>Descriptor:</th><th>Fecha de creación:</th>
<th>Palabras clave:</th><th>Descargar:</th>
</tr>
<tr>
<td><?=$investigacion->codigo_investigacion ?></td>
<td><?=$investigacion->materia ?></td>
<td><?=$investigacion->tema?></td>
<td><?=$investigacion->fecha ?></td>
<td><?=$investigacion->palabras_claves ?></td>
<td><a class="btn" target="_blank" href="<?='localhost/'.$investigacion->path?>"><i class="icon-download"></i></a></td>
</tr>
</tbody>
</table>
<hr />
<?php endforeach; ?>
<?php if (isset($link)): ?>
<center> <?php echo $link ?> </center>
<?php endif ?>
