php - 向复杂查询添加分页

Question

我花了几天时间尝试让这项工作成功。我有一个图书数据库。我有一个名为 getBooks 的函数文件。

    function getBooks($limit = 10000, $sortBy = 'tblAuthor.LastName' ,  $direction='ASC', $where=1){
$db = connect();
$fields = array('tblBook.Title', 'tblAuthor.FirstName', 'tblAuthor.LastName', 'tblCategory.Category');
$format = 'Select '. implode(', ', $fields) .' FROM tblAuthor INNER JOIN (tblBook INNER JOIN tblCategory ON tblBook.CatID=tblCategory.CatID) ON tblBook.AuthorID=tblAuthor.AuthorID where %1$s ORDER BY %2$s %3$s LIMIT %4$s ';
$query = sprintf($format, $where, $sortBy, $direction, $limit);
$escapedQuery = stripslashes($db->realEscape($query));  
$db->runQuery($escapedQuery);
if($db->hasErrors()){
    print_r($db->getErrors());
    echo('<br /><br />Exiting the script.');
    die();
}
$results = $db->fillResultset();
close($db);
return $results;    

}//结束获取书籍？>

然后将其调用到处理其余部分的库。

    <?php
    // include header
    include ("header.php");
    include_once('bookFunctions.php');
    $books=getBooks();
    $myName = "My Books";
    ?>   
   <div id="content">
   <?php if (count($books)){
   ?>
   <table class="books"> 
  <tr>
     <th>Book Title</th>
     <th>Author's Last Name</th>
     <th>Author's First Name</th>
     <th>Genre</th>
  </tr>
   <?php
    foreach($books as $book) 
        {
            echo "<tr>";//start the row

            //echo out each cell    
            echo "<td>" . $book['Title'] . "</td>";
            echo "<td>" . $book['LastName'] . "</td>";
            echo "<td>" . $book['FirstName'] . "</td>";             
            echo "<td>" . $book['Category'] . "</td>";

            echo "</tr>";//end the row
        }
    ?>  

我已经尝试了各种分页脚本和图，但我就是不知道将其插入到我的查询中的位置。我正在努力解决的最新问题是:http://stefangabos.ro/php-libraries/zebra-pagination/我知道一定有办法做到这一点。如有任何建议，我们将不胜感激。

Answer 1

如果第 1 页且每页有 10 个项目，您的限制类别将为 LIMIT 0, 10。即偏移量 0 和长度 10。

+------+--------+
| Page | Offset |
+------+--------+
|  1   |    0   |
|  2   |   10   |
|  3   |   20   |
+------+--------+

这里的模式是 offset = (page - 1) * items_per_page。

<?php

$num_per_page = 10;

$page = intval($REQUEST['page']);
$offset = ($page - 1) * $num_per_page;

$sql_limit = "LIMIT $offset, $num_per_page";

一种方法是修改函数原型(prototype)以包含 page 和 num_per page 的参数 -

function getBooks($sortBy = 'tblAuthor.LastName',  $direction='ASC', $where = 1, $page = 1, $num_per_page = 10) {
    $offset = ($page - 1) * $num_per_page;
    $db = connect(); 
    $fields = array('tblBook.Title', 'tblAuthor.FirstName', 'tblAuthor.LastName', 'tblCategory.Category'); 
    $format = 'Select '. implode(', ', $fields) .' FROM tblAuthor INNER JOIN (tblBook INNER JOIN tblCategory ON tblBook.CatID=tblCategory.CatID) ON tblBook.AuthorID=tblAuthor.AuthorID where %1$s ORDER BY %2$s %3$s LIMIT %4$d, %5$d '; 
    $query = sprintf($format, $where, $sortBy, $direction, $offset, $num_per_page);

然后修改对 getBooks 的调用 -

$books=getBooks('tblAuthor.LastName', 'ASC', null, intval($_REQUEST['page']), 10);