php - 基于 AJAX 的无限滚动 - 不适用于按 DESC 顺序排序的 Mysql 记录

Question

我正在使用可用的代码片段 here

我的 PHP 代码是:

<?PHP
    /***  This is for bottomless listing of record from a table ****/
    $sql = 'SELECT * FROM agency_list ORDER BY id DESC LIMIT 0, 5';
   $query = $dbo4->prepare($sql);   // dbo4 coming from config.php
   $query->execute();
   $list = $query->fetchAll();
 ?>

相关的css代码是:

.content {
    padding: 10px;
    min-height: 100px;
   text-align: center;
}

#loader {
    text-align: center;
    display: none;
}
#items { 
    list-style: none; 
   text-align: left; 
}
#items li { 
    margin: 0 0 10px 0;
    background: #FFFFFF;    /* original -- f1f0f0*/
    border: 1px solid #999999;
    border-radius: 5px;
    color: #333333;
 }
 #items li h2 {    
   font-size: 18px;
   padding: 5px;
}

#items li p {
   padding: 5px;
}

java脚本代码(end-less-scroll.js)如下:

var is_loading = false; // initialize is_loading by false to accept new loading
var limit = 5; // limit items per page
$(function() {
    $(window).scroll(function() {
        if($(window).scrollTop() + $(window).height() == $(document).height()) {
            if (is_loading == false) { // stop loading many times for the same page
                // set is_loading to true to refuse new loading
                is_loading = true;
                // display the waiting loader
                $('#loader').show();
                // execute an ajax query to load more statments
                $.ajax({
                    url: 'load_more.php',
                    type: 'POST',
                    data: {last_id:last_id, limit:limit},
                    success:function(data){
                        // now we have the response, so hide the loader
                        $('#loader').hide();
                        // append: add the new statments to the existing data
                        $('#items').append(data);
                        // set is_loading to false to accept new loading
                        is_loading = false;
                    }
                });
            }
       }
    });
});

上面的代码是调用 load_more.php，它有以下代码:

<?PHP
    include 'config.php';

    $last_id    =   $_POST['last_id'];
    $limit      =   5; 

    if (isset($_POST['limit'])) 
    {
        $limit  =   intval($_POST['limit']);
    }

    // selecting the items for page params
    try 
    {
        $sql    =   'SELECT * FROM agency_list WHERE id > :last_id ORDER BY id DESC LIMIT 0, :limit';
        $query  =   $dbo4->prepare($sql);

        $query->bindParam(':last_id', $last_id, PDO::PARAM_INT);
        $query->bindParam(':limit', $limit, PDO::PARAM_INT);
        $query->execute();

        $list   =   $query->fetchAll();
    } 
    catch (PDOException $e) 
    {
        echo 'PDOException : '.  $e->getMessage();
    }

    $last_id = 0;

    foreach ($list as $rs) 
    {
        $last_id = $rs['id'];
        ?>
            <li>
                <h2>&nbsp;&nbsp;&nbsp;<a href='#'><?PHP echo  '[' .$rs['id'] .'] ' . $rs['agency_nm']; ?></a>
                <img height = "32px" width = "32px" src="images/face_smile_1.png"></h2>

                <blockquote>
                    <p>
                        Lorem ipsum dolor sit amet, consectetur adipiscing elit. Et non ex maxima parte de tota iudicabis? 
                        Item de contrariis, a quibus ad genera formasque generum venerunt. Sit enim idem caecus, debilis. 
                        Duo Reges: constructio interrete.
                        <BR>
                        Lorem ipsum dolor sit amet, consectetur adipiscing elit. Et non ex maxima parte de tota iudicabis? 
                        Item de contrariis, a quibus ad genera formasque generum venerunt. Sit enim idem caecus, debilis. 
                        Duo Reges: constructio interrete.
                    </p>
                </blockquote>
                <h5>&nbsp;&nbsp;&nbsp;<?PHP echo $rs['agency_addr_landmark']; ?></h5>
                <h5>&nbsp;&nbsp;&nbsp;<?PHP echo $rs['agency_city']. ' - ' . $rs['agency_pin'] . ' [' . $rs['agency_state'] . '] ' ; ?></h5>                                            
                <h5>&nbsp;&nbsp;&nbsp;<?PHP echo $rs['agency_contact_per_1']; ?></h5>

                <h5>&nbsp;&nbsp;&nbsp;<font color = "maroon"><?PHP echo $rs['agency_contact_addr1'];?></font></h5>

                <p style="align:right;">
                    <a href="#" class="tags" style="background-color:darkgreen;">Details</a>
                    <a href="#" class="tags" style="background-color:#955251;">Update</a>
                    <a href="#" class="tags" style="background-color:#5B5EA6;">More</a>
                </p>
                <BR>
            </li>
            <BR>
        <?PHP
    }

    if ($last_id != 0) 
    {
        echo '<script type="text/javascript">var last_id = '.$last_id.';</script>';
    }

    // sleep for 1 second to see loader, it must be deleted in prodection
    sleep(1);
