PHP MySQL Table Data Pagination using TableSort Plugin

By | December 27, 2014
We have Seen Sorting HTML Table element using tablesort plugin in the previous tutorial , here in this tutorial by using the same plugin we will see one more useful feature of it that is pagination of the table element , this feature is useful when you have large amount of data to be displayed into HTML Table element .

Download            Live Demo

Database

Create a database countries with table country .

CREATE TABLE IF NOT EXISTS country (
       idCountry           int(5)    AUTO_INCREMENT,
       countryCode      char(2) ,
       countryName     varchar(45) ,
       currencyCode    char(3) ,
       capital                varchar(30) ,
  PRIMARY KEY (‘idCountry’)
)

Insert data into table you can find the .sql file inside the download and all the table data is available .

Installing TableSorter Plugin

include css file and all required js file like jquery , tablesort and tablesort pager inside the head tag of the html page .

    <head>
      <link rel=”stylesheet” href=”css/style.css” type=”text/css”  />
      <script
type=”text/javascript” src=”js/jquery-latest.js”></script>
      <script
type=”text/javascript” src=”js/jquery.tablesorter.js”></script>
      <script
type=”text/javascript” src=”js/jquery.tablesorter.pager.js”></script>
    </head>

CSS Theming

There are two style sheets which you can find in download one for blue theme  and another for green theme.

    For Blue Theme
         <link rel=”stylesheet” type=”text/css” href=”blue/style.css”>

    For Green Theme
        <link rel=”stylesheet” type=”text/css” href=”green/style.css”>

JavaScript

add the below javaScript inside the head tag of your html page

<script type=”text/javascript“>
    $(function() {
        $(“table”)
            .tablesorter({widthFixed: true, widgets: [‘zebra’]})
            .tablesorterPager({container: $(“#pager”)});
    });
</script>

PHP Script

connect to mysql database with php script

file : config.php

<?php

$mysql_hostname = “localhost”;
$mysql_user = “root”;
$mysql_password = “”;
$mysql_database = “countries”;

$bd = mysql_connect($mysql_hostname, $mysql_user, $mysql_password) or die(“Opps some thing went wrong”);
mysql_select_db($mysql_database, $bd) or die(“Opps some thing went wrong”);

?>

now using php script fetch data from mysql database table and show it on to html table element using the below php script.

file : index.php

<table cellspacing=”1″ class=”tablesorter”>
    <thead>
        <tr>
            <th>Name</th>
            <th>Code</th>
            <th>Currency</th>
            <th>Capital</th>
        </tr>
    </thead>  

    <tfoot>
        <tr>
            <th>Name</th>
            <th>Code</th>
            <th>Currency</th>
            <th>Capital</th>
        </tr>
    </tfoot>

    <tbody>
<?php
include(“config.php”);

$result = mysql_query(“SELECT * FROM country”);

while ($row = mysql_fetch_array($result)) {

    echo“<tr>”.
            “<td>”.$row[‘countryName’].”</td>”.
            “<td>”.$row[‘countryCode’].”</td>”.
            “<td>”.$row[‘currencyCode’].”</td>”.
            “<td>”.$row[‘capital’].”</td>”.
        “</tr>”;
       }
     
?>
    </tbody>
</table>