Sort paginated table with mysql data

Question

I currently have a table that is populated with mysql data and I have the some jquery code to paginate the data. This works fine, however, I am also using jquery.tablesorter.min.js to sort the tables. This only works on the first page, as soon as you change the page, it only sorts the data on that page instead of all of the data that is being pulled into the table. I need to be able to sort all of the data when clicking the column headers, not just the data on that page that was parsed by the following pagination code:

tablesorter:

<script>
$(document).ready(function() {
 $("#sortedtable").tablesorter({ sortlist: [0,0] });
});
</script>

pagination:

 <script type="text/javascript">
$(document).ready(function() {

//Declaring Variables

var page = 1;
var per_page = 20;
var items = $('.row').length;
var page_last = Math.ceil(items/per_page);

//Set Page  

function setPage(page) {
    $('.row').slice(0, page * per_page).hide();
    $('.row').slice(page * per_page - per_page, page * per_page).show();
    $('.row').slice(page * per_page).hide();
    $('#page_display').html('Page ' + page + ' of ' + page_last);
}

//Next Button

$('.next').click(function() {
    if (page < page_last) {
        page++;
        setPage(page);
    }   
});

//Previous Button

$('.prev').click(function() {
    if (page > 1) {
        page--;
        setPage(page);
    }   
});



setPage(1); 
});
</script>

php/mysql

$cbsql = "SELECT patient_id, AES_DECRYPT(firstName,'$key') as firstName, AES_DECRYPT(lastName,'$key') as lastName, AES_DECRYPT(telephone1,'$key') as telephone1, AES_DECRYPT(email,'$key') as email, cbdate, status, owner FROM patientprofiles WHERE owner = '$owner' and cbdate <= '$today' AND cbdate != 0000-00-00 ORDER BY cbdate ASC";
$mycbData = mysql_query($cbsql,$con);

echo "<table id=sortedtable>
    <thead>\n<tr>
        <th>Date</th>
        <th>Patient</th>
        <th>Phone</th>
        <th>Email</th>
    </tr>\n</thead>\n"; 

while($record=mysql_fetch_array($mycbData)){

$mysql_cbdate = date('m/d/Y', strtotime($record['cbdate']));

echo "<tr class=row>";  
echo "<td>" .$mysql_cbdate. "</td>";
echo "<td>" .     stripslashes($record['lastName']) . ", " .$record['firstName'] . "</td>";
echo "<td>" .$record['telephone1']. "</td>";
echo "<td>" .$record['email']. "</td>";
echo "</tr>";
}
echo "</table>";
?>

Disclaimer I know I should be done using mysql and I will be as soon as this project is finished

Please, [don't use `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). *They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation)*. See the [red box](http://uk.php.net/manual/en/function.mysql-connect.php)? Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://us1.php.net/pdo) or [MySQLi](http://us1.php.net/mysqli). [This article](http://php.net/manual/en/mysqlinfo.api.choosing.php) will help you decide which. — Jay Blanchard, Oct 06 '14 at 16:14

score 0 · Answer 1 · answered Oct 06 '14 at 19:56

you should refresh data from server again with ajax or refreshing same page.

use $.post or $.ajax (documentation here) to reload data from server.

On mysql side, use LIMIT for pagination. "SELECT .... ORDER BY cbdate ASC LIMIT 50, 10;" its tells mysql that skip first 50 rows and return 10 following rows.

(documentation here)

Sort paginated table with mysql data

1 Answers1