paging through database results using jquery

Question

I'm looking for info on how difficult it will be to page through a number of results from a database using jquery. I have already found a plugin but I don't think it's what I need.

I have a form with 8 textboxes. I want to populate these 8 textboxes with the first database result and then show paging if there are more results. If there are more results then the user should be able to click the next button to have the new data imported into the textboxes.

Anyone have any suggestions?

Answer 1

You haven't mentioned which underlying platform you are using. It is ASP.NET MVC? Ruby on Rails? Paging support is generally built into the underlying platform.

If you want to make an AJAX or JSON call jQuery is certainly capable of doing that, but what that call looks like will depend on which platform you are using.

If it is Ruby, you should find some guidance here: http://www.sitepoint.com/article/ajax-jquery/

Answer 2

The way to do this (or the way I do it) is that I bind ajax calls to the pagination links. The links have the url built-in and know what page they are referring to.

e.g.

<a class="pager" href="/ajax_pages/get_results.php?page=3"> 3 </a>

Now you can intercept the clicks to these links using the jquery live function

$(function() {
      $('a.pager').live('click',function() {
             var url = $(this).attr('href');
             $('#destination').load(url);
      }
 }

Since you're using 'live', you don't have to bind the newly generated HTML manually. And so your links will be ready to go right away.

Then all you have to do is generate the offset value based on the requested page and limit (which your backend service should know).

The alternative method is very close, but requires more work in javascript. Have the service that your pager calls return json encoded data. Your callback function will then have to populate your page with this data and then update your pagination links so that they work correctly.

This may be the more elegent solution than blowing out the entire form every time, but does require more work. It is unlikely that it will be a noticeable difference.

Answer 3

Personally, if I knew I wouldn't ever really have more than 5 pages or so, I would forgo the Ajax thing altogether... Not that I don't like it... I just don't think its necessary in this case.

<?php
// Example Database result (say, 2 "pages" worth)...
// We'll pretend the cells in your database match the textarea names...
$results = array(
    [0] => array(
        'textbox1'=>'abc',
        'textbox2'=>'def',
        'textbox3'=>'ghi',
        'textbox4'=>'jkl',
        'textbox5'=>'mno',
        'textbox6'=>'pqr'
        'textbox6'=>'stu'
        'textbox6'=>'vwx'
    ),
    [1] => array(
        'textbox1'=>'cba',
        'textbox2'=>'fed',
        'textbox3'=>'ihg',
        'textbox4'=>'lkj',
        'textbox5'=>'onm',
        'textbox6'=>'rqp'
        'textbox6'=>'uts'
        'textbox6'=>'xwv'
    )
)
$json_results = json_encode($results);
?>

<!-- Generate some jQuery and HTML -->
<script language="javascript">
    var pages = eval('<?=$json_results;?>');
    $(function() {
        $('.page_num').live('click',function() {
            var page = $(this).attr('rel');
            if(pages[page] && pages[page].length > 0) {
                $.each(pages[page],function(key,value) {
                    // assuming your key names are the same as the
                    // names of your textareas
                    $('textarea[name="'+key+'"]').value(value);
                });
            } else {
                alert("Oops, that page doesn't exist for some reason...");
            }
        });
    });
</script>

<?php foreach($results[0] as $key=>$value): ?>
<textarea name="<?=$key?>"><?=$value;?></textarea>
<?php endforeach; ?>

<div id="page_nums">
<?php for($i=1;$i<=sizeof($results)-1;$i++): ?>
<a class="page_num" href="#" rel="<?=$i;?>"><?=$i;?></a>
<?php endfor; ?>