How to sort rows of HTML table that are called from MySQL

Question

I know it s such a basic thing  but a Google search hasn t shown me how to re-sort the rows after clicking the th links   I ve got this    lt table border  1  gt     lt tr gt       lt th gt Type  lt  th gt       lt th gt Description  lt  th gt       lt th gt Recorded Date  lt  th gt       lt th gt Added Date  lt  th gt     lt  tr gt    lt  php  while  row   mysql fetch array  result          gt       lt tr gt           lt td gt  lt  php echo  row  type     gt  lt  td gt           lt td gt  lt  php echo  row  description     gt  lt  td gt           lt td gt  lt  php echo  row  recorded date     gt  lt  td gt           lt td gt  lt  php echo  row  added date     gt  lt  td gt       lt  tr gt       lt br   gt        lt  php    mysql close      gt   lt  table gt    I need to be able to click type and sort alphabetically  and click on either Recorded Date or Added Date and sort by date   I see that I need to have the MySQL queries do this  but do I set them up as conditionals with a href tags

User · Accepted Answer

The easiest way to do this would be to put a link on your column headers, pointing to the same page. In the query string, put a variable so that you know what they clicked on, and then use ORDER BY in your SQL query to perform the ordering.

The HTML would look like this:

<th><a href="mypage.php?sort=type">Type:</a></th>
<th><a href="mypage.php?sort=desc">Description:</a></th>
<th><a href="mypage.php?sort=recorded">Recorded Date:</a></th>
<th><a href="mypage.php?sort=added">Added Date:</a></th>

And in the php code, do something like this:

<?php

$sql = "SELECT * FROM MyTable";

if ($_GET['sort'] == 'type')
{
    $sql .= " ORDER BY type";
}
elseif ($_GET['sort'] == 'desc')
{
    $sql .= " ORDER BY Description";
}
elseif ($_GET['sort'] == 'recorded')
{
    $sql .= " ORDER BY DateRecorded";
}
elseif($_GET['sort'] == 'added')
{
    $sql .= " ORDER BY DateAdded";
}

$>

Notice that you shouldn't take the $_GET value directly and append it to your query. As some user could got to MyPage.php?sort=; DELETE FROM MyTable;

User · Answer

A SIMPLE TABLE SORT PHP CODE    the simple table for several values processing and sorting  using this sortable js script     lt html gt  lt head gt   lt script src  sorttable js  gt  lt  script gt    lt style gt  tbody tr td  color green border-right 1px solid width 200px    lt  style gt   lt  head gt  lt body gt    lt  php  First   array  a    b    c    d     Second   array  1    2    3    4     if   empty   POST  myFirstvalues         First   explode   r n    POST  myFirstvalues      Second   explode   r n    POST  mySecondvalues         gt    lt  br gt Hi User  PUT your values lt  br gt  lt  br gt   lt form action    method  POST  gt  projectX lt  br gt   lt textarea cols  20  rows  20  name  myFirstvalues  style  width 200px background url untitled PNG  position relative top 19px Float left   gt   lt  php foreach  First as  vv   echo  vv   r n     gt   lt  textarea gt   The due amount lt  br gt   lt textarea cols  20  rows  20  name  mySecondvalues  style  width 200px background url untitled PNG  Float left   gt   lt  php foreach  Second as  vv   echo  vv   r n     gt   lt  textarea gt   lt input type  submit  gt   lt  form gt    lt table class  sortable  style  padding 100px 0 0 300px   gt   lt thead style  background-color  999999  color red  font-weight  bold  cursor  default   position relative   gt     lt tr gt  lt th gt ProjectX lt  th gt  lt th gt Due amount lt  th gt  lt  tr gt   lt  thead gt   lt tbody gt    lt  php foreach  First as  indx   gt   value        echo   lt tr gt  lt td gt    First  indx    lt  td gt  lt td gt    Second  indx    lt  td gt  lt  tr gt        gt   lt  tbody gt   lt tfoot gt  lt tr gt  lt td gt TOTAL     amp nbsp  lt b gt 111111111 lt  b gt  lt  td gt  lt td gt Still to spend     amp nbsp  lt b gt 5555555 lt  b gt  lt  td gt  lt  tr gt  lt  tfoot gt  lt  br gt  lt  br gt   lt  table gt   lt  body gt   lt  html gt    source  php sortable table

User · Answer

