johram pong johram pong - 1 year ago 54
MySQL Question

error in pagination if no records in database

so if my offset is 0 i get a negative result like



and my code is

$query = "SELECT COUNT(*) FROM applied WHERE memberID = :memberID";
$stmt = $db->prepare($query);
$stmt->bindParam(':memberID', $uid, PDO::PARAM_INT);
$total = $stmt->fetchColumn();

$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;

$query = "SELECT * FROM applied WHERE memberID = :memberID ORDER BY id DESC LIMIT :per_page OFFSET :offset";

$stmt = $db->prepare($query);
$stmt->bindParam(':per_page', $per_page, PDO::PARAM_INT);
$stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
$stmt->bindParam(':memberID', $uid, PDO::PARAM_INT);

$result = $stmt->fetchAll();

would this be a solution

$offset = ($page - 1) * $per_page;
if($offset < 0){
$offset = 10;

or someone here could show me a better way to deal with this issue

appreciate your help

Answer Source

Use the abs function of PHP to always make sure your offset is a positive number.

$offset = abs(($page - 1)  * $per_page);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download