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