all posts

Using browser SQLite’s LIMIT/OFFSET to page results with AJAX

Published to Blog on 22 Feb 2011

Another post mostly for me.

I spent some time tonight trying to solve a problem in a mobile/HTML 5 app I am building. I have two lists that pull data from a client-side database that could possibly contain many records. Rather than pull all the results on page load and dynamically build a huge list I decided to look into doing some sort of paged results. I wanted my results to look something like this:

CropperCapture[114]

 

Luckily SQLite’s SELECT statement supports LIMIT and OFFSET statements that are ideal for supporting AJAX-style paging. Following is psuedocode for the implementation:

 

function getAccounts(pageSize, pageIndex) {
    // setup the db transaction
    var sql = 'SELECT * FROM accounts ORDER BY name LIMIT ' + pageIndex + ',' + pageSize + ';';
    /// execute the sql statement
    if (result == null || result.rows.length == 0) {
        // hide the "More" button
    }
    else {
        for (var i = 0; i < result.rows.length; i++) {
            // dynamically add the item to the list                            
        }
    }
}

 

 


Dan Hounshell
Web geek, nerd, amateur maker. Likes: apis, node, mobile, motorcycles, watches, food, Nashville, Savannah, Cincinnati and family. Dislikes: mean people
Dan Hounshell on Twitter