Click a button to sort a table in jQuery

Question

I have a pagination that works great but I need to add buttons before the table so it can sort by "id", "name", etc.. So one button for id and one for name!

I have this code in jQuery at index.php so you can browse fast between pages..

 <script type="text/javascript">
$(document).ready(function(){
    function loading_show(){
        $('#loading').html("<img src='images/loading.gif'/>").fadeIn('fast');
    }
    function loading_hide(){
        $('#loading').fadeOut('fast');
    }                
    function loadData(page){
        loading_show();                    
        $.ajax
        ({
            type: "POST",
            url: "load_data.php",
            data: "page="+page,
            success: function(msg)
            {
                $("#containers").ajaxComplete(function(event, request, settings)
                {
                    loading_hide();
                    $("#containers").html(msg);
                });
            }
        });
    }
    loadData(1);  // For first time page load default results
    $('#containers .pagination li.active').live('click',function(){
        var page = $(this).attr('p');
        loadData(page);

    });           
    $('#go_btn').live('click',function(){
        var page = parseInt($('.goto').val());
        var no_of_pages = parseInt($('.total').attr('a'));
        if(page != 0 && page <= no_of_pages){
            loadData(page);
        }else{
            alert('Skriv ett nummer mellan 1 och '+no_of_pages);
            $('.goto').val("").focus();
            return false;
        }

    });


});

load_data.php - php to get things from mysql

    <?php
if($_POST['page'])
{
$page = $_POST['page'];
$cur_page = $page;
$page -= 1;
$per_page = 15;
$previous_btn = true;
$next_btn = true;
$first_btn = true;
$last_btn = true;
$start = $page * $per_page;
include "config.php";

$query_pag_data = "SELECT id,name from th_list ORDER BY id DESC LIMIT $start, $per_page";
$result_pag_data = mysql_query($query_pag_data) or die('MySql Error' . mysql_error());
$msg = "";

echo '<table border="0" cellspacing="0">
    <tr id="head"><th width="30">Kategori</th><th>Genre</th><th>Namn</th><th><img src="https://cdn1.iconfinder.com/data/icons/Sizicons/16x16/download.png"></th><th>Klick</th><th>Kommenterarer</th></tr>
';
while ($row = mysql_fetch_array($result_pag_data)) {
$htmlmsg=htmlentities($row['name']);
    $msg .= "<tr><td># 1</td><td>Drama</td><td>" . $htmlmsg . "</td><td><img src='https://cdn1.iconfinder.com/data/icons/diagona/icon/16/095.png'></td><td>2</td><td>3</td></tr>";

}
$msg = "<div class='data'><ul>" . $msg . "</ul></div></table><br />"; // Content for Data


/* --------------------------------------------- */
$query_pag_num = "SELECT COUNT(*) AS count FROM th_list";
$result_pag_num = mysql_query($query_pag_num);
$row = mysql_fetch_array($result_pag_num);
$count = $row['count'];
$no_of_paginations = ceil($count / $per_page);

/* ---------------Calculating the starting and endign values for the loop----------------------------------- */
if ($cur_page >= 7) {
    $start_loop = $cur_page - 3;
    if ($no_of_paginations > $cur_page + 3)
        $end_loop = $cur_page + 3;
    else if ($cur_page <= $no_of_paginations && $cur_page > $no_of_paginations - 6) {
        $start_loop = $no_of_paginations - 6;
        $end_loop = $no_of_paginations;
    } else {
        $end_loop = $no_of_paginations;
    }
} else {
    $start_loop = 1;
    if ($no_of_paginations > 7)
        $end_loop = 7;
    else
        $end_loop = $no_of_paginations;
}
/* ----------------------------------------------------------------------------------------------------------- */
$msg .= "<div class='pagination'><ul>";

// FOR ENABLING THE FIRST BUTTON
if ($first_btn && $cur_page > 1) {
    $msg .= "<li p='1' class='active'>Första</li>";
} else if ($first_btn) {
    $msg .= "<li p='1' class='inactive'>Första</li>";
}

// FOR ENABLING THE PREVIOUS BUTTON
if ($previous_btn && $cur_page > 1) {
    $pre = $cur_page - 1;
    $msg .= "<li p='$pre' class='active'>Föregående</li>";
} else if ($previous_btn) {
    $msg .= "<li class='inactive'>Föregående</li>";
}
for ($i = $start_loop; $i <= $end_loop; $i++) {

    if ($cur_page == $i)
        $msg .= "<li p='$i' style='color:#fff;background-color:#006699;' class='active'>{$i}</li>";
    else
        $msg .= "<li p='$i' class='active'>{$i}</li>";
}

// TO ENABLE THE NEXT BUTTON
if ($next_btn && $cur_page < $no_of_paginations) {
    $nex = $cur_page + 1;
    $msg .= "<li p='$nex' class='active'>Nästa</li>";
} else if ($next_btn) {
    $msg .= "<li class='inactive'>Nästa</li>";
}

// TO ENABLE THE END BUTTON
if ($last_btn && $cur_page < $no_of_paginations) {
    $msg .= "<li p='$no_of_paginations' class='active'>Sista</li>";
} else if ($last_btn) {
    $msg .= "<li p='$no_of_paginations' class='inactive'>Sista</li>";
}
$goto = "<div style='float: right;'><input type='text' class='goto' style=' float: left; margin-left: 10px; width: 30px; height: 8px; '/><input type='submit' id='go_btn' style='width: 40px; padding: 0; margin-left: 5px; float: left; margin-top: 0px;height: 27px;' value='Gå'/></div>";
$total_string = "<span class='total' a='$no_of_paginations'>Sida <b>" . $cur_page . "</b> av <b>$no_of_paginations</b></span>";
$msg = $msg . "</ul>" . $goto . $total_string . "</div>";  // Content for pagination
echo $msg;
}
?>

