How to have pagination in search results page?

[nagyln]

I am fairly new and incompetent when it comes to PHP, so my question might be dumb. Sorry if it is.

I'd like to have a pagination on my search results because anytime there are many results, the page loads really slow. The website is connected to a phpmyadmin database.

This is how my pagination coding looks like:

<?php
require 'partials/header.php'; //navigation and database connection

if (isset($_GET['search']) && isset($_GET['submit'])) {
    $search = filter_var($_GET['search'], FILTER_SANITIZE_FULL_SPECIAL_CHARS);
    $query = "SELECT * FROM posts WHERE title LIKE '%$search%' ORDER BY date_time DESC";
    $posts = mysqli_query($connection, $query);
}
//if i don't comment this one out for the pagination: Cannot modify header information - headers already sent
else {
    header('location: ' . ROOT_URL . 'index.php');
    die();
}
?>

//pagination
<?php
$searches_per_page = 20;
$number_of_searches = mysqli_num_rows($posts);
$number_of_pages = ceil($number_of_searches/$searches_per_page);
if(!isset($_GET['page'])) {
    $page = 1;
} else {
    $page = $_GET['page'];
}
$this_page_first_searches = ($page - 1) * $searches_per_page;
$query = "SELECT * FROM posts WHERE title LIKE '%$search%' ORDER BY date_time DESC LIMIT " . $this_page_first_searches . ', ' . $searches_per_page;
$posts = mysqli_query($connection, $query);
?>

 

 

The HTML part is 100% working correctly, the only issue that I'm struggling with is that everytime I switch to another page through my pagination, it gives an error with these messages:

Quote

: Undefined variable: posts - for this line: $number_of_searches = mysqli_num_rows($posts);
: mysqli_num_rows() expects parameter 1 to be mysqli_result, null given in - for this line: $number_of_searches = mysqli_num_rows($posts);

: Undefined variable: search in -for this line: $query = "SELECT * FROM posts WHERE title LIKE '%$search%' ORDER BY date_time DESC LIMIT " . $this_page_first_searches . ', ' . $searches_per_page;

 

I am sorry if my explanation is unclear, I tried my best.

So, the pagination gets displayed correctly, the first result page works well too, but as soon as I click to page 2, the warnings above are getting displayed.

I have no idea how to fix this issue. Does anyone have an idea what to do?

[Barand]

When you get to page 2, are $_GET['search'] and $_GET['submit'] both set?

[nagyln]

15 minutes ago, Barand said:

When you get to page 2, are $_GET['search'] and $_GET['submit'] both set?

I've been working around with it and I think that could be the issue:

<div class="pag">
            <?php
            if($page > 1) {?>
                <li> <?php echo '<a class="paging" href="?search=&submit=?page=' . ($page - 1) . '">Back</a>';?></li>
            <?php }
            $start = max(1, $page - 2);
            $end = min($number_of_pages, $page + 2);
            for($p = $start; $p <= $end; $p++) {?>
                <li class="active_pag<?php echo $p==$page ? ' active' : ''; ?>"> <?php echo '<a class="paging" href="?search=&submit=?page=' . $p . '">' . $p . '</a> ';?></li>
            <?php }
            if($number_of_pages > $page) {?>
                <li> <?php echo '<a class="paging" href="?search=&submit=?page=' . ($page + 1) . '">Forward</a>';?></li>
            <?php }
            ?>
        </div>

The "href="?search=&submit=?page=" part might be the black sheep. First, I do not get the values from the search when I switch pages. Second, when I do this, now the errors disappeared, the URL changes but the actual results do not.

Do you know how to fix that?

[Barand]

Firstly, you need corectly formatted query stringd (you have another "?" before page= instead of a "&")

Secondly you need to pass the search value (urlencodeed) and a submit value (1)

echo '<a class="paging" href="?search='.urlencode($_GET['search']).'&submit=1&page=' . ($page + 1) . '">Forward</a>';

 

[nagyln]

12 minutes ago, Barand said:

Firstly, you need corectly formatted query stringd (you have another "?" before page= instead of a "&")

Secondly you need to pass the search value (urlencodeed) and a submit value (1)

You saved me! Thank you a lot!😻

[mac_gyver]

php has a function that will help you build the query string part of urls, including urlencoding it - http_build_query. if you search the forum for that function you will find a number of examples showing how to get any existing $_GET parameters, populate the page parameter, build the query string, and build the pagination links.

Edited August 1, 2022 by mac_gyver

[Barand]

2 hours ago, nagyln said:

You saved me! Thank you a lot

FYI - my preferred method is not to use links with query strings but to add a hidden "page" field to the search form. When the pagination buttons are clicked they update the hdiden field with their page number and resubmit the form.

Example (The table used is "pupil" table from my sql tutorial)

<?php
include 'db_inc.php';            //  creates pdo connection
$pdo = pdoConnect('jointute');   //  use your own

$searches_per_page = 5;

$search = $_GET['search'] ?? '';      // set defaukt values
$page = $_GET['page'] ?? 1;           //

//
//  Record count
//
$res = $pdo->prepare("SELECT COUNT(*) FROM pupil
                      WHERE lname LIKE ?
                     ");
$res->execute(["%{$search}%"]);
$number_of_pages = ceil($res->fetchColumn()/$searches_per_page);
$prev = $page > 1 ? $page - 1 : 1;
$next = $page < $number_of_pages ? $page + 1 : $number_of_pages;
$start = max(1, $page - 2);
$end = min($number_of_pages, $page + 2);

//
//  Get data for display
//
$res = $pdo->prepare("SELECT fname
                           , lname
                           , classid
                           , dob
                           , timestampdiff(YEAR, dob, CURDATE()) as age
                      FROM pupil
                      WHERE lname LIKE ?
                      ORDER BY lname, fname
                      LIMIT ?,?
                     ");
$res->execute(["%{$search}%", ($page-1)*$searches_per_page, $searches_per_page ]);
$pupils = '';
foreach ($res as $row) {
    $pupils .= "<tr><td>" . join("</td><td>", $row) . "</td></tr>" ;
}

?>
<!DOCTYPE html>
<html lang='en'>
<head>
<title>Search example</title>
<meta http-equiv='Content-Type' content='text/html; charset=utf-8'>
<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script type='text/javascript'>
    $().ready (function() {
        
        $(".paging").click( function() {
            let page = $(this).val()
            $("#page").val(page)                 // set page no in hidden form field
            $("#searchform").submit()            // resubmit the form
        })
        
    })
</script>
<style type='text/css'>
    body {
        background-color: black;
        color: white;
    }
    td , th {
        padding: 4px;
    }
</style>
</head>
<body>
<form id='searchform'>
    Search for <input type='text' name='search' value="<?=$search?>">
    <input type='hidden' name='page' id='page' value='<?=$page?>'>
    <input type='submit' value='Search'>
</form>
<?php
    if ($number_of_pages > 1) {
        echo "<br><br><button class='paging' value='$prev'>&lt;</button> ";
        for ($p = $start; $p <= $end; $p++) {
            echo "<button class='paging' value='$p'>$p</button> ";
        }
        echo "<button class='paging' value='$next'>&gt;</button>";
    }
?>
<br><br>
<table border='1'>
    <tr><th>First Name</th><th>Last Name</th><th>Class</th><th>DOB</th><th>Student Age</th></tr>
    <?=$pupils?> 
</table>

</body>
</html>