Sorting column with pagination by clicking column header using php and mysql

                       All of you know how to sorting column while clicking column header like mysql table in database through my previous post. Don't you know? Please visit this sorting column by clicking column header using php .

                       Now we are going to see about sorting column with pagination like table in mysql database. You sorting column by clicking column header in mysql table and you paginated to next page. You will get result followed by next value.
                       ie, you sort column by id. First 30 values displayed in first page in mysql table. When you paginate to next page, you will get output from 31st to 60 in your mysql table. Can you make your table like mysql table with sorting and pagination? Yes, you can also done pagination using php.
                      Already you know the sorting concepts. So now we will see sorting with pagination concepts.

                      1. Start and limit values based on page number:

$limit=10;
 $page=$_GET['p'];
 if($page=='')
 {
  $page=1;
  $start=0;
 }
 else
 {
  $start=$limit*($page-1);
 }

     Where,
                  $limit is the number rows per page.
                  $page is page number.
                  $start is starting point of limit in mysql query.

                    2. Total number of data in mysql table:
Then we need to calculate the total number of data in table. Then find the maximum pages like below:

$total_values=mysql_query("SELECT game_ID, category_Name, game_Title FROM table1");
$total=mysql_num_rows($total_values);
$maxpage=ceil($total/$limit);

     Where,
                mysql_num_rows() returns the numbers results in numeric.
                ceil() returns the whole digit number. ie, ceil(2.3) => 3.
                $maxpage returns the number of pages.

                     3. Function for pagination:

function pagination($maxpage,$page,$url,$field,$sort)
{  
  //After you sorting your table by clicking particular column, the following trick is used to display 
   //values with similar sorted values.
  if($sort=='ASC')
  {
    $sort='DESC';
  }
  else
  {
    $sort='ASC';
  }
  echo'<ul style="list-style-type:none;">';
   for($i=1; $i<=$maxpage; $i++)
   {
    if($i==$page)
{
 echo'<li style="float:left;padding:5px;">'.$i.'</li>';
}
else
{
 echo'<li style="float:left;padding:5px;"><a href="sort.php?p='.$i.'&sorting='.$sort.'&field='.$field.' ">'.$i.'</a></li>';
}
   }
  echo'</ul>';
}
pagination($maxpage,$page,$url,$field,$sort);

          Where, you have to use small tricks. You have to pass the sorting and field values in url using function.
ie, Already we set if the url get 'ASC' value, then it will take as 'DESC'. This is for sorting. Similarly, you have to do same thing for pagination.

Sorting column with pagination by clicking column header:

The total php script as follows for both sorting and pagination  by clicking column header:

<?php
error_reporting(E_ALL ^ E_NOTICE);
mysql_connect('localhost','root','') or die(mysql_error());
mysql_select_db('new');
$field='game_ID';
$sort='ASC';
if(isset($_GET['sorting']))
{
  if($_GET['sorting']=='ASC')
  {
  $sort='DESC';
  }
  else { $sort='ASC'; }
}
if($_GET['sorting'])
{
if($_GET['field']=='game_ID')
{  $field = "game_ID";  }
elseif($_GET['field']=='category_Name')
{ $field = "category_Name"; }
elseif($_GET['field']=='game_Title')
{ $field="game_Title"; }
}
//pagination
 $url='sort.php';
 $limit=10;
 $page=$_GET['p'];
 if($page=='')
 {
  $page=1;
  $start=0;
 }
 else
 {
  $start=$limit*($page-1);
 }

$sql = "SELECT game_ID, category_Name, game_Title FROM table1 ORDER BY $field $sort limit $start, $limit";
$total_values=mysql_query("SELECT game_ID, category_Name, game_Title FROM table1");
$total=mysql_num_rows($total_values);
$maxpage=ceil($total/$limit);
$result = mysql_query($sql) or die(mysql_error());
echo'<table border="0">';
echo'<th><a href="sort.php?sorting='.$sort.'&field=game_ID">Game Id</a></th>
     <th><a href="sort.php?sorting='.$sort.'&field=category_Name">Category Name</a></th>
<th><a href="sort.php?sorting='.$sort.'&field=game_Title">Game Name</a></th>';
while($row = mysql_fetch_array($result)) {
echo'<tr><td>'.$row['game_ID'].'</td><td>'.$row['category_Name'].'</td><td>'.$row['game_Title'].'</td></tr>';
}
echo'</table>';
function pagination($maxpage,$page,$url,$field,$sort)
{
  //After you sorting your table by clicking particular column, the following trick is used to display
   //values with similar sorted values.
  if($sort=='ASC')
  {
    $sort='DESC';
  }
  else
  {
    $sort='ASC';
  }
  echo'<ul style="list-style-type:none;">';
   for($i=1; $i<=$maxpage; $i++)
   {
    if($i==$page)
{
echo'<li style="float:left;padding:5px;">'.$i.'</li>';
}
else
{
echo'<li style="float:left;padding:5px;"><a href="sort.php?p='.$i.'&sorting='.$sort.'&field='.$field.' ">'.$i.'</a></li>';
}
   }
  echo'</ul>';
}
pagination($maxpage,$page,$url,$field,$sort);
?>

               Now you will get output  like this:

               Now you can sorting column with pagination by clicking column header.          

Related Post:

Simple pagination concepts using php and mysql
Pagination using php, mysql and ajax

Order by in mysql table

                      If your column values in mysql table contains more rows, then you can't get the highest and lowest values. On that time, you can sort the column values ascending and descending by 'ORDER BY' mysql command with 'ASC' and 'DESC'. The mysql query for sort column values in mysql table as follwos as:
                       SELECT * FROM table_name ORDER BY field_name ASC( for ascending)
                       SELECT * FROM table_name ORDER BY field_name DESC( for descending)

Order by in Mysql:

                                      Consider the following table. table1 is name of this table.

                              Now we are going to sort column 'id' in ascending order. The mysql query for this:

                       SELECT * FROM table1 ORDER BY id ASC

Now you'll get output like this:

                           You can see the column 'id' values are sorting in ascending order.

Order by multiple columns in mysql:

                           You can sort the multiple columns in single mysql query. The mysql query as follows as:

                       SELECT * FROM table1 ORDER BY id ASC, date DESC 

             Where,
                        - The column 'id' got the first preference to sort
                        - Then the column 'date' is sort
Your output like this:

                        You can see the mysql table 'table1' which is sorted by id in ascending order and date in descending order.

Related Post:

distinct in mysql
Group by in mysql
How to use order by together with group by in mysql
Difference between delete truncate drop in mysql
how to use order by before group by in mysql
Primary key in mysql