http://coldfusion.com logo
Join Slack
Powered by
Welcome back Datatables Now has anyone done the P...
# cfml-general
s
Welcome back Datatables Now has anyone done the Pagination using mysql limit and offset, i am doing but my results are always empty here is my gist what is wrong here, any clues https://trycf.com/gist/9fa88c22da3d85270d6e5d2758adb66d/lucee5?theme=monokai
m
thx for the gist.. first question, how many records dump when you run the query without the limit?
here's a working example of limit and offset: https://extendsclass.com/mysql/68a04ce also, swap variables (even though in your test it works the same): LIMIT #start# OFFSET #length# should be LIMIT #length# OFFSET #start#
c
datatables always gets fun with serverside
s
but still some issues
total records i have 16000
it seems my start variable has wrong value, from the datatascript, it is going as 0 in form scope
m
limit and offset works... start a bit more slowly to understand the issue. First do a TOP 50 in the query and dump that data, then put a LIMIT 10 OFFSET 5 and see how it looks. Once you know the querying is solid, you can focus on the logic before the query
... also when troubleshooting like this, be sure the where statement isn't messing you up.
s
yes, top not going to work, because it is mySQL and i confirmed with the data of limit and offset and it seems when it first loads, it goes from limit 200,-200 because if you see in the code i am doing an offset caclculation which is start-1 * 200, so that is endidng up wrong results, now i am not sure how do i fix the start because it has to start from 1, if i need to caluclate the fetch properly and make the pagination work
m
I mean limit without the offset, early morning
s
ok let me try that
but incase if you want to know what is the offset i am getting, is this: LIMIT 200 OFFSET 0 next time LIMIT 200 OFFSET 40000 and records i have is just 16000
it seems if i remove offset, its working good and without any issues
so why we have offset
now i noticed, pagination does not move
numbers in pagination change but the records stay 1 to 200
c
worth posting a full gist with the frontend - could be anything going on inbetween front / back. easy to get things wrong with datatables - totally doable though
s
hmm, ok i will create a gist
c
if your using serverside you would only want to pass in the however many X records per page you want to load
m
yeah, you are feeding it a bad value from the front-end. pagination really works in mysql. I'd bet if you did a cfset of start and length in your getdata function, you would get the results you want.
c
datatables should send all the information you need in its request to work out how many records with what start/offset etc its its request to the backend
i think you should have a 
start
and 
length
parameter passed from datatables that should do the job with your query - only issue might be JS being base 0 and that needing a little +1 here and there
s
ok, so you know how it fixed, i did not passed any arguments to a CFC, i just used direct form scope in a cfc (which is basically in my terms is wrong), but it started working (i am still looking for an answer, why on the cfc function, it behaved odd
m
good to hear.. you're welcome 🙂
Open in Slack
PreviousNext
Powered by