PHP, MySQL, Drupal, .htaccess, Robots.txt, Phponwebsites: Sorting column by clicking column header with php and mysql in table

7 May 2014

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:


sorting column values using PHP and Mysql at phponwebsites


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


ascending and descending column values while clicking column header using PHP and Mysql at phponwebsites


                      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.

43 comments:

  1. Thank you much for this post. I tried lots of time before see this post. I can't get correct answer. But now i know the sorting columns by clicking column header.
    I need pagination concept with sorting column. Could you please post? Thanks in advance.

    ReplyDelete
  2. thank u very much for that post...nice

    ReplyDelete
  3. How do you narrow down the field result. Let's say their are 5 different category_Name how can I select and display only 1 of those 5 and then make it sortable?

    ReplyDelete
    Replies
    1. This post is not cover your requirements.
      It is only sorting column values by clicking column header like Phpmyadmin

      Delete
  4. pls tell which one is table1.php?
    didnt get this pls tell?asap

    ReplyDelete
    Replies
    1. Just created php file with above reference and save this "table1.php".

      Delete
  5. The sort part does not work well. Everytime the page is reloaded $sort will be ASC. How can I prevent that.

    ReplyDelete
    Replies
    1. When the first time the page is loaded, $sort value will be ASC. After that it will be changed based on your clicks at header. You can see these lines
      $sort='ASC';
      if(isset($_GET['sorting']))
      {
      if($_GET['sorting']=='ASC')
      {
      $sort='DESC';
      }
      else
      {
      $sort='ASC';
      }
      }

      You can also see the changes on address bar.

      Delete
  6. Thanks for your reply. I tried it that way first. My idea is that the line in the example Game Id reloads the page. So I think the value of $sort will be SET again to 'ASC'.
    I don't see any changes on the addressbar. It always shows ASC.

    ReplyDelete
    Replies
    1. It didn't take as you expect. Because i checked the condition whether the $_GET['sorting'] is coming or not. Just you can see below codes.

      if(isset($_GET['sorting']))
      {
      if($_GET['sorting']=='ASC')
      {
      $sort='DESC';
      }
      else
      {
      $sort='ASC';
      }

      Delete
  7. a href="table1.php?sorting='.$sort.'&field=game_ID" was the line I wanted to write. Not the word Game Id.

    ReplyDelete
    Replies
    1. Thats correct..
      Please see line
      Game Id

      You can't assign word 'Game id'. Because you can't create field with space in mysql. You need to add '-' or '_' for connect words.

      Delete
  8. No, I know. I only tried to write the line of your example. I added a line 'echo $sort;' before and after the code of your first reply.
    echo $sort;

    if(isset($_GET['sorting']))
    {
    if($_GET['sorting']=='ASC')
    {
    $sort='DESC';
    }
    else
    {
    $sort='ASC';
    }

    echo $sort;

    The first time the reply is ASC ASC
    After clicking on the header the reply is ASC DESC

    The addressbar still does not change

    I am sorry to say so.

    ReplyDelete
  9. Excuse me, I made a terrible mistake. I inserted the if(isset($_GET['sorting'])) code AFTER the code to display the header. Not before. Shame on me. I changed it and now it works. Thanks for your patience.

    ReplyDelete
  10. why are the little arrows not showing?

    ReplyDelete
    Replies
    1. I mean ... how can i make em displays

      Delete
  11. But, I guess this sort method to be slow for long data sets.

    ReplyDelete
  12. Hi thanks for your articel,
    But i have problem, my table cant sort DESC.

    When I click the header, he always sort the table ASC.
    so i cant sort it DESC

    Any idea ??

    ReplyDelete
    Replies
    1. yup i have set $sort = DESC; And this is my code

      $sort='DESC';
      echo $sort;
      if(isset($_GET['sorting']))
      {
      if($_GET['sorting']=='ASC')
      {
      $sort='DESC';
      }
      else
      {
      $sort='ASC';
      }
      }

      < my code >

      according to me, $sort catch this value
      else
      {
      $sort = 'ASC';
      }

      do you have any idea?

      Delete
    2. hi men,
      i have wrong code at:
      'NIK'

      the right code is:
      NIK

      without '

      and now it works perfectly thanks guru

      Delete
  13. HI,

    sorting is working fine but i got this error "Undefined index: field" when i rain the code.
    do you know why?

    ReplyDelete
    Replies
    1. Add this code at top of your code snippet.

      error_reporting('E_ALL ^ E_NOTICE');

      Delete
    2. it works. Thanks

      Delete
    3. do you know how i can add below styles to php table?

      a:link {color: white;} /* unvisited link */
      a:visited {color: white;} /* visited link */
      a:hover {color: white;} /* mouse over link */
      a:active {color: white;} /* selected link */
      a:link {text-decoration: none;}

      a:visited {text-decoration: none;}

      a:hover {text-decoration: underline;}

      a:active {text-decoration: underline;}

      table.tableizer-table {
      border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif;
      font-size: 12px;
      }
      .tableizer-table td {
      padding: 4px;
      margin: 3px;
      border: 1px solid #ccc;
      }
      .tableizer-table th {
      background-color: #104E8B;
      color: #FFF;
      font-weight: bold;
      }

      Delete
  14. I want to add below style php table. How you can do that?

    a:link {color: white;} /* unvisited link */
    a:visited {color: white;} /* visited link */
    a:hover {color: white;} /* mouse over link */
    a:active {color: white;} /* selected link */
    a:link {text-decoration: none;}

    a:visited {text-decoration: none;}

    a:hover {text-decoration: underline;}

    a:active {text-decoration: underline;}

    table.tableizer-table {
    border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif;
    font-size: 12px;
    }
    .tableizer-table td {
    padding: 4px;
    margin: 3px;
    border: 1px solid #ccc;
    }
    .tableizer-table th {
    background-color: #104E8B;
    color: #FFF;
    font-weight: bold;
    }

    ReplyDelete
  15. <html>

    <head>

      <style type="text/css">

       Add above style here..

      </style>

    </head>

    <body>

      Add above PHP code here  and add class to table

    </body>

    </html>

    ReplyDelete
  16. i have written the same code as you given above but it cannot sort the table plz correct my errors from below codes. Thanks in advance.

    $select_query=mysqli_query($DB_Connect,"SELECT * FROM users ORDER BY $field $sort");

    ID
    First Name

    if(isset($_GET['sorting']))
    {
    if($_GET['sorting']=='ASC')
    {
    $sort='DESC';
    }
    else
    {
    $sort='ASC';
    }
    }

    if(isset($_GET['field'])=='ID')
    {
    $field="ID";
    }
    elseif(isset($_GET['field'])=='First_Name')
    {
    $field="First_Name";
    }

    ReplyDelete
  17. This comment has been removed by the author.

    ReplyDelete
  18. This comment has been removed by the author.

    ReplyDelete
  19. This comment has been removed by the author.

    ReplyDelete
  20. This comment has been removed by the author.

    ReplyDelete
  21. This comment has been removed by the author.

    ReplyDelete
  22. This comment has been removed by the author.

    ReplyDelete
  23. This comment has been removed by the author.

    ReplyDelete
  24. This comment has been removed by the author.

    ReplyDelete
  25. This comment has been removed by the author.

    ReplyDelete
  26. if(isset($_GET['sirala']) && isset($_GET['sorting'])) {
    if ($_GET['sirala'] == 'uye_adi') {
    $_SESSION["sirala"] = "uye_adi";
    } elseif ($_GET['sirala'] == 'tarih') {
    $_SESSION["sirala"] = "tarih";
    }
    $field = $_SESSION["sirala"];

    if ($_GET['sorting'] == 'ASC') {
    $_SESSION["sort"] = "DESC";
    } elseif($_GET['sorting'] == 'DESC') {
    $_SESSION["sort"] = "ASC";
    }
    $sort = $_SESSION["sort"];
    }

    if(isset($_SESSION["sirala"])) {
    $field = $_SESSION["sirala"];
    }
    if(isset($_SESSION['sort'])) {
    $sort = $_SESSION["sort"];
    }

    ReplyDelete
  27. Good work sir and it is so helpful for me you done a good job.

    ReplyDelete
  28. thanks sir for your great work.

    ReplyDelete
  29. Notice: Undefined index: field in C:\xampp7.3\htdocs\abc\sort.php on line 16

    ReplyDelete
  30. You make so many great points here that I read your article a couple of times. Your views are in accordance with my own for the most part. This is great content for your readers. 増田裕介

    ReplyDelete