时间戳

首页 > 解决方案 > 使用 Mysqli 和准备好的语句进行分页

php - 使用 Mysqli 和准备好的语句进行分页

问题描述

我正在学习 PHP。我尝试构建一个脚本，通过分页显示来自 MySQL 的记录。

我将代码设置为每页显示 1 条记录。收件人“admin123”的 MySQL 表中有 4 条记录或匹配项。我已登录此帐户。

所以，这个分页脚本应该拉 4 条记录给我看。每页 1 条记录。共4页。它给出了找到的 echo 2 结果（它应该显示 4）。然后只按预期在页面上显示 1 条记录。但是，我没有看到分页部分。没有像这样显示到其他页面的链接：

第 1234 页。

我现在很困惑。请看一下，让我知道我哪里出错了。

<?php 

declare(strict_types=1);
ini_set('display_errors', '1');
ini_set('display_startup_errors', '1');
error_reporting(E_ALL);
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

include 'configurations.php'; 

if (!$conn) 
{ 
    $error = mysqli_connect_error(); 
    $errno = mysqli_connect_errno(); 
    print "$errno: $error\n"; 
    exit(); 
} 
else 
{   
    //Get Page Number. Default is 1 (First Page). 
 $page_number = $_GET["page_number"]; 
if ($page_number == "") 
    { 
        $page_number = 1; 
    } 

$sender_username = $mod; //$mod defined in configurations.php
$recipient_username = $user; 
$links_per_page = 1; 
$max_result = 30; 
$offset = ($page_number*$links_per_page)-$links_per_page;       

$query_1 = "SELECT COUNT(*) FROM messages WHERE recipient_username = ? 
    AND sender_username = ? ORDER BY id LIMIT ? OFFSET ?"; 
$stmt_1 = mysqli_prepare($conn,$query_1); 
mysqli_stmt_bind_param($stmt_1,'ssii',$recipient_username,$sender_username,$    links_per_page,$offset); 
mysqli_stmt_execute($stmt_1); 
$result_1 = mysqli_stmt_bind_result($stmt_1,$matching_rows_count); 
mysqli_stmt_fetch($stmt_1); 
mysqli_stmt_free_result($stmt_1); 

$total_pages = ceil($matching_rows_count/$links_per_page); 
$query_2 = "SELECT id,date_and_time,recipient_username,sender_username,warning_messages FROM 
    messages WHERE recipient_username = ? AND sender_username = ? ORDER BY id 
    LIMIT ? OFFSET ?"; 
$stmt_2 = mysqli_prepare($conn,$query_2); 
mysqli_stmt_bind_param($stmt_2,'ssii',$recipient_username,$sender_username,$links_per_page,$offset); 
mysqli_stmt_execute($stmt_2); 
$result_2 = mysqli_stmt_bind_result($stmt_2,$id,$date_and_time,$recipient_username,$sender_username,$warning); 
mysqli_stmt_fetch($stmt_2);     
?>
    <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional/EN"> 
<html> 
<head> 
<meta content="text/html; charset=ISO-8859-1" http-equiv=" content-type"> 
<title><?php echo "$site_name User $user Warnings in $server_time time."; ?> 
    </title> 
</head> 
<body> 
<br> 
<p align="center"><span style="font-weight:bold;"><?php echo "$site_name 
    User $user Warnings in $server_time time."; ?></span></align> 
<br> 
<br> 
    <table width="1500" border="0" cellpadding="5" cellspacing="2" 
    bgcolor="#666666"> 
<?php if(!$stmt_2) 
{ 
    ?> 
<tr> 
    <td bgcolor="#FFFFFF">No record found! Try another time.</td> 
</tr> 
    <?php 
} 
    else 
    { 
    if(($offset+1)<=$max_result) 
    { 
        printf("<b> %d Result Found ...</b>\n",$matching_rows_count); ?><br> 
        <br> 
        <tr name="headings"> 
        <td bgcolor="#FFFFFF" name="heading_submission- 
                number">Submission Number</td> 
        <td bgcolor="#FFFFFF" name="heading_date-and- 
                time">Date & Time in <?php echo "$server_time" ?></td> 
        <td bgcolor="#FFFFFF" name="heading_recipient- 
                username">To</td> 
        <td bgcolor="#FFFFFF" name="heading_sender- 
                username">From</td> 
        <td bgcolor="#FFFFFF" name="heading_warning">Warning</td> 
        </tr> 
        <tr name="user-details">            
                <td bgcolor="#FFFFFF" name="submission-number"><?php 
                printf("%s",$id); ?></td> 
                <td bgcolor="#FFFFFF" name="date-and-time"><?php 
                printf("%s",$date_and_time); ?></td> 
                <td bgcolor="FFFFFF"  name="recipient-username"><?php 
                printf("%s",$recipient_username); ?></td> 
        <td bgcolor="#FFFFFF" name="sender-username"><?php 
                printf("%s",$sender_username); ?></td> 
        <td bgcolor="#FFFFFF" name="warning"><?php printf("%s",$warning); ?> 
                </td> 
        </tr> 
                <?php 
        while(mysqli_stmt_fetch($stmt_2)) 
        { 
        ?> 
        <tr name="user-details"> 
        <td bgcolor="#FFFFFF" name="submission-number"><?php 
                    printf("%s",$id); ?></td> 
        <td bgcolor="#FFFFFF" name="date-and-time"><?php 
                    printf("%s",$date_and_time); ?></td> 
        <td bgcolor="#FFFFFF" name="recipient-username"><?php 
                    printf("%s",$recipient_username); ?></td> 
        <td bgcolor="#FFFFFF" name="sender-username"><?php 
                    printf("%s",$sender_username); ?></td> 
        <td bgcolor="#FFFFFF" name="warning"><?php 
                    printf("%s",$warning); 
                    ?></td> 
        </tr> 
        <?php 
        ?> 
        <tr name="pagination"> 
        <td colspan="10" bgcolor="#FFFFFF"> Result Pages: 
        <?php              
        if($page_number < $total_pages) 
        { 
            for($i=1;$i<=$total_pages;$i++) //Show Page Numbers in 
                        Serial.
            echo "<a href=\"{$_SERVER['PHP_SELF']}? 
                        user=$user&page_number={$i}\">{$i}</a> "; 
        }           
        else 
        { 
            for($i=$total_pages;$i>=1;$i--) //Show Page Numbers in 
                        Reverse.
            echo "<a href=\"{$_SERVER['PHP_SELF']}? 
                        user=$user&page_number={$i}\">{$i}</a> "; 
        } 
            ?> 
            </td> 
                </tr> 
            <?php 
        } 
        } 
        }
    ?>   
        </table>    
        <br> 
        <br> 
    <p align="center"><span style="font-weight:bold;"><?php echo "$site_name 
        User $user Warnings in $server_time time."; ?></span></align> 
    <br> 
    </div> 
    <br> 
    </body> 
    </html> 
    <?php 
    mysqli_stmt_free_result($stmt_2); 
    mysqli_stmt_close($stmt_2); 
    mysqli_close($conn); 
     } 
     ?>

标签： phppaginationprepared-statement

解决方案

推荐阅读