"load more"按钮无法正确排序结果

Question

我有一个问题困扰了我一段时间。我的页面上有10个活动，在页面底部有一个"加载更多"按钮，可以使用AJAX/jQuery从MySQL加载另外10行。问题是结果的排序不正确。前10行的顺序是正确的，但当我单击"加载更多"按钮时，它会加载另外10行，这些行的类别和日期与前10行不一致。

例如：

     **Title**            **Category**          **Location**      **Date**   
     Title 1              Boardgames            Copenhagen        11/9/2015
     Title 2              Boardgames            Copenhagen        12/9/2015
     Title 3              Social Activity       Copenhagen        14/9/2015
     Title 4              Sports                London            16/9/2015
     Title 5              Sports                London            23/9/2015

前5行按位置、类别和日期正确排序

ORDER BY k.name DESC,aa.category_id,aa.date

然而，例如，如果我加载接下来的5行，这些行没有排序，因此它们与第一行匹配。如果我试着按下"加载更多"按钮，它可能会是这样的：

     **Title**            **Category**          **Location**      **Date**   
     Title 1              Boardgames            Copenhagen        11/9/2015
     Title 2              Boardgames            Copenhagen        12/9/2015
     Title 3              Social Activity       Copenhagen        14/9/2015
     Title 4              Sports                London            16/9/2015
     Title 5              Sports                London            23/9/2015
     Title 6              Boardgames            Copenhagen        13/9/2015
     Title 7              Boardgames            Copenhagen        14/9/2015
     Title 8              Social Activity       Copenhagen        14/9/2015
     Title 9              Sports                London            16/9/2015
     Title 10             Sports                London            22/9/2015

这可能与我的查询中的LIMIT 10有关？我感谢所有的帮助！

以下是我对前10行的查询：

   SELECT ar.id,ar.title,ar.time,ar.href,k.name AS kommune,aa.date,c.name AS cat
                                FROM activity_results ar
                                INNER JOIN activity_analyzer aa 
                                ON aa.activity_id = ar.id
                                INNER JOIN user_activity_preferences uap
                                ON uap.category_id = aa.category_id
                                INNER JOIN user_kommune_preferences ukp
                                ON ukp.kommune_id = aa.kommune_id
                                INNER JOIN kommuner k 
                                ON k.kommune_id = aa.kommune_id
                                INNER JOIN categories c
                                ON c.id = aa.category_id
                                INNER JOIN users u
                                ON u.id = ukp.user_id
                                WHERE (uap.user_id AND ukp.user_id = '$_SESSION[user_id]')
                                AND ar.status = '2'
                                AND aa.overtegnet = '0'
                                AND aa.date != '0000-00-00'
                                AND aa.date >= now()
                                AND (aa.gender = '0'OR aa.gender = u.gender)
                                ORDER BY k.name DESC,aa.category_id,aa.date
                                LIMIT 10

对于我接下来的10行：

    SELECT ar.id,ar.title,ar.time,ar.href,k.name AS kommune,aa.date,c.name AS cat
            FROM activity_results ar
            INNER JOIN activity_analyzer aa 
            ON aa.activity_id = ar.id
            INNER JOIN user_kommune_preferences ukp
            ON ukp.kommune_id = aa.kommune_id
            INNER JOIN kommuner k 
            ON k.kommune_id = aa.kommune_id
            INNER JOIN categories c
            ON c.id = aa.category_id
            INNER JOIN users u
            ON u.id = ukp.user_id
            WHERE ukp.user_id = '$_SESSION[user_id]'
            AND ar.status = '2'
            AND aa.overtegnet = '0'
            AND aa.date != '0000-00-00'
            AND aa.date >= now()
            AND (aa.gender = '0'OR aa.gender = u.gender)
            AND ar.id > $last_activity_id
            ORDER BY k.name DESC,aa.category_id,aa.date
            LIMIT 10

Answer 1

这里有一个分页的例子，应该对您有用。你每次都必须发送你想要加载的页面，但它对你正在做的事情有效。

if(!isset($page)){
    $page = 1;
}
$limit = 10;
$start = ($page - 1) * $limit + 1
$query = sprintf(
    'SELECT ar.id,ar.title,ar.time,ar.href,k.name AS kommune,aa.date,c.name AS cat
    FROM activity_results ar
    INNER JOIN activity_analyzer aa 
    ON aa.activity_id = ar.id
    INNER JOIN user_activity_preferences uap
    ON uap.category_id = aa.category_id
    INNER JOIN user_kommune_preferences ukp
    ON ukp.kommune_id = aa.kommune_id
    INNER JOIN kommuner k 
    ON k.kommune_id = aa.kommune_id
    INNER JOIN categories c
    ON c.id = aa.category_id
    INNER JOIN users u
    ON u.id = ukp.user_id
    WHERE (uap.user_id AND ukp.user_id = '$_SESSION[user_id]')
    AND ar.status = '2'
    AND aa.overtegnet = '0'
    AND aa.date != '0000-00-00'
    AND aa.date >= now()
    AND (aa.gender = '0'OR aa.gender = u.gender)
    ORDER BY k.name DESC,aa.category_id,aa.date
    LIMIT %s,%s', $start, $limit
);