Adding Pagination to search results with PHP

Question

i'm struggling with adding pagination with php to my search results. Currently I search on index.php for a term and it takes me to search.php with a list of results that match from my database. This part works fine but i'd like it to show 10 results per page rather than every result on the same page. How do I achieve this? I've taken a look here and here but my code is a little different and i'm struggling to implement the suggestions. Any help would be greatly appreciated, even if to just point me in the correct direction.

    <?php
        if (isset($_POST['submit-search'])) {
            $search = mysqli_real_escape_string($conn, $_POST['search']);
            $sql = "SELECT * FROM people WHERE location LIKE '%$search%'";
            
            $result = mysqli_query ($conn, $sql);
            $queryResult = mysqli_num_rows($result);

            echo " ".$queryResult." RESULTS";

            if ($queryResult > 0){
                while ($row = mysqli_fetch_assoc($result)){
                    echo "<div>
            <h3>".$row['firstname']."</h3>
            <p>".$row['lastname']."</p>
            <p>".$row['location']."</p>
            <p>".$row['profession']."</p>
            </div>";
                }       
            }   
        }
    ?>

score 0 · Answer 1 · answered Jun 24 '20 at 17:22

I will explain the logic here and give you the link in which the pagination is implemented.

In your present code, you are fetching all the results from the DB and displaying it in the template.

What you should actually do is make use of the MySQL's LIMIT clause, and fetch the results in batches.

LIMIT clause can be used like below:

SELECT * FROM people WHERE location LIKE '%$search%' LIMIT 5, 10

Where 5 is OFFSET and 10 is Row Count. That is, the above query only fetches the result from 5 to 15 (Given the rows are continuous ids).

More info on: MYSQL LIMIT

Now, you can think OFFSET as Current page * Number of people listed on a page and Row Count as Number of people listed on a page (constant). So, for the first page you will use the following query:

SELECT * FROM people WHERE location LIKE '%$search%' LIMIT 0, 10
Where, 
Current page = 0 * 10;
number of people listed in a page = 10;

For the second page:

SELECT * FROM people WHERE location LIKE '%$search%' LIMIT 10, 10
Current page = 1*10;
number of people listed in a page = 10;

and so on... The only variable that changes here is the page, which you can request through URL.

I will add the link to a github repo which implements this logic. Please go through that and understand the working.

Repo: PHP Pagination

Adding Pagination to search results with PHP

1 Answers1