UFRAGG

Using AJAX, paginate html data tables.

About
Features
Documentation
Downloads

Basic call

<table id="example_tablepaginateQ" class="tablepaginateQ">
</table>
<script type="text/javascript">
$(document).ready( function() {
        $('#example_tablepaginateQ').tablepaginateQ({
                ajax: {
                        url: 'example_data.php'
                },
                rows: 25,
                sort_col: 1,
                sort_cols: [1,3]
        });
});
</script>

Call Options

select code

// default options
var defaults = {
        search_show: true,                              // true - show Q search; false - do not show
        search_label: 'Filter',                 // label for search button
        search_external: null,                  // external container id to serialize and send as search parm
        search_external_btn: null,              // external search button id; to bind to Q search; will try to auto find
        nav_first_btn_label: 'First',   // label for first page button; null do not display
        nav_prev_btn_label: 'Prev',             // label for prev page button; null do not display
        nav_next_btn_label: 'Next',             // label for next page button; null do not display
        nav_last_btn_label: 'Last',     // label for last page button; null do not display
        nav_show_rows: [ 5, 10, 25, 50], // nbr rows allowed to be show
        on_nav_rows_scroll_to_top: true,// auto scroll to top of table when nbr rows shown changes
        nav_nbr_center_pages: 3,                // number of page links to show around current page
        nav_nbr_edge_pages: 2,                  // number pf page links to show at start and end
        nav_nbr_page_spacer: '..',              // label between edge and center page links
        css_even_row: 'even_row',               // zebra stripes
        css_odd_row: 'odd_row',                 // zebra stripes
        simple_table: false, // false|true; true to hide all the Q (nav, search, pagination, ajax)
                                                 // can pass in as table attr data-simple_table="true"
        sort_cols: [ 'all'],  // array of columns to allow sorting; 0 indexed
        
        id: null,                       // unique id; in case multiple tables on page
        target_name: 't_',      // prefix of data sent to server
        page: 1,                        // current page
        offset: 0,                      // current sql offset
        rows: 5,                        // current number of rows to show
        search: '',             // if Q search, text; if external, json
        sort_col: 0,            // current column to sort by; 0 indexed
        sort_order: 'asc',      // sql direction to sort by; asc|desc
        total: null,            // total number of rows in table; required for pagination
        
        msg_initial_loading: 'Data loading..',  // msg if data table empty and fetching data
        msg_loading: 'Loading..',                               // msg for table loading
        
        ajax: { // jquery ajax options
                url: null, // required
                type: 'POST',
                cache: true,
                timeout: 120000, // ms; thus 120s
                data: {
                },
                dataType: 'html'
        },
        
        initial_load: true,  // false|true; 
                                                 // false if table popuplated on page load; 
                                                 // true to do ajax call on page load and populate table
        
        'ignore': null
}; // end defaults

Variables sent back to the server on AJAX interaction

select code

't_' is defined by opts.target_name
 
t_id            // unique id of table
t_page          // page number requested
t_offset        // row offset; for sql limit
t_rows          // number of rows requested
t_search        // what to search for
t_sort_col      // column being sorted
t_sort_order    // asc|desc

Example PHP data source (included in download)

select code

<?php
 
$t_rows = $_POST;
$t_page = $_POST['t_page'];
$t_offset = $_POST['t_offset'];
$t_sort_order = $_POST['t_sort_order'];
if ($t_sort_order != 'asc' && $t_sort_order != 'desc') {
        $t_sort_order = 'asc';
}
$t_sort_col = $_POST['t_sort_col'];
$t_search = $_POST['t_search'];
$t_search = preg_replace('/[^a-zA-Z0-9 ]/', '', $t_search);
 
mysql_connect("localhost", "user", "pwd");
mysql_select_db("demo");
 
// user is responsible for proper sql security, performance, etc
$sql = "SELECT employee_id, employee, tacos_eaten, hired FROM demo WHERE 1 ";
$sql_total = "SELECT COUNT(employee_id) total FROM demo WHERE 1 ";
if (!empty($t_search)) {
        $sql .= " AND employee LIKE '%".$t_search."%' ";
        $sql_total .= " AND employee LIKE '%".$t_search."%' ";
}
 
// sleep(4); // to test slow response time
 
// get total number of records for grid pagination
// usually running another query w/o order by and w/o limit is fast
// alternative method could be one query w/ order by, then
// mysql data_seek() to offset and then in while(mysql_fetch) break on grid_rows = row nbr
$result = mysql_query($sql_total);
$details = mysql_fetch_array($result);
$total = $details['total'];
 
if ($t_sort_col == 1) {
        $sql .= " ORDER BY employee ".$t_sort_order;
} else if ($t_sort_col == 3) {
        $sql .= " ORDER BY hired ".$t_sort_order;
}
$sql .= " LIMIT ".$t_offset.", ".$t_rows;
 
$result = mysql_query($sql);
?>
<table class="tablepaginateQ" data-total="<?php echo $total?>">
        <thead>
        <tr class="header">
        <th>Employee #</th>
        <th>Employee</th>       
        <th>Tacos Eaten</th>
        <th>Hired</th>
        </tr>
        </thead>
        <tbody>
<?php
while ($details = mysql_fetch_array($result)) {
        echo '<tr>';
        echo '<td>'.$details['employee_id'].'</td>';
        echo '<td>'.$details['employee'].'</td>';
        echo '<td>'.$details['tacos_eaten'].'</td>';
        echo '<td>'.$details['hired'].'</td>';
        echo '</tr>';   
}
?>
</tbody>
</table>