load 10 records per click cfc,ajax,jquery

Question

I have a test code that works, at least partially, returning data from cfc using ajax call on click

here is the code:

<script type="text/javascript"> 
    $(document).ready(function(){
            $('#showteams').click(function() {
                $.ajax({
        url: "test.cfc?method=getAllTeams&returnformat=JSON",
        dataType: 'json',
        data: { startrow: "10", endrow: "10" },

        success:function(data) {
            $.each(data, function(i,team){

            $('#teamList').append('<li><a href="#">' + team[0] + '</a> this is the username ' + team[1] + ' ----- name --- ' + team[2] + '');
                    });

                }});
            });
            $('#teams').show();
            $('#teamList').fadeIn(1200);
            $(this).attr('disabled', 'disabled');
        });
</script>

and a simple cfc (the final one will be much more complex but also flexible enough to be changed when needed)

<cfcomponent output="false">
     <cffunction name="getAllTeams" access="remote" output="false" returntype="any">
     <cfargument name="startrow" required="yes" type="any">
     <cfargument name="endrow" required="yes" type="any">
     <cfargument name="cat" required="no" type="any" hint="add later with the rest of the code">

        <cfquery name="qGetAllTeams" datasource="datasource">
            SELECT ID,NAME,LASTNAME
            FROM table
        </cfquery>

        <cfset one=arraynew(1)> 
        <cfset two=arraynew(1)> 
        <cfloop query="qGetAllTeams" startrow="#arguments.startrow#" endrow="#arguments.endrow#"> 
            <cfset one[1]=ID> 
            <cfset one[2]=NAME> 
            <cfset one[3]=LASTNAME> 
            <cfset ArrayAppend(two,#one#)>
        </cfloop> 

        <cfreturn two />

     </cffunction>
</cfcomponent>

the code above works but displays only the first 10 records because values are set manually within - data: { startrow: "10", endrow: "10" }, in javascript

while I can easily build this or any sort of paging using only server side code the entire javascript is confusing me here and have no idea how to sort it out. What ever I try in cfc, to mathematically calculate startrow and endrow to be used in cfloop (curently i just put startrow="#arguments.startrow#" endrow="#argumentsendrow#" though this should be changed obviously :) ) it return only the first 10 records because startrow is set to 10 in javascript. I am definitely missing some basic logic here and would really appreciate your help on this one.

score 0 · Answer 1 · answered Oct 21 '12 at 13:59

0

You need to track the row start in javascript if you want paging to be handled client side.

$(document).ready(function() {
    var startRow=1;
    $('#showteams').click(function() {
       var endRow= startRow+9;

        $.ajax({
            url: "test.cfc",
            dataType: 'json',
            data: { 
                method : 'getAllTeams',
                returnformat : 'JSON',
                startrow: startRow,
                endrow: endRow
            },

            success: function(data) {
                /* increment startRow*/
                startRow +=10;
                $.each(data, function(i, team) {

                    $('#teamList').append('<li><a href="#">' + team[0] + '</a> this is the username ' + team[1] + ' ----- name --- ' + team[2] + '');
                });

            }
        });
    });
    $('#teams').show();
    $('#teamList').fadeIn(1200);
    $(this).attr('disabled', 'disabled');
});

Note how I changed the url to move the search key/values to the data object of the AJAX call. jQuery will parse the object into the same urlencoded format

You receive these values as you would any form element where startrow is equivalent to name on a form field. Also your query statement is looking up all entries

answered Oct 21 '12 at 13:59

charlietfl

170,828
13
121
150

first of all, I would like to thank you for your time and effort to help me understand what I'm doing wrong, really appreciate that :) innitially I wanted to handle everything on the server side though I was confused what to do with javascript, anyway your code works well. "Also your query statement is looking up all entries" – user1751287 Oct 21 '12 at 14:51
"Also your query statement is looking up all entries" I simply do not see any other way to handle it but with cfloop as the query will be a bit more complex say ` SELECT ID,NAME,LASTNAME FROM table WHERE cat="#arguments.cat1#" and subcat="#arguments.subcat2#" ORDER BY DATE ` – user1751287 Oct 21 '12 at 14:59
you can add the start/end as limit to query also using the variables LIMIT( #start#, #end#) My coldfusion is rusty – charlietfl Oct 21 '12 at 15:01
I do not think LIMIT will help here as it will work exactly the same as maxrows attribute for cfquery, though I'll try with it. simpler is better when it comes to coding :) – user1751287 Oct 21 '12 at 15:17
pulling DB data is different than parsing results. With no limit set you will pull all entries each time – charlietfl Oct 21 '12 at 15:18
yes I know that :) I've just tried with LIMIT and once it's set clicking on button will have no effect, namely it will display only the first one. here is what I've tried LIMIT #arguments.startrow#, #arguments.endrow# it displays the second one for some reason – user1751287 Oct 21 '12 at 15:24
I think I will be able to sort it out, and instead of cfloop and arrays I'll serizlizeJSON(queryname) for output – user1751287 Oct 21 '12 at 15:27
exactly what i was going to suggest. Send back all the data sent to server first. SO you get a full understanding of AJAX interaction. You can inspect the request in a browser console – charlietfl Oct 21 '12 at 15:33
i sort it out :) i needed to put var startRow=0; because LIMIT starts from 0, and do some other changes :) also while I am doing everything on the client side how can hide
– user1751287 Oct 21 '12 at 15:49
just before $.ajax `var $el=$(this).hide()` then after `each` that adds data `$el`.show()` – charlietfl Oct 21 '12 at 15:54

load 10 records per click cfc,ajax,jquery

1 Answers1