PHP - Limit While Pdo Mysql Results

Hi all,

I need your help as I have got a problem with display the 50 rows in the search results. I want to limit 50 rows in per page and if I have less than 50 rows like 21 rows in the page 2 then display the 21 rows.

I have got a problem with the limit, because when I tried to use `LIMIT 0, 50`, it will display total 71 rows which I only want to display no more than 50 in per page.

Here is what I have tried:

    $search_mailbox = $link->prepare("SELECT * FROM $folder WHERE from_email LIKE ? OR subject LIKE ? OR message LIKE ? ORDER BY received_date DESC LIMIT 0, 50");
    $search_mailbox->execute([$searchemail, $searchsubject, $searchmessage]);

And I have also tried this:

    $search_mailbox = $link->prepare("SELECT * FROM $folder WHERE from_email LIKE ? OR subject LIKE ? OR message LIKE ? ORDER BY received_date DESC LIMIT $offset, $limit");
    $search_mailbox->execute([$searchemail, $searchsubject, $searchmessage]);

It will not limit the rows I want. It will still show the 71 rows.

Here is the full code:

<?php
    
    // Initialize the session
    session_start();
    
    //Connect to the database
    require_once('config.php');
    
    $searchemail = '%' . 'gmail.com' . '%';
    $searchsubject = '%' . 'gmail.com' . '%';
    $searchmessage = '%' . 'gmail.com' . '%';
    
    $mailbox = $link->prepare("SHOW TABLES");
    $mailbox->execute();
    $folders = array();
    $total = 0;
    $total_rows = 0;
    $i = 0;
            
    while ($folder = $mailbox->fetch(PDO::FETCH_NUM)) {
        $folder = $folder[0];
    
        if (strpos($folder, 'users') !== false) {
            $folder = str_replace('users', '', $folder);
        }
        else
        {
            $folders[$i] = $folder;
        }
        $i++;
    }
    
    foreach($folders as $folder) {
        $search_mailbox = $link->prepare("SELECT * FROM $folder WHERE from_email LIKE ? OR subject LIKE ? OR message LIKE ? ORDER BY received_date DESC LIMIT 0, 50");
        $search_mailbox->execute([$searchemail, $searchsubject, $searchmessage]);
        
        if ($search_mailbox->rowCount() > 0) {
            $total += $search_mailbox->rowCount();
        }
    }
    
    $page = 1;
    $limit = 50;
    //$per_page = 50;
    //$pages = ceil($total / $per_page);
    
    $offset = ($page - 1) * $limit;
    
    
    foreach($folders as $folder) {
        
        $search_mailbox = $link->prepare("SELECT * FROM $folder WHERE from_email LIKE ? OR subject LIKE ? OR message LIKE ? ORDER BY received_date DESC LIMIT $offset, $limit);
        $search_mailbox->execute([$searchemail, $searchsubject, $searchmessage]);
    
    
        if ($search_mailbox->rowCount() > 0) {
            foreach($search_mailbox->fetchAll() as $k => $row) {
                $email_number = $row['id'];
                $search_from = $row['from_email'];
                $search_subject = $row['subject'];
                $total_rows++;
                echo $search_subject . '.........................' . $total_rows;
                echo "<br>";
            }
        }
                        
    }
    
    ?>

I am unable to fetch the first 50 rows in the page 1 and I am also unable to fetch the 21 rows in the page 2. 

What I am trying to achieve is when I am in page 1, I want to display the first 50 rows, then in the page 2 if I have another 50 rows then display the 50 rows, but if I have less than 50 rows like 21 rows then display the 21 rows.

Can you please show me an example how I can start with first 50 rows when I am in the page 1 then in page 2 display 21 rows to make in total 71 rows?

Any advice would be much appreicated.

Thanks in advance.