如何查询前10行，下次查询表中的其他10行

I have more than 500 rows with in my Database Table with particular date.

我的数据库表中有超过500行，具有特定日期。

To query the rows with particular date.

查询具有特定日期的行。

select * from msgtable where cdate='18/07/2012'

This returns 500 rows.

这将返回500行。

How to query these 500 rows by 10 rows step by step. Query First 10 Rows and show in browser,then query next 10 rows and show in browser?

如何逐步查询这500行×10行。查询前10行并在浏览器中显示，然后查询下10行并在浏览器中显示？

7 个解决方案

#1

Just use the LIMIT clause.

只需使用LIMIT子句。

SELECT * FROM `msgtable` WHERE `cdate`='18/07/2012' LIMIT 10

And from the next call you can do this way:

从下一个电话中你可以这样做：

SELECT * FROM `msgtable` WHERE `cdate`='18/07/2012' LIMIT 10 OFFSET 10

More information on OFFSET and LIMIT on LIMIT and OFFSET.

有关LIMIT和OFFSET的OFFSET和LIMIT的更多信息。

#2

LIMIT limit OFFSET offset will work.

LIMIT限制OFFSET偏移将起作用。

But you need a stable ORDER BY clause, or the values may be ordered differently for the next call (after any write on the table for instance).

但是你需要一个稳定的ORDER BY子句，或者对于下一次调用可能会以不同的方式排序值（例如，在对表进行任何写操作之后）。

SELECT *
FROM   msgtable
WHERE  cdate = '2012-07-18'
ORDER BY msgtable_id  -- or whatever is stable 
LIMIT  10
OFFSET 50;  -- to skip to page 6

Use standard-conforming date style (ISO 8601 in my example), which works irregardless of your locale settings.

使用符合标准的日期样式（在我的示例中为ISO 8601），无论您的语言环境设置如何，它都可以使用。

Paging will still shift if involved rows are inserted or deleted or changed in relevant columns. It has to.

如果在相关列中插入或删除或更改涉及的行，则分页仍将移位。它必须。

To avoid that shift or for better performance with big tables use smarter paging strategies:

为避免这种转变或使用大表更好的性能，请使用更智能的分页策略：

Optimize query with OFFSET on large table
在大表上使用OFFSET优化查询

#3

SET @rownum = 0; 
SELECT sub.*, sub.rank as Rank
FROM
(
   SELECT *,  (@rownum := @rownum + 1) as rank
   FROM msgtable 
   WHERE cdate = '18/07/2012'
) sub
WHERE rank BETWEEN ((@PageNum - 1) * @PageSize + 1)
  AND (@PageNum * @PageSize)

Every time you pass the parameters @PageNum and the @PageSize to get the specific page you want. For exmple the first 10 rows would be @PageNum = 1 and @PageSize = 10

每次传递参数@PageNum和@PageSize以获取所需的特定页面。例如，前10行是@PageNum = 1和@PageSize = 10

#4

for first 10 rows...

前10行......

SELECT * FROM msgtable WHERE cdate='18/07/2012' LIMIT 0,10

for next 10 rows

接下来的10行

SELECT * FROM msgtable WHERE cdate='18/07/2012' LIMIT 10,10

#5

You can use postgresql Cursors

你可以使用postgresql游标

BEGIN;
DECLARE C CURSOR FOR where * FROM msgtable where cdate='18/07/2012';

Then use

然后用

FETCH 10 FROM C;

to fetch 10 rows.

获取10行。

Finnish with

芬兰人

COMMIT;

to close the cursor.

关闭光标。

But if you need to make a query in different processes, LIMIT and OFFSET as suggested by @Praveen Kumar is better

但是如果你需要在不同的过程中进行查询，那么@Praveen Kumar建议的LIMIT和OFFSET会更好

#6

Ok. So I think you just need to implement Pagination.

好。所以我认为你只需要实现分页。

$perPage = 10;

$pageNo = $_GET['page'];

Now find total rows in database.

现在找到数据库中的总行数。

$totalRows = Get By applying sql query;

$pages = ceil($totalRows/$perPage);    

$offset = ($pageNo - 1) * $perPage + 1

$sql = "SELECT * FROM msgtable WHERE cdate='18/07/2012' LIMIT ".$offset." ,".$perPage

#7

<html>
<head>
    <title>Pagination</title>
</head>
<body>
<?php 

    $conn = mysqli_connect('localhost','root','','northwind');
    $data_per_page = 10;
    $select = "SELECT * FROM `customers`";  
    $select_run = mysqli_query($conn, $select);
    $records = mysqli_num_rows($select_run);
    // while ($result = mysqli_fetch_array($select_run)) {      
    //     echo $result['CompanyName'] . '<br>';        
    // }
    // $records;
    echo "<br>";
    $no_of_page = ceil($records / $data_per_page);
    if(!isset($_GET['page'])){
        $page = 1;
    }else{
        $page = $_GET['page'];
    }
    $page_limit_data = ($page - 1) * 10;
    $select = "SELECT * FROM customers LIMIT " . $page_limit_data . ',' . $data_per_page ;
    $select_run = mysqli_query($conn, $select);
    while ($row_select = mysqli_fetch_array($select_run)){
        echo $row_select['CompanyName'] . '<br>' ;
    }
    for($page=1; $page<= $no_of_page; $page++){ 
        echo "<a href='pagination.php?page=$page'> $page" . ', ';   
    }
?>
<br>
<h1> Testing Limit Functions Here  </h1>
<?php 
$limit = "SELECT CompanyName From customers LIMIT 10 OFFSET 5";
$limit_run = mysqli_query($conn , $limit);
while($limit_result = mysqli_fetch_array($limit_run)){
    echo $limit_result['CompanyName'] . '<br>';
}
?>
</body>
</html>

#1