javascript - PHP Mysql 表分页

Question

我一直在尝试在表格上制作一个带有分页的页面。单击按钮后，main.php 中的表由从 page.php 中的查询检索到的前 16 行填充。例如，检索到的行数为 20。因此第二页将多出 4 行。事情是当我单击“下一步”按钮以显示缺少的 4 条记录时，该页面然后转移到包含缺少记录的 page.php。我想要发生的是将 main.php 中的 20 条记录替换为 4 条记录。

MAIN.PHP

<div id="section">
    <head3>Asset Assignment</head3><br><br>
    <table>
        <td>
            <tr>Search Asset:</tr>
            <tr><input type="text" id="sidt" name="sid"></tr>
            <tr><input type="button" name="searchSub" value="Search" onClick="searchItem()"></tr>
        </td>
    </table><br>
    <table>
        <tr>
            <td>Employee ID</td>
            <td>Asset Serial Number</td>
        </tr>
        <tr>
            <td><input type="text" name="empID"></td> 
            <td><input type="text" name="srlID"></td>
        </tr>
        <tr>
            <td align="right"><input type="button" name="assign" value="Assign" onClick="assignItem()"></td>
        </tr>
    </table><br>
</div>
<div id="section2"></div>

JavaScript

function searchItem()
{   
    var xhr;  
    if (window.XMLHttpRequest) {
        xhr = new XMLHttpRequest();  
    } 
    else if (window.ActiveXObject) {
        xhr = new ActiveXObject("Microsoft.XMLHTTP");
    }  
    var data = "asid=" + document.getElementById("sidt").value;
    xhr.open("POST", "page.php", true);   
    xhr.setRequestHeader("Content-Type", "application/x-www-form-urlencoded");              
    xhr.send(data);  
    xhr.onreadystatechange = display_data; 
    function display_data() 
    {  
        if (xhr.readyState == 4) 
        { 
            if (xhr.status == 200) 
            {       
                document.getElementById("section2").innerHTML = xhr.responseText;
            } 
            else 
            { 
                alert('There was a problem with the request.');  
            }  
        }  
    }  
}
function assignItem(){}

页面.PHP

<?PHP
session_start();
$dbHost     = "localhost";
$dbUser     = "root"; 
$dbPass     = "password";
$dbDatabase = "awsims";
$db         = new PDO("mysql:dbname=$dbDatabase;host=$dbHost;port=3306", $dbUser, $dbPass); 
if (!isset($_GET['startrow']) or !is_numeric($_GET['startrow'])) {
  $startrow = 0;
} else {
  $startrow = (int)$_GET['startrow'];
}
$sql = $db->prepare("SELECT * FROM assets LIMIT $startrow, 16");
$sql->execute();
$fetch = $sql->rowCount();
$num=$fetch;
if($num>0)
{
    echo "<table style='width: 100%' border='1'>";
    echo "<tr><td>ID</td><td>Drug</td><td>quantity</td></tr>";
    for($i=0;$i<$num;$i++)
    {
        $row = $sql->fetch(PDO::FETCH_ASSOC);
        $a = $row['SerialNumber'];
        $b = $row['AssetType'];
        $c = $row['AssetSubType'];
        echo "<tr>";
        echo "<td>$a</td>";
        echo "<td>$b</td>";
        echo "<td>$c</td>";
        echo "</tr>";
    }
    echo "</table>";
}
echo '<a href='.$_SERVER['PHP_SELF'].'?startrow='.($startrow+16).'>Next</a>';
$prev = $startrow - 16;
        //if ($prev >= 0)
echo '<a href='.$_SERVER['PHP_SELF'].'?startrow='.$prev.'>Previous</a>';
?>

Answer 1

尝试将您的 sql 语句更改为以下查询。

$sql = $db->prepare("SELECT * FROM assets LIMIT 16 OFFSET $startrow");

下一个和上一个链接代码应该如下所示。

echo '<a href='.$_SERVER['PHP_SELF'].'?startrow='.($startrow+1).'>Next</a>';
$prev = $startrow - 1;
echo '<a href='.$_SERVER['PHP_SELF'].'?startrow='.$prev.'>Previous</a>';