php 如何从给定的行号开始选择 MySQL 中的行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1252673/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I select rows in MySQL starting at a given row number?
提问by mrpatg
Say I have 50 rows in a MySQL table. I want to select the first ten (LIMIT 10), but then I want to be able to select the next 10 on a different page.
假设我在 MySQL 表中有 50 行。我想选择前十个 ( LIMIT 10)，但随后我希望能够在不同的页面上选择接下来的 10 个。
So how do I start my selection, after row 10?
那么如何在第 10 行之后开始我的选择呢？
Updated query:
更新的查询：
mysql_query("
SELECT * FROM `picdb`
WHERE `username` = '$username'
ORDER BY `picid` DESC
LIMIT '$start','$count'
")
回答by chaos
I recommend working by obtaining the first page using:
我建议通过使用以下方法获取第一页来工作：
LIMIT 0, 10
then for the second page
然后是第二页
LIMIT 10, 10
then
然后
LIMIT 20, 10
for the third page, and so on.
对于第三页，依此类推。
回答by Karl Voigtland
LIMIT 10
LIMIT 10 OFFSET 10
From the MySQL 5.1 docs on SELECTsyntax:
For compatibility with PostgreSQL, MySQL also supports the LIMIT row_count OFFSET offset syntax.
为了与 PostgreSQL 兼容，MySQL 还支持 LIMIT row_count OFFSET 偏移语法。
回答by TenTen Peter
This question is old but i just want to add a code that is not hardcoded, the answer chaos gave means you'll have to hardcode your scripts(Select statement). you can achieve the same results by getting the file name and then select data from the database based on the current page, without hardcoding your select statement. first get the current page
这个问题很老，但我只想添加一个不是硬编码的代码，答案混乱意味着您必须对脚本进行硬编码（Select 语句）。您可以通过获取文件名然后根据当前页面从数据库中选择数据来获得相同的结果，而无需对 select 语句进行硬编码。首先获取当前页面
$page = basename($_SERVER['SCRIPT_FILENAME']);
$page_counter = rtrim($page, ".php");
//setting your limit
$start = 0;
$limit = 10;
//if current page is not index.php then $start = ($limit * page_counter);
// e.g if current page is 1.php then $start = ($limit * 1) = 10
//if current page is 2.php then $start = ($limit * 2) = 20
if ($page !== 'index.php') {
$start = ($limit * $page_counter);
}
//getting row count
$ROW_COUNT = $db->query('SELECT * from tableName')->rowCount();
//getting number of rows left in the table
$rows_left = ("SELECT * FROM tableName limit ?,?");
$rows_left = $db->prepare($rows_left);
$rows_left->execute(array($start,$ROW_COUNT));
$rows = $rows_left->fetchAll(PDO::FETCH_ASSOC);
$number_rows = 0;
foreach ($rows as $r) {
$number_rows = $number_rows + 1;
}
//if number of rows left in the table is less than 10 then $limit = the number of rows left
if ($number_rows < 10) {
$limit = $number_rows;
}
//getting all rows
$getRows = "SELECT * FROM tableName limit ?,?";
$getRows = $db->prepare($getRows);
$getRows->execute(array($start , $limit));
$getRows = $getRows->fetchAll(PDO::FETCH_ASSOC);
回答by Sachin Prasad
select * from 'table_name'
ORDER BY 'column_id 'DESC
LIMIT 0,10;
select * from 'table_name'
ORDER BY 'column_id' DESC
LIMIT 10,10;
select * from 'table_name'
ORDER BY 'column_id' DESC
LIMIT 20,10;
and continue till the numbers you want.
并继续直到你想要的数字。
