I'm going to refrain from posting my massive code block and just start exactly what I nailed the issued down to.
I'm running this query:
...
SELECT id, Title, images, recdate, 'item' AS type
FROM ads_list
WHERE to_days(now())<= (to_days(recdate)+14)
ORDER BY recdate DESC
LIMIT $offset, $listsperpage
...
This is for a pagination script. My $offset is just a count of the current records position and $listsperpage is set to an arbitrary number per pages (and this is set to 24). So there are supposed to be 24 results per page...
Everything is working perfectly fine EXCEPT, when I run my query in DESC by recdate sort, there are only 23 records outputted in my array on the first page??
And if I run with ASC sort, there are 24 records that display as expected on each page.
My recdate field is formatted like so:
2012-01-14 07:10:33
2012-01-14 07:10:35
2012-01-14 07:10:38
2012-01-14 07:10:30 ...
I also tried to do a DESC sort by id (auto) and still the first result array only contains 23 records, but the total array are all expected records.
I found the "missing" record from the first result is actually pushed forward, so its as if there is just a gap in the results with DESC. And that array is pushed completely forward to the last results page.. So all items are shifted one slot it appears.
I did not want to post up my entire code block because it's a lot, and I've spent a few hours testing and trying variations of my code, so I really think it's something to do with the SQL sort.
I just can't see my DESC would cause this oddity and ASC sort shows as normal.
Finally figured this nightmare out!
I still can't explain why it happens, but at least I am getting full expected results now.
In my while loop, I check the $list["images"] != "". I turns out when using DESC sort on recdate, there is a blank array value, so it evaluates to true and "drops" that listing from the page. So I added $list["images"] == "" and now the image and data displays for that array item.