Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Aug 2002
    Location
    Malaysia
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sorting table columns ascending and descending

    Hi, I have this table generated with values from the database. By default, the data will be sorted ascendingly according to first column. I want the data to be sorted according to any column, that would mean whenever I click on the title of any column, it would sort descendingly, then if I click at the same column again, it will revert back to ascending sort, and so on.

    Well, I can make them work only once, i.e. descendingly, after that I can't revert the sorting back to ascending. Can anyone help me on this? I hope I don't have to use any JavaScript on this...

    Thanks.


    PHP Code:
    <?PHP
    // Get the name of the column to sort
    $columnname "";
    if (
    $_GET['columnname']) {
    $columnname $_GET['columnname'];
    }

    // Get the value of either ascending or descending
    $order "";
    if (
    $_GET['order']) {
    $order $_GET['order'];
    }

    // Display 20 records per page.
    $display_number 20;

    // Connect to the database.
    $db_connection mysql_connect ('localhost''root''123456') or die (mysql_error());
    $db_select mysql_select_db('proposal');

    // Make the calculation to see how many pages are there.
    if (!isset($num_pages)) {
        
        
    $query1 "SELECT * FROM proposals";
        
        
    // Query the database.
        
    $query_result1 mysql_query ($query1) or die (mysql_error());
        
        
    // Calculate the number of pages required.
        
    $num_results = @mysql_num_rows ($query_result1);
        
        if (
    $num_results $display_number) {
            
            
    $num_pages ceil ($num_results/$display_number);
        
        } elseif (
    $num_results 0) {
            
            
    $num_pages 1;
        
        } else {
            
            echo 
    '<font color="#006699" size="2" face="Verdana, Arial, Helvetica, sans-serif"><b>There are no proposals at all.</b></font>';
        
        }
        
        
    // Currently at item 0.
        
    $start 0;

    }

    // Make the default column name
    if ($columnname == "")
    {
    $columnname "proposalno"; }

    // Make the default order sorting
    if ($order == "") {
    $order "ASC"; }
    elseif (
    $order == "ASC") {
    $order "DESC";}
    elseif (
    $order == "DESC") {
    $order "ASC";}

    // Make the new, limited query.
    $query "SELECT * FROM proposals ORDER BY $columnname $order LIMIT $start, $display_number";

    // Print a table.
    echo '<br><table width="760" align="center" cellpadding="0" cellspacing="0" border="1" bordercolor="#3399CC">
    <tr align="center" bgcolor="#006699">

    <td width="100">
    <div align="center">
    <font color="#FFFFFF" size="2" face="Verdana, Arial, Helvetica, sans-serif"><b>Proposal No</b></font>
    </div>
    </td>

    // Here is the column that I tested...
    <td width="260">
    <div align="center">
    <font color="#FFFFFF" size="2" face="Verdana, Arial, Helvetica, sans-serif"><b><a href="proposal-basicview.php?columnname=clientsname&order=ASC">Client Name</a></b></font>
    </div>
    </td>

    <td width="400">
    <div align="center">
    <font color="#FFFFFF" size="2" face="Verdana, Arial, Helvetica, sans-serif"><b>Project Title</b></font>
    </div>
    </td>

    </tr>'
    ;

    // Print each item.

    $query_result = @mysql_query ($query);

    while (
    $row = @mysql_fetch_array ($query_result)) {
        
        echo 
    "    <tr align=\"center\" bgcolor=\"$row[colourcode]\">        
        <td align=\"left\"><font size=\"2\" face=\"Verdana, Arial, Helvetica, sans-serif\" color=\"#006699\">&nbsp$row[proposalno]</font></td>
        <td align=\"left\"><font size=\"2\" face=\"Verdana, Arial, Helvetica, sans-serif\" color=\"#006699\">&nbsp$row[clientsname]</font></td>
        <td align=\"left\"><font size=\"2\" face=\"Verdana, Arial, Helvetica, sans-serif\" color=\"#006699\">&nbsp$row[projecttitle]</font></td>
        </tr>"
    ;

    }
    ...
    ?>

  • #2
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    If you want to do this in PHP, then you need to post the page to itself, with the sorting order in the querystring. Like
    www.thesite.com/thepage.php?sort=asc
    Then inside the page, you grab the querystringvalue and inject it in the SQL statement.
    Like
    PHP Code:
    $order="asc"// the defaultorder
    $order=$_GET['sort'// default will be replaced if there was a value in the querystring
    ... 
    Then you display a link to sort them differently. Like
    PHP Code:
    if ($order=="asc") {
      
    $orderlink = ("<a href=\"" $_SERVER["PHP_SELF"] . "?sort=desc\" title=\"Sort the records in descending order\">Sort descending</a>") ;
    } else {
      
    $orderlink = ("<a href=\"" $_SERVER["PHP_SELF"] . "?sort=asc\" title=\"Sort the records in ascending order\">Sort ascending</a>") ;
    }
    echo 
    $orderlink 
    But i see you use a limit clause, so you will get different records, cause the records are first sorted, and then the limit-clause is executed to build the recordset ...

  • #3
    New Coder
    Join Date
    Aug 2002
    Location
    Malaysia
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Reply

    Hi Raf, thanks for guidance. It seems what you showed here is almost the same concept as mine, i.e. post to the page itself and inject a querystring to the SQL statement.

    Looks like I would have to go for the conventional method, e.g. place two images (one up arrow and one down arrow) to represent the ASC and DESC respectively. So when I click on either one of them, they will inject the "sort" string to the SQL.

    I have my initial thoughts of using just one link to achieve that. I encountered this in phpMyAdmin, exactly what I want but I simply can't decipher how they code that. Their code is just too complex for me

  • #4
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The difference between my code and yours is that you need to check if there is a sort on that column before you build and display the link. In your code, it's always ascending, no ?

    But now that i reread it, i see that you want to have this sort option on each variable.

    So you need to do this dynamically, by indeed adding the variablename (columnname) also to the querystring and then, when you build the table, check if the variable name from the recordset is the same as the one from the querystring.
    PHP Code:
    //build a default header for both ascending and descending link
    $orderlink = ("<a href=\"" $_SERVER["PHP_SELF"] . "?columnname=proposalno&sort=desc\" title=\"Sort the records in descending order\">Sort descending</a>") ;
    $orderlink .= "&nbsp;&nbsp;" ;
    $orderlink .= ("<a href=\"" $_SERVER["PHP_SELF"] . "?columnname=proposalno&sort=asc\" title=\"Sort the records in ascending order\">Sort ascending</a>") ; 

    if 
    $_GET['columnname']=="proposalno" {
      if (
    $order=="asc") {
        
    $orderlink = ("<a href=\"" $_SERVER["PHP_SELF"] . "?columnname=proposalno&sort=desc\" title=\"Sort the records in descending order\">Sort descending</a>") ;  // default replaced by single link
      
    } else {
        
    $orderlink = ("<a href=\"" $_SERVER["PHP_SELF"] . "?columnname=proposalno&sort=asc\" title=\"Sort the records in ascending order\">Sort ascending</a>") ; 
      }
    }
    echo 
    $orderlink 
    But to make this realy generic (like it probably is in phpMyAdmin) you just need to get the columnname (with mysql_field_name() )and then replace all 'proposalno' by that fieldname.

    + you need to place the above code in a for-loop with an incrementing variable for the number of columns. It would look like
    PHP Code:
    $num_fieldsmysql_num_fields($query_result );
    if (
    $num_fields) { //build the order by row
           
    echo "<tr>" 
           for (
    $i 0$i $num_fields$i++) {
                
    $colname mysql_field_name($query_result $i) ;
                 
    //build a default header for both ascending and descending link
                
    $orderlink = ("<a href=\"" $_SERVER["PHP_SELF"] . "?columnname=" $colname  "&sort=desc\" title=\"Sort the records in descending order\">Sort descending</a>") ;
                
    $orderlink .= "&nbsp;&nbsp;" ;
                
    $orderlink .= ("<a href=\"" $_SERVER["PHP_SELF"] . "?columnname=" $colname "&sort=asc\" title=\"Sort the records in ascending order\">Sort ascending</a>") ; 

               if 
    $_GET['columnname']== $colname {
                  if (
    $order=="asc") {
                    
    $orderlink = ("<a href=\"" $_SERVER["PHP_SELF"] . "?columnname=" $colname ." &sort=desc\" title=\"Sort the records in descending order\">Sort descending</a>") ;  // default replaced by single link
                  
    } else {
                    
    $orderlink = ("<a href=\"" $_SERVER["PHP_SELF"] . "?columnname=" $colname "&sort=asc\" title=\"Sort the records in ascending order\">Sort ascending</a>") ; 
                  }
                }
                echo (
    "<td>" $orderlink "</td>" // place each link-couplein a new cell
           
    }
           echo 
    "</tr>" ;
        } else {
            echo 
    "Empty recordset" ;
    }
    //and then here comes your code to display the records from the recordset.
    while ($row = @mysql_fetch_array ($query_result)) {
       ... 
    It's untested code, but it should be debugable ...
    Last edited by raf; 10-13-2003 at 01:04 PM.

  • #5
    New Coder
    Join Date
    Aug 2002
    Location
    Malaysia
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thanks...

    Hi Raf, I will try them out. Thanks!


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •