Sorting column by clicking column header with php and mysql in table

                       All of you use the tables in mysql database. When you click the column header at first time, it displays values in ascending order. Likewise it displays values in descending order, while clicking field name second time. Normally the table look like this:

When you click the category_Name field, it display value by ascending order as follow as:

                      We can make our table like tables in mysql database. We can sorting values in column by clicking filed name. It can be done by PHP.
Follow the below steps.
          1. Connect file with mysql database as follows as:

mysql_connect('server_name','username','password');
mysql_select_db('db_name');

where,
           - server_name means localhost,
           - username is username of your database
           - password is password of your database
           - db_name is name of your database

         2. Then, you need to sorting tables based on column filed name. So you have to pass the field name in url. Similarly, you sort table values both ascending and descending. So you need to also pass order types in url. By default, table values displays in ascending. You can fixed order types and also field name.

$field='game_ID';
$sort='ASC';

where, the mysql table display values by ascending order based on game_ID

Now your headings should be like this.

<th><a href="table1.php?sorting='.$sort.'&field=game_ID">Game Id</a></th>
<th><a href="table1.php?sorting='.$sort.'&field=category_Name">Category Name</a></th>
<th><a href="table1.php?sorting='.$sort.'&field=game_Title">Game Name</a></th>

where,
           - table1.php is the name of file.
           - $sort means order type either ascending or descending
           - game_ID, category_Name, game_Title are field names in mysql table.

        3. Now you need get the values from url.

if(isset($_GET['sorting']))
{
  if($_GET['sorting']=='ASC')
  {
  $sort='DESC';
  }
  else { $sort='ASC'; }
}
if($_GET['field']=='game_ID')
{ 
    $field = "game_ID";  
}
elseif($_GET['field']=='category_Name')
{
   $field = "category_Name"; 
}
elseif($_GET['field']=='game_Title')
{ 
   $field="game_Title"; 
}

where, you handle your statergies. ie, If sorting value is ascending, then you will sort table by descending. Likewise you'll sort table by ascending, if sort value is descending. Then you need to get column field values.
           
       4. Then you write a query in which both order types and field name should be present. The mysql query should be like this.

SELECT game_ID, category_Name, game_Title FROM yobash_game ORDER BY $field $sort

where,
            - $filed is a default field name ie, gameId
            - $sort is a default order type already we fixed. ie. ASC

Sorting column by clicking column header

       5. Finally combine all the PHP codes as follows as:

table1.php

<?php
mysql_connect('localhost','root','') or die(mysql_error());
mysql_select_db('db_name') or die(mysql_error());
$field='game_ID';
$sort='ASC';
if(isset($_GET['sorting']))
{
  if($_GET['sorting']=='ASC')
  {
  $sort='DESC';
  }
  else
  {
    $sort='ASC';
  }
}
if($_GET['field']=='game_ID')
{
   $field = "game_ID";
}
elseif($_GET['field']=='category_Name')
{
   $field = "category_Name";
}
elseif($_GET['field']=='game_Title')
{
   $field="game_Title";
}
$sql = "SELECT game_ID, category_Name, game_Title FROM yobash_game ORDER BY $field $sort";
$result = mysql_query($sql) or die(mysql_error());
echo'<table border="1">';
echo'<th><a href="table1.php?sorting='.$sort.'&field=game_ID">Game Id</a></th>
     <th><a href="table1.php?sorting='.$sort.'&field=category_Name">Category Name</a></th>
<th><a href="table1.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>';
?>

          Finally you'll get PHP scripts for sorting table when you click the column filed name. Now you can sort tables as like as tables in mysql database.

Related Post:
Simple pagination concepts using php and mysql
Pagination using php, mysql and ajax