分页不断显示 SQL 数据的相同部分

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

我有一个来自 SQL 的非常大的数据集，我需要对其进行分页.

I have a really large data set from SQL that i need to paginate.

我的分页代码有问题.代码确实显示了 URL 中的页码，并且它确实在表格底部为我提供了分页超链接.但是，我单击的任何页面都会输出 sql 数据表的相同部分.

I have an issue with my pagination code. The code does show the page number in the URL and it does give me pagination hyperlinks at the bottom of the table. However, any page I click on, it outputs the same exact portion of the sql datatable.

另外，我在 wordpress 中这样做.

Also, I'm doing this in wordpress.

// define how many results you want per page
$results_per_page = 10;

// find out the number of results stored in database
$sql='SELECT * FROM ETF';
$result = mysqli_query($con, $sql);
$number_of_results = mysqli_num_rows($result);

// determine number of total pages available
$number_of_pages = ceil($number_of_results/$results_per_page);

// determine which page number visitor is currently on
if (!isset($_GET['page'])) {
  $page = 1;
} else {
  $page = $_GET['page'];
}

// determine the sql LIMIT starting number for the results on the displaying page
$this_page_first_result = ($page-1)*$results_per_page;

// retrieve selected results from database and display them on page
$sql='SELECT * FROM ETF LIMIT ' . $this_page_first_result . "," .$results_per_page;

$result = mysqli_query($con, $sql);

while($row = mysqli_fetch_array($result)) {
  echo $row['ETF'] . ' ' . $row['ETF NAME']. '<br>';
}


// display the links to the pages
for ($page=1;$page<=$number_of_pages;$page++) {
  echo '<a href="index.php/stocks/sec-forms/?page=' . $page . '">' . $page. '</a>';
}

推荐答案

尝试:

$sql='SELECT * FROM ETF LIMIT ' . $results_per_page . ' OFFSET ' . $this_page_first_result;

同样如上所述，您应该使用ORDER BY"按特定列排序以获得一致的结果.

Also as mentioned you should sort by a certain column with 'ORDER BY' for consistent results.

相关文章