php - 使用 MySQL 数据库子集的 AJAX 分页

Question

我有一个页面，使用 mysql 和 meekrodb 从数据库中提取信息。结果限制为每页 15 个，并且限制为数据库的字母数字子集 - 在此示例中，为 1-3 和 A-B

我现在想在单击#goleft 或#gort 时添加分页，然后它将从数据库中提取正确的项目。

如何对 pagination.php 进行 ajax 调用并将变量 $start 传递给它？不太清楚，如有不对的地方还请指正。不确定 pagination.php 中的 $start,15 是否正确

我已经走到这一步了:

主页:

<?php
require_once 'meekrodb.2.2.class.php';
require_once 'dconnect.php';  // database login info
// currently pulling $results from database on initial pg load
$results = DB::query("SELECT substr(theme, 1, 1) as Alphabet, theme, developer, thumb, thumb_lg FROM gallery ORDER BY (CASE Alphabet
  WHEN '1' THEN 1
  WHEN '2' THEN 2
  WHEN '3' THEN 3  
  WHEN 'A' THEN 4
  WHEN 'B' THEN 5
  ELSE 6
  END), theme
  LIMIT 15");
// get count of all relevant items
$tcount = DB::query("SELECT substr(theme, 1, 1) as Alphabet, theme, developer, thumb, thumb_lg FROM gallery ORDER BY (CASE Alphabet
  WHEN '1' THEN 1
  WHEN '2' THEN 2
  WHEN '3' THEN 3  
  WHEN 'A' THEN 4
  WHEN 'B' THEN 5
  ELSE 6
  END), theme");
// get count of all relevant items
$counter = DB::count();
$ipp = 15;  // items per page
$tpages = ceil($counter / $ipp);  // total pages

// write each entry to specific div;
$x = 0;
foreach ($results as $row) {
  $x++;
  if ($x == 1) {
    $t1 = $row['theme'];
    $d1 = $row['developer'];
    $th1 = $row['thumb'];
    $thlg1 = $row['thumb_lg'];
  }
  ... Additional if's through 15th item
}
?>

Basic Html:

<img src="<?php echo($th1); ?>" data-retina="<?php echo($thlg1); ?>" alt="<?php echo($t1); ?>" />
<span><p class="hname"><?php echo($t1); ?></p>
<div class="bull">&bull;</div>
<p class="hdev"><?php echo($d1); ?></p></span>
...
<div id="thumbnav"><div id="goleft"></div><div id="gort"></div></div>

主 pg jQuery:

// Previous button
var curpg = 1;
$('#goleft').mouseup (function() {
  var newpg = curpg - 1;
  if (newpg == 0) {newpg = 1} // reset page if going back to first page
  curpg = newpg;
  var $start = (newpg - 1) * 15 + 1;
  // how to pass $start to pagination.php ??
});

// Next button
$('#gort').mouseup (function() {
  var newpg = curpg + 1;
  var $totpgs = <?php echo $tpages; ?>;  // DOESN'T Echo anything !!!
  console.log('Total Pages: ' + $totpgs);
  if (newpg > $totpgs) {newpg = $totpgs}  // limit page to total pages
  curpg = newpg;
  var $start = (newpg - 1) * 15 + 1;
  console.log('Start: ' + $start);
  // how to pass $start to pagination.php ??
});

分页.php:

<?php
require_once 'meekrodb.2.2.class.php';
require_once 'dconnect.php';  // database login info
// pull from database using specific page items
$results = DB::query("SELECT substr(theme, 1, 1) as Alphabet, theme, developer, thumb, thumb_lg FROM gallery ORDER BY (CASE Alphabet
  WHEN '1' THEN 1
  WHEN '2' THEN 2
  WHEN '3' THEN 3  
  WHEN 'A' THEN 4
  WHEN 'B' THEN 5
  ELSE 6
  END), theme
  LIMIT $start,15");
?>

更新1:分页.php

<?php
$start = $_POST['start'];  // capture input from AJAX
require_once 'meekrodb.2.2.class.php';
require_once 'dconnect.php';
// pull from database using specific page items
$navresults = DB::query("SELECT substr(theme, 1, 1) as Alphabet, theme, developer, thumb, thumb_lg FROM gallery ORDER BY (CASE Alphabet
  WHEN '1' THEN 1
  WHEN '2' THEN 2
  WHEN '3' THEN 3  
  WHEN 'A' THEN 4
  WHEN 'B' THEN 5
  ELSE 6
  END), theme
  LIMIT $start,15");
$x = 0;
foreach ($navresults as $row) {
  $x++;
  if ($x == 1) {
    $t1 = $row['theme'];
    $d1 = $row['developer'];
    $th1 = $row['thumb'];
    $thlg1 = $row['thumb_lg'];
  }
  ... up to 15th variable set
}

主要 jQuery:

//Next pagination
$('#gort').mouseup (function() {
  var newpg = curpg + 1;
  var $totpgs = <?php echo $tpages; ?>; // Doesn't show echo !!!
  console.log('Total Pages: ' + $totpgs);
  if (newpg > $totpgs) {newpg = $totpgs}  // limit page to total pages
  curpg = newpg;
  var $start = (newpg - 1) * 15 + 1;
  console.log('Start: ' + $start);
  $.ajax({
    url:'pagination.php',
    type:'POST',
    data:{start:$start},
    dataType:'text'
  });
});

Answer 1

您需要对 pagination.php 进行 ajax 调用

$('#goleft').mouseup (function() {
    var newpg = curpg - 1;
    if (newpg == 0) {newpg = 1} // reset page if going back to first page
    curpg = newpg;
    var $start = (newpg - 1) * 15 + 1;
    $.ajax({
        url:'pagination.php',
        type:'POST',
        data:{start:$start},
        dataType:'json'
    }).done(function(response) {
        tableparser(response);
    });
});

所以，你看，现在你需要一个函数(我将其命名为 tableparser)来解析 JSON 并填充 HTML 表格。

同时，您需要 pagination.php 脚本使用有效的 JSON 进行应答

<?php
$start = $_POST['start'] // I'm capturing the input sent from Ajax
require_once 'meekrodb.2.2.class.php';
require_once 'dconnect.php';  // database login info
// pull from database using specific page items
$results = DB::query("SELECT substr(theme, 1, 1) as Alphabet, theme, developer, thumb, thumb_lg FROM gallery ORDER BY (CASE Alphabet
  WHEN '1' THEN 1
  WHEN '2' THEN 2
  WHEN '3' THEN 3  
  WHEN 'A' THEN 4
  WHEN 'B' THEN 5
  ELSE 6
  END), theme
  LIMIT $start,15");

echo json_encode($results);

我不知道您的数据库查询的具体实现，但是一旦您将结果放入数组中，您需要将它们输出为 json_encoded 以便 jQuery 将它们作为对象接收。