rodrix rodrix - 4 months ago 21
SQL Question

how could i get search results to work with the pagination?

Hi friends so i got this search criteria thing which has option for either select a value or select all but any ways the issue here is i haven't a clue how to get the results to stay intact when pagination takes place?

as of now if click next or any page it would show blank page because nothing gets passed other than just the page numbers.

here is my codes

<?php

$title = clean($_REQUEST['title']);
$name = clean($_REQUEST['name']);
$description = clean($_REQUEST['description']);

$criteria = array();

if($title !='')
{
$criteria[] = "title = '".$title."'";
}elseif($title =='All')
criteria[] = "title = ''";
}

if($name !='')
{
$criteria[] = "name = '".$name."'";
}elseif($name =='All')
criteria[] = "name = ''";
}

if($description !='')
{
$criteria[] = "description = '".$description."'";
}elseif($description =='All')
criteria[] = "description = ''";
}

try {

$total = $db->query('SELECT COUNT(*) FROM table WHERE '.implode(' AND ', $criteria).'')->fetchColumn();
global $per_page;

$pages = ceil($total / $per_page);

$page = min($pages, filter_input(INPUT_GET, 'page', FILTER_VALIDATE_INT, array(
'options' => array(
'default' => 1,
'min_range' => 1,
),
)));


$offset = ($page - 1) * $per_page;
$start = $offset + 1;
$end = min(($offset + $per_page), $total);


$stmt = $db->prepare('SELECT * FROM table WHERE '.implode(' AND ', $criteria).' ORDER BY id DESC LIMIT :per_page OFFSET :offset');

$stmt->bindParam(':per_page', $per_page, PDO::PARAM_INT);
$stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
$stmt->execute();

if ($stmt->rowCount() > 0) {
$stmt->setFetchMode(PDO::FETCH_ASSOC);
$iterator = new IteratorIterator($stmt);
foreach ($iterator as $row) {

echo $row['id'];
}

echo '<div id="pagination">
<div id="pagiCount">';
$prevlink = ($page > 1) ? '<span id="prev"><a href="?page=1" title="First page">First</a></span> <span id="prev"><a href="?page=' . ($page - 1) . '" title="Previous page"><<</a></span>' : '';
$nextlink = ($page < $pages) ? '<span id="next"><a href="?page=' . ($page + 1) . '" title="Next page">>></a></span> <span id="next"><a href="?page=' . $pages . '" title="Last page">Last</a></span>' : '';
echo '<div id="paging"><p><small>', $prevlink, ' Page ', $page, ' of ', $pages, '', $nextlink, '</small></p></div>';
echo '</div></div>';
} else {
echo '<p>Nothing found.</p>';
}
} catch (Exception $e) {
echo '<p>Nothing found.</p>';
}
?>


i would really be very greatful to you for your kind help with this. cheers

Answer

You have to regenarate the query string for with the same parameters except the page.

So I think that something like that will work:

//regenerate query string with new page 
$cur_params = $_GET;
unset($cur_params["page"]);
$cur_url = strtok($_SERVER["REQUEST_URI"],"?") . "?" . http_build_query($cur_params, "", "&");
$page_back_url = $cur_url . (empty($cur_params) ? "" : "&") . "page=" . ($page - 1);
$page_next_url = $cur_url . (empty($cur_params) ? "" : "&") . "page=" . ($page + 1);
$page_last_url = $cur_url . (empty($cur_params) ? "" : "&") . "page=$pages";