php中的分页

2022-01-04 00:00:00 sql php pagination

我正在使用它来收集特定用户的评论.我想在每页上显示 7 条评论并希望启用分页.实施步骤是什么.对不起.n00b 问题.

i am using this to collect comments made abt a particular user. i want to display 7 comments on each page and want to enable pagination. what would be the steps of implementation. sorry. n00b question.

$query = "SELECT msg, user_id, comment_time FROM comments WHERE aid = '$aid' ORDER BY comment_time DESC";
        $result = mysql_query($query) or die("ERROR: $query.".mysql_error());
        if (mysql_num_rows($result) > 0) {
            while($row = mysql_fetch_object($result)){
                $uidval = $row->user_id;
                if ($uidval != 0){
                $userInfo = $facebook->api_client->fql_query("SELECT name, pic_square_with_logo, profile_url FROM user WHERE uid='".$uidval."'");

            //  echo '<br>Array Info<br>';
            //  print_r($userInfo);
            //  echo '<br><br>';

                $nameuser = $userInfo[0]['name'];
                $pic = $userInfo[0]['pic_square_with_logo'];
                $profile_url = $userInfo[0]['profile_url'];

                echo '<img src="'.$pic.'" />';
                echo '<a href="'.$profile_url.'">'.$nameuser.'</a>';
                echo '<br>';
                echo $row->comment_time;
                echo '<br>';
                echo $row->msg;
                echo '<br>';
                }

            }
        }

推荐答案

该解决方案最好通过 SQL 的限制/偏移子句实现.对于 MySQL，这是通过将 LIMIT [offset] [count] 附加到您的查询来实现的.PostgreSQL 使用单独的 select ... LIMIT [count] OFFSET [offset] 语法.

The solution is best achieved via SQL's limit/offset clauses. For MySQL, this is achieved via appending LIMIT [offset] [count] to your query. PostgreSQL uses separate select ... LIMIT [count] OFFSET [offset] syntax.

这个想法是您将返回的结果数量限制为您想要显示的实际数量.如果您显示 200 页中的第 1 页，每页有 100 个结果，它可以显着提高性能.

The idea is you limit the number of results returned to the actual number you want to display. It can yield a huge performance increase if you're displaying page 1 of 200, with a hundred results per page.

当然，您需要运行第二个查询 - 一个 select count(*) from ... 以确定结果集中的结果数.将其除以每页的结果数，您就得到了页数.

Of course, you need to run a second query - a select count(*) from ... to determine the number of results in the result set. Divide this by the number of results per page, and you've got the number of pages.

您可能希望在查询字符串中传递一个页面参数，例如

You will likely want to pass a page parameter in the query string, something like

http://mysite.com/index.php?page=7

然后使用类似的方法提取数据(考虑这个伪代码；我知道我实际上没有正确查询)

and then pull the data using a similar method (consider this pseudo code; I know I'm not actually querying properly)

<?php

$num_per_page = 20; // 20 results per page

$page = isset($_GET['page']) ? $_GET['page'] : 0; // start from page 0

// Build our big query minus the SELECT part here
$from_part = 'tbl_results [where ...]"'

$num_results = query("SELECT COUNT(*) FROM $from_part");

// Use ceil to round upwards
$num_pages = ceil($num_results / $num_per_page);

// Cap the page a the last page
if ($page > $num_pages)
  $page = $num_pages;

// Cap the page at the first page
if ($page <= 1)
  $page = 1;

$offset = $page * $num_per_page;

// Build the final query to select the relevant page of data
$results = query("SELECT * FROM $from_part LIMIT $offset, $num_per_page");

?>

相关文章