And then on index.php I write this, to load all the data..

 <div id="loading"></div>
    <div id="containers">
        <div class="data"></div>
        <div class="pagination"></div>
    </div>

Would be great if you can help me! :)

I want a button that says "ID". Like = Sort by [id], and when you click on the button.. it will automatically sort by id, in the same effect/way when I click on page 2 in the pagination! :) Sorry if I'm unclear.. — Johan, Nov 26 '13 at 19:17

score 0 · Accepted Answer · answered Nov 26 '13 at 19:25

Here is a demo how you can accomplish this try adding anchor in your <th> elements use title attribute of anchor as the column name

echo '<table border="0" cellspacing="0">
<tr id="head">
<a href="javascript:void(0)" class="sorter" title="Kategori"><th width="30">Kategori</th></a>
<a href="javascript:void(0)"  class="sorter" title="Genre"><th>Genre</th></a>
<a href="javascript:void(0)" class="sorter"  title="Namn"><th>Namn</th></a>
<th><img src="https://cdn1.iconfinder.com/data/icons/Sizicons/16x16/download.png"></th>
<a href="javascript:void(0)" class="sorter"  title="Klick"><th>Klick</th></a>
<a href="javascript:void(0)" class="sorter"  title="Kommenterarer"><th>Kommenterarer</th></a>
</tr>
';

Add a click function for class sorter

$('.sorter').live('click',function(){
    var sorter = $(this).attr('title');

var page = $('#containers .pagination li.inactive:eq(0)').attr('p'); /* get current page no.*/
    loadData(page,sorter);

});

slight change in your loadData() pass sorter to load_data.php also

data: "page="+page+"&sorter="+sorter,

Now in your query get the sorter and do order by

if($_POST['sorter']=="Kategori" || empty($_POST['sorter'])){
$order_column='id'; /* add conditions for other $_POST['sorter'] values */ 
}
$query_pag_data = "SELECT id,name from th_list ORDER BY $order_column DESC LIMIT $start, $per_page";

Please don't use mysql* functions they are depreciated instead use prepared statements or atleast use mysqli*

Hope it makes sense and will give you idea to step ahead on sorting like ASC/DESC

Hi! Thanks for the time:) But when I change data: "page="+page, to data: "page="+page+"&sorter="+sorter, The whole table disappear :( — Johan, Nov 26 '13 at 19:49
Sorry. My fault. I changed this; function loadData(page){ to function loadData(page, sorter){. — Johan, Nov 26 '13 at 19:58
@Johan read this http://stackoverflow.com/questions/1820927/request-monitoring-in-chrome debug your ajax call and tell me what error you are getting — M Khalid Junaid, Nov 26 '13 at 20:00
I'm not sure where I found errors.. but it says: Form Data: page=1&sorter=Kategori .. so it seems that it's right? — Johan, Nov 26 '13 at 20:46
Ok. It seems that the PHP is wrong. I can get out the _POST but when I write in i ORDER BY '".$sort."', it doesn't work! — Johan, Nov 26 '13 at 21:12

score 0 · Answer 2 · answered Nov 26 '13 at 19:47

place a link in your page to do it with ajax call as such :

<a href="javascript:void(0);" onclick="sortby_id()" >SORT BY ID </a>

and attach a script file, define your function in it:

function sortby_id() {
    $.ajax({
                type:"POST",
                //data:"",
                url:"http://www.yourdomain.com/yourpage.php?page=2",// you can use more variable, just pass it to the url here
                success: function(data){
                    //write into div when successful handling


                }, //end of success
                error: function(){
                    // give your message when there is en error.
                    }// end of error

            });
}// end of function

you the one above if you like to use ajax. this does not make the page reload. and use below if you do not want to use ajax or any javascript Generate you link with variable:

<a href="yourpage.php?page=2&sortby=id">SORT BY ID</a> // this will reload your page with page number two and variable will pass via url to handle it in your php file.

Hope this helps

score 0 · Answer 3 · answered Nov 26 '13 at 20:45

0

I you don't mind using a plugin I'd suggest this: http://tablesorter.com/docs/#Demo

answered Nov 26 '13 at 20:45

viljun

370
3
11

Yeah.. but actually I want ONE button that can sort my table, not by clicking the tables titles.. :) – Johan Nov 26 '13 at 20:48

Click a button to sort a table in jQuery

3 Answers3