this is a php file   lt html gt   lt head gt   lt style gt  a link  color green   a visited  color purple   A active  color  red   A hover  color  red   table       width 50       height 50     table th td       border 1px solid black    th td       text-align center        background-color yellow    th       background-color green      color white         lt  style gt   lt script type  text javascript  gt  function working str    if  str            document getElementById  tump   innerHTML       return       if  window XMLHttpRequest        code for IE7   Firefox  Chrome  Opera  Safari   xmlhttp new XMLHttpRequest        else       code for IE6  IE5   xmlhttp new ActiveXObject  Microsoft XMLHTTP        xmlhttp onreadystatechange function         if  xmlhttp readyState  4  amp  amp  xmlhttp status  200            document getElementById  tump   innerHTML xmlhttp responseText            xmlhttp open  GET   getsort php q   str true   xmlhttp send       lt  script gt   lt  head gt   lt body bgcolor  pink  gt   lt form method  post  gt   lt select name  sortitems  onchange  working this value   gt   lt option value    gt Select lt  option gt   lt option value  Id  gt Id lt  option gt   lt option value  Name  gt Name lt  option gt   lt option value  Email  gt Email lt  option gt   lt option value  Password  gt Password lt  option gt   lt  select gt   lt  php   connect mysql connect  localhost   root        db mysql select db  test1   connect    sql mysql query  select   from mine    echo   lt center gt  lt br gt  lt br gt  lt br gt  lt br gt  lt table id  tump  border  1  gt   lt tr gt   lt th gt Id lt  th gt   lt th gt Name lt  th gt   lt th gt Email lt  th gt   lt th gt Password lt  th gt   lt  tr gt    echo   lt tr gt    while   row mysql fetch array  sql      gt   lt td gt  lt  php echo   row Id     gt  lt  td gt   lt td gt  lt  php echo   row Name     gt  lt  td gt   lt td gt  lt  php echo   row Email     gt  lt  td gt   lt td gt  lt  php echo   row Password     gt  lt  td gt   lt  php echo   lt  tr gt      echo   lt  table gt  lt  center gt     gt   lt  form gt   lt br gt   lt div id  tump  gt  lt  div gt   lt  body gt   lt  html gt  ------------------------------------------------------------------------ that is another php file   lt html gt   lt body bgcolor  pink  gt   lt head gt   lt style gt  a link  color green   a visited  color purple   A active  color  red   A hover  color  red   table       width 50       height 50     table th td       border 1px solid black    th td       text-align center        background-color yellow    th       background-color green      color white         lt  style gt   lt  head gt   lt  php  q   GET  q     connect mysql connect  localhost   root        db mysql select db  test1   connect    sql mysql query  select   from mine order by  q    echo   lt table id  tump  border  1  gt   lt tr gt   lt th gt Id lt  th gt   lt th gt Name lt  th gt   lt th gt Email lt  th gt   lt th gt Password lt  th gt   lt  tr gt    echo   lt tr gt    while   row mysql fetch array  sql      gt   lt td gt  lt  php echo   row Id     gt  lt  td gt   lt td gt  lt  php echo   row Name     gt  lt  td gt   lt td gt  lt  php echo   row Email     gt  lt  td gt   lt td gt  lt  php echo   row Password     gt  lt  td gt   lt  php echo   lt  tr gt      echo   lt  table gt     gt   lt  body gt   lt  html gt     that will sort the table using ajax

User · Answer

That s actually pretty easy  here s a possible approach    lt table gt       lt tr gt           lt th gt               lt a href   orderBy type  gt Type  lt  a gt           lt  th gt           lt th gt               lt a href   orderBy description  gt Description  lt  a gt           lt  th gt           lt th gt               lt a href   orderBy recorded date  gt Recorded Date  lt  a gt           lt  th gt           lt th gt               lt a href   orderBy added date  gt Added Date  lt  a gt           lt  th gt       lt  tr gt   lt  table gt   lt  php  orderBy   array  type    description    recorded date    added date      order    type   if  isset   GET  orderBy     amp  amp  in array   GET  orderBy     orderBy          order     GET  orderBy        query    SELECT   FROM aTable ORDER BY    order      retrieve and show the data      gt    That ll do the trick

User · Answer

This is the most simple solution that  use      Use this as first line upon load of page   sort     GET  s         Then simply sort according to that variable   sql  SELECT   FROM tracks ORDER BY  sort    echo   lt tr gt    echo   lt td gt  lt a href  report tracks php s title  gt Title lt  a gt  lt td gt    echo   lt td gt  lt a href  report tracks php s album  gt Album lt  a gt  lt td gt    echo   lt td gt  lt a href  report tracks php s artist  gt Artist lt  a gt  lt td gt    echo   lt td gt  lt a href  report tracks php s count  gt Count lt  a gt  lt td gt    echo   lt  tr gt

User · Answer

It depends on nature of your data  The answer varies based on its size and data type  I saw a lot of SQL solutions based on ORDER BY  I would like to suggest javascript alternatives   In all answers  I don t see anyone mentioning pagination problem for your future table  Let s make it easier for you  If your table doesn t have pagination  it s more likely that a javascript solution makes everything neat and clean for you on the client side  If you think this table will explode after you put data in it  you have to think about pagination as well   you have to go to first page every time when you change the sorting column   Another aspect is the data type  If you use SQL you have to be careful about the type of your data and what kind of sorting suites for it  For example  if in one of your VARCHAR columns you store integer numbers  the sorting will not take their integer value into account  instead of 1  2  11  22 you will get 1  11  2  22   You can find jquery plugins or standalone javascript sortable tables on google  It worth mentioning that the  lt table gt  in HTML5 has sortable attribute  but apparently it s not implemented yet

[php] How to sort rows of HTML table that are called from MySQL

Examples related to php

Examples related to mysql

Examples related to html-table