“加载更多”按钮不能正确排序结果

Question

I have a problem that has been bugging me for a while now. I have 10 activities on my page and at the bottom of it I have a "load more"-button that loads another 10 rows from MySQL using AJAX/jQuery. The problem is that the ordering of the results is incorrect. The first 10 rows are ordered correctly, but when I click the "load more" button it loads another 10 rows whose category and date don't correspond with the first 10 rows.

For instance:

     **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

The first 5 rows are sorted correctly by location, category and then date

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

However for instance if I load the next 5 rows these aren't sorted so they match the first ones. It would probably look like this if I try pressing "load more" button:

     **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

It probably has something to do with the LIMIT 10 in my query? I appreciate all help!

Here is my query for my first 10 rows:

   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

And for my next 10 rows:

    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

Here is an example of pagination that should work for you. You will have to send in the page you want to load each time but it will work for what you are doing.

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
);