?>

最后获取的记录被列在这个 div 中(在 HTML 文件中)

<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.0/jquery.min.js"></script>
<script type="text/javascript" src="js/end-less-scroll.js"></script>

<div class="row">
    <div class="twelve columns">            
        <div class="content">
            <ul id="items">
                <?PHP
                    $last_id = 0;
                    foreach ($list as $rs) 
                    {
                        $last_id = $rs['id']; // keep the last id for the paging
                        ?>
                        <li>
                            <h2>&nbsp;&nbsp;&nbsp;<a href='#'><?PHP echo  '[' .$rs['id'] .'] ' . $rs['agency_nm']; ?></a><img height = "32px" width = "32px" src="images/face_smile_1.png"></h2>

                            <blockquote>
                                <p>
                                    Lorem ipsum dolor sit amet, consectetur adipiscing elit. Et non ex maxima parte de tota iudicabis? Item de contrariis, a quibus ad genera formasque generum venerunt. Sit enim idem caecus, debilis. Duo Reges: constructio interrete.
                                    <BR>
                                    Lorem ipsum dolor sit amet, consectetur adipiscing elit. Et non ex maxima parte de tota iudicabis? Item de contrariis, a quibus ad genera formasque generum venerunt. Sit enim idem caecus, debilis. Duo Reges: constructio interrete.
                                </p>
                            </blockquote>
                            <h5>&nbsp;&nbsp;&nbsp;<?PHP echo $rs['agency_addr_landmark']; ?></h5>
                            <h5>&nbsp;&nbsp;&nbsp;<?PHP echo $rs['agency_city']. ' - ' . $rs['agency_pin'] . ' [' . $rs['agency_state'] . '] ' ; ?></h5>                                            
                            <h5>&nbsp;&nbsp;&nbsp;<?PHP echo $rs['agency_contact_per_1']; ?></h5>

                            <h5>&nbsp;&nbsp;&nbsp;<font color = "maroon"><?PHP echo $rs['agency_contact_addr1'];?></font></h5>

                            <p style="align:right;">
                                <a href="#" class="tags" style="background-color:darkgreen;">Details</a>
                                <a href="#" class="tags" style="background-color:#955251;">Update</a>
                                <a href="#" class="tags" style="background-color:#5B5EA6;">More</a>
                            </p>
                            <BR>

                        </li>
                        <BR>
                        <?PHP
                    }
                ?>
                <script type="text/javascript">var last_id = <?PHP echo $last_id; ?>;</script>
            </ul>
            <!-- this is the paging loader, now is hidden, it wiil be shown when we scroll to bottom --> 
            <p id="loader"><img src="images/ajax-loader.gif"></p>
        </div><!-- content -->          
        <hr>
    </div>
</div>

我的问题是

(1) 如果我在给定的两个 MySQL 查询中按 ASC 顺序对记录进行排序，则代码可以正常工作。但是，如果我将排序顺序从 ASC(如链接教程中给出的那样)更改为 DESC，则逻辑仅列出 5 条记录，然后再次列出 4、3、2、1 条记录并停止工作。我知道我在逻辑上做了一些非常愚蠢的事情，但无法找到我的错误。请帮助我。

(2) 假设我需要按其他列排序(这不是 PRIMARY 键 - id 在这里是 PRIMARY KEY)例如假设我想按 agency_nm 对记录进行排序，并以底部较少的列表方式使用它列出它们，我是否也应该在 AJAX 部分(或 load_more.php)中进行一些更改？抱歉，我不擅长 AJAX - 因此提出了这个愚蠢的问题。

感谢/问候大家的帮助和建议。

Answer 1

改变行:

$sql = 'SELECT * FROM agency_list WHERE id > :last_id ORDER BY id DESC LIMIT 0, :limit';
$sql = 'SELECT * FROM agency_list WHERE id < :last_id ORDER BY id DESC LIMIT 0, :limit';

您所要做的就是将此运算符:> 更改为 <