PHP - How To Limit Number Of Result Per Page

if (!$pw_ok)	{
				if (isset($_SERVER["REMOTE_ADDR"])) {
						$str_RemoteHost = $_SERVER["REMOTE_ADDR"];
					} else {
						$str_RemoteHost = '';
					}
	
					$qry_WriteToDatabase = "	INSERT INTO	cms_user_login_attempts
											(
												cula_user_id,
												cula_date_time,
												cula_remote_host,
												cula_attempt_count
											)
								VALUES			(
												" . $db->SQLString($row->user_id) . ",
												Now(),
												" . $db->SQLString($str_RemoteHost, true) . ",
												'cula_attempt_count'
											)";
					$db->query($qry_WriteToDatabase);
					
					$qry_UpdateCount = "	UPDATE 
												cms_user_login_attempts
											SET 
												cula_attempt_count = cula_attempt_count + 1
											WHERE 
												cula_user_id = " . $db->SQLString($row->user_id) . " ";
					$db->query($qry_UpdateCount);							
					
					
					
					$qry_CheckDatabase = "	SELECT 
												CASE WHEN count(*) >= 5 THEN 0 ELSE 1 END as allowed_login 
											FROM
												cms_user_login_attempts
											WHERE
												cula_date_time >= DATE_SUB(CURRENT_TIMESTAMP, interval 48 hour) 
											AND 
												cula_user_id = " . $db->SQLString($row->user_id) . "";
					$rs_CheckDatabase = $db->query($qry_CheckDatabase);
					
					if (! (isset($qry_CheckDatabase) && $qry_CheckDatabase)) {
					$errors->defineError("invalid_user_pass", "Too many attempts, account locked for 48hours.", array("username","password"));
					}
					
				
					
			}

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.

|  id          | int(4)       | NO   | PRI | NULL    | auto_increment |
| user_id     | int(4)       | NO   |     | NULL    |                |
| recipient   | varchar(30)  | NO   |     | NULL    |                |
| subject     | varchar(25)  | YES  |     | NULL    |                |
| cc_email    | varchar(30)  | YES  |     | NULL    |                |
| reply       | varchar(20)  | YES  |     | NULL    |                |
| location    | varchar(50)  | YES  |     | NULL    |                |
| stationery  | varchar(40)  | YES  |     | NULL    |                |
| ink_color   | varchar(12)  | YES  |     | NULL    |                |
| fontchosen  | varchar(30)  | YES  |     | NULL    |                |
| message     | varchar(500) | NO   |     | NULL    |                |
| attachment  | varchar(40)  | YES  |     | NULL    |                |
| messageDate | datetime     | YES  |     | NULL    |        

            $params = array(                 
               ':user_id' => $userid,                 
               ':recipient' => $this->message_vars['recipient'],                 
               ':subject' => $this->message_vars['subject'],                 
               ':cc_email' => $this->message_vars['cc_email'],                 
               ':reply' => $this->message_vars['reply'],                 
               ':location' => $this->message_vars['location'],                 
               ':stationery' => $this->message_vars['stationery'],                 
               ':ink_color' => $this->message_vars['ink_color'],                 
               ':fontchosen' => $this->message_vars['fontchosen'],                 
               ':message' => $messageInput,                 
               ':attachment' => $this->message_vars['attachment'],                 
               ':messageDate' => $date             );

$sql = "INSERT INTO messages (user_id,recipient, subject, cc_email, reply, location,stationery, ink_color, fontchosen, message,attachment) VALUES( $userid, :recipient, :subject, :cc_email, :reply, :location, :stationery, :ink_color, :fontchosen, $messageInput, :attachment, $date);";

And lastly, here is how I am calling it:
            $dbh = parent::$dbh;             
           $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);              
           if (empty($dbh))    return false;             
                  $stmt = $dbh->prepare($sql);             
                 $stmt->execute($params) or die(print_r($stmt->errorInfo(), true));              
           if (!$stmt) {    print_r($dbh->errorInfo());              }

<?php

$con=mysqli_connect("address","username","pass","db name");
// Check connection
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

if (!(isset($pagenum))) 
{ 
 $pagenum = 1; 
 }

$data = mysqli_query($con,"SELECT * FROM info");

$rows = mysql_num_rows($data);

$page_rows = 15;

$last = ceil($rows/$page_rows);

if($pagenum < 1);
{ 

 $pagenum = 1;
 
 } 
 elseif ($pagenum > $last); 
 {
$pagenum = $last; 
 } 
$max = 'limit ' .($pagenum - 1) * $page_rows .',' .$page_rows; 
 
$result = mysql_query($con,"SELECT * FROM topsites $max") or die(mysql_error()); 
 
 
echo "<table border='1' class='boldtable' cellpadding='2'>
<tr>
<th>Creditor</th>
<th>dd/mm/yyyy</th>
<th>Credit Score</th>
<th>State</th>

<th>Income</th>

<th>Approval</th>
<th>Credit Line</th>
<th>Interest</th>
<th>Comment</th>
</tr>";

while($row = mysqli_fetch_array($result)) {
  echo "<tr>";
  echo "<td>" . $row['creditor'] . "</td>";
  echo "<td>" . $row['date'] . "</td>";	 
  echo "<td>" . $row['fako'] . "</td>";
  echo "<td>" . $row['state'] . "</td>";

  echo "<td>" . $row['income'] . "</td>";

  echo "<td>" . $row['approval'] . "</td>";
  echo "<td>" . $row['creditline'] . "</td>";
  echo "<td>" . $row['interest'] . "</td>";
  echo "<td>" . $row['comments'] . "</td>";
  echo "</tr>";
}

echo "</table>";

echo " --Page $pagenum of $last-- <p>";
if ($pagenum == 1)

{

} 
 
 else
 
{

echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=1'> <<-First</a> ";

echo " ";

$previous = $pagenum-1;

echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$previous'> <-Previous</a> ";

}

echo " ---- ";

if ($pagenum == $last)

{

}

else {

$next = $pagenum+1;

echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$next'>Next -></a> ";

echo " ";

echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$last'>Last ->></a> ";

}

mysqli_close($con);

?>

 The result pages is supposed to have  pagination like google  help me please