CODEWITHSUNDEEP
phppagination
Cottonmouth
Cottonmouth

Reputation: 19

PHP Pagination with SQL_CALC_FOUND_ROWS and LIMIT

Hello my dear friends,

My goal is to paginate through my database entries, while using SQL_CALC_FOUND_ROWS and FOUND_ROWS, because I also have a search function

The search results are there, I just cannot paginate through them.

Probably my code is a total mess for the PHP Pros here, please don’t worry about commenting on that. Any help is greatly appreciated. I am just starting out on PHP and I am highly motivated to become a Pro one day as well.

What is it that I have to do in order to make the pagination work, so that I can paginate while having a search function on the page?

DETAILS:

connect

try {
    $conn = new PDO($server, $user, $pass);
    $conn -> setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    $conn -> setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    // echo "success";
} catch (PDOException $e) {
    echo "ERROR: " . $e -> getMessage();
}

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL & ~E_NOTICE);

index 

if (!isset($_GET['page'])) { $page = 1;
} else { $page = $_GET['page']; }

if ($page < 1) { $page = 1;
} elseif ($page > $last_page) {
    $page = $last_page; }

$search = $_GET['search'];
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Document</title>
</head>
<body>
    <form action="<?php $_SERVER['PHP_SELF'] ?>" method="GET">
        <input type="text" name="search" value="<?php $search ?>">
        <input type="submit" value="search">
    </form</body>
</html>

<?php
$start = 0;
$limit = 3;

$query = "
    SELECT SQL_CALC_FOUND_ROWS * FROM tbl_articles
    WHERE 
        (art_headline LIKE '%{$search}%')
        OR 
        (art_content LIKE '%{$search}%')
    LIMIT $start, $limit
";

$stmt = $conn -> prepare($query);
$stmt -> execute();

while ($res = $stmt -> fetch(PDO::FETCH_ASSOC)) {
    echo '<h1>'.$res['art_headline'].'</h1>'."\n";
    echo '<p>'.$res['art_content'].'</p>';
    echo '<b>'.$res['art_author'].'</b>'."\n";
    echo '<span>'.date("d.m.Y", strtotime($res['art_datetime'])).'</span>'."\n\n";
    echo '<b>'.$res['art_id'].'</b>'."\n";
}

$query = "
    SELECT FOUND_ROWS() AS num FROM tbl_articles
";

$stmt = $conn -> prepare($query);
$stmt -> execute();
$max = $stmt -> fetch(PDO::FETCH_ASSOC);
var_dump($max[num]);

$total_pages = $max[num];
// $stmt = $conn -> query($query) -> fetchColumn();
$last_page = ceil($total_pages / $limit);

// loop through pages
echo "<div><nav>";
for ($i = 1; $i <= $last_page; $i++) {

    if (
            ($i == 1)
            || 
            ($i == $last_page)
            ||
            (
                ($i >= ($page - 2)) 
                && 
                ($i <= ($page + 2))
            )
        ) {

        if ($last_i != ($i - 1)) {
            $out .= " ... ";
        }

        if ($page == $i) { $out .= "["; }
        $out .= "<a href=\"?page=$i&search=$search\">$i</a>";
        if ($page == $i) { $out .= "] "; }

        $out .= " ";
        $last_i = $i;

        $prev = $page - 1;
        $prev_link = "<a href=\"?page=$prev&search=$search\">back</a>\n";
        $next = $page + 1;
        $next_link = "<a href=\"?page=$next&search=$search\">next</a>\n";
    }
}
echo $prev_link;
echo $out;
echo $next_link;
echo "</nav></div>";
?>

Any help is greatly appreciated, from a PHP beginner.

Upvotes: 0

Views: 289

Answers (1)

Cottonmouth
Cottonmouth

Reputation: 19

ok guys, my PHP Pro Mentor helped me solve it.

Here we go:

to fix the search results display I had to insert

if ($_GET['page'] > 1) {
    $start = ($_GET['page'] - 1) * $limit;
}

between the $start and $limit variables and the first $query

to fix the pagination I just had to insert:

$page = $_GET['page'];

on top of the script.

Upvotes: 1

Related Questions