SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Addict Bravogolf's Avatar
    Join Date
    Aug 2004
    Location
    All over really :)
    Posts
    205
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Sorting per table header

    It's probably something so basic but I can't seem to find out how!

    I'm running a select statement on my table with PHP to a MySQL database.
    It brings back everything but I want to be able to click on that the table item and sort it.

    So if I click the name column, re-arrange all the data returned but sort via column name

    Here is my code for running a select all statement:
    Code:
    	$query="SELECT * FROM leads";
    	$result=mysql_query($query);
    	$colourcount=1;
    
    	echo "<table class='ex' cellpadding='4' cellspacing='0'>\n";
    	echo "<tr><td class='header' width='7' align='center'>Sales ID</td><td class='header'>Customer Name</td><td class='header'>Address</td><td class='header'>Contact No</td><td class='header'>Status</td><td class='header'>Description</td><td class='header'>Agent</td></tr>\n";
    	while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) 
    	{
    			$SalesID=$line['SalesID'];
    		   if($colourcount==1)
    		   {		
    					echo "<tr class='tdex' onmouseover='HighlightBackground(this);' onmouseout='ResetBackgroundtdex(this);;'"?> onClick="MyWindow=window.open('perparameterview.php?ID=<? echo "$SalesID"; ?>','_parent');"><? echo "\n";
    					$colourcount=0; 
    			}
    			else
    			{
    					echo "<tr class='tdex1' onmouseover='HighlightBackground(this);' onmouseout='ResetBackgroundtdex1(this);'"?> onClick="MyWindow=window.open('perparameterview.php?ID=<? echo "$SalesID"; ?>','_parent');"><? echo "\n";
    					$colourcount=1;
    			}
    			
    		   foreach ($line as $col_value) 
    		   {
    					echo "<td>$col_value</td>\n";
    		   }
    		   echo "</tr>\n";
    	}
    	echo "</table>\n";

  2. #2
    SitePoint Evangelist nsj's Avatar
    Join Date
    Oct 2005
    Location
    Jamaica (W.I)
    Posts
    447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Redirect the page and pass the sql field column name as a header parameter.

    for example:
    to sort by sales ID,

    ...
    echo "
    <tr>
    <td class='header' width='7' align='center'>
    <a href='?order=SalesID'>Sales ID</a>
    </td>
    ";
    ...

    Then in your query where you have
    $query="SELECT * FROM leads";
    $result=mysql_query($query);

    say:
    $order = $_GET['order'];

    $query = "SELECT * FROM leads ORDER BY $order";
    $result=mysql_query($query) or die("Invalid order field");

    You can use the same A href statement above for all fields specifying different field names that correspond to each one.

  3. #3
    SitePoint Addict Bravogolf's Avatar
    Join Date
    Aug 2004
    Location
    All over really :)
    Posts
    205
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you very much!

    I stuck in an if loop to order by a default value too in the even that none of the table headers are clicked

    Thanks nsj :-D

  4. #4
    SitePoint Evangelist nsj's Avatar
    Join Date
    Oct 2005
    Location
    Jamaica (W.I)
    Posts
    447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah that's good. Sorry i forgot to mention that.
    I usually default it to sort by ID or descending post date or whatever.
    which reminds me...
    You might want to play around with the sort ASC and DESC mysql feature so that users can click on the heading again to sort in the reverse order.
    Just a thought.

    Cheers.

  5. #5
    SitePoint Addict Bravogolf's Avatar
    Join Date
    Aug 2004
    Location
    All over really :)
    Posts
    205
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah, was wondering about that.

    Will look that up, thanks.

  6. #6
    SitePoint Addict Bravogolf's Avatar
    Join Date
    Aug 2004
    Location
    All over really :)
    Posts
    205
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmmm, this is wee bit harder to do! I've got the code to order by but how do I get it to only reverse the order mode if it's clicked a second time?

    Code:
    <?php function showall()
    	{
    	//$order="SalesID";
    	$order = $_GET['order'];
    	if($order == NULL)
    	{
    		$order="SalesID";
    	}
    	$query="SELECT * FROM leads ORDER by $order";
    	$result=mysql_query($query) or die("Invalid order field");
    	$colourcount=1;
    
    	echo "<table class='ex' cellpadding='4' cellspacing='0'>\n";
    	echo "<tr><td class='header' width='7' align='center'"?> onClick="MyWindow=window.open('reglist.php?order=SalesID','_parent');"><? echo "Sales ID</td><td class='header'"?> onClick="MyWindow=window.open('reglist.php?order=CustName','_parent');"><? echo "Customer Name</td><td class='header'"?> onClick="MyWindow=window.open('reglist.php?order=CustAddress','_parent');"><? echo "Address</td><td class='header'"?> onClick="MyWindow=window.open('reglist.php?order=CustTel','_parent');"><? echo "Contact No</td><td class='header'"?> onClick="MyWindow=window.open('reglist.php?order=CustStatus','_parent');"><? echo "Status</td><td class='header'"?> onClick="MyWindow=window.open('reglist.php?order=CustDescription','_parent');"><? echo "Description</td><td class='header'"?> onClick="MyWindow=window.open('reglist.php?order=AgentAssigned','_parent');"><? echo "Agent</td></tr>\n";
    	while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) 
    	{
    			$SalesID=$line['SalesID'];
    		   if($colourcount==1)
    		   {		
    					echo "<tr class='tdex' onmouseover='HighlightBackground(this);' onmouseout='ResetBackgroundtdex(this);;'"?> onClick="MyWindow=window.open('perparameterview.php?ID=<? echo "$SalesID"; ?>','_parent');"><? echo "\n";
    					$colourcount=0; 
    			}
    			else
    			{
    					echo "<tr class='tdex1' onmouseover='HighlightBackground(this);' onmouseout='ResetBackgroundtdex1(this);'"?> onClick="MyWindow=window.open('perparameterview.php?ID=<? echo "$SalesID"; ?>','_parent');"><? echo "\n";
    					$colourcount=1;
    			}
    			
    		   foreach ($line as $col_value) 
    		   {
    					echo "<td>$col_value</td>\n";
    		   }
    		   echo "</tr>\n";
    	}
    	echo "</table>\n";
    
    	}
    
    ?>

  7. #7
    SitePoint Evangelist nsj's Avatar
    Join Date
    Oct 2005
    Location
    Jamaica (W.I)
    Posts
    447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:

    $orderby = $_GET['o'];
    $dir=$_GET['d'];
    if(!isset($orderby)){
        $orderby = "DefOrderField";
    }

    if(!isset($dir)){
        $dir = "DESC";
    }
    if(strcmp($dir,"ASC")!=0 and strcmp($dir,"DESC")!=0){
        $dir = "DESC";
    }

    $query = "SELECT * FROM TABLE ORDER BY $orderby $dir";
    $result=mysql_db_query ($db,$query)or die (mysql_error());
    if(strcmp($dir,"ASC")==0){
        $ddir = "DESC";
    }else{
        $ddir = "ASC";
    }

    ............
    # In your table
    <td align="center">
         <a href="?o=FieldName&d=<?=$ddir?><strong>ID</strong></a>
    </td>
    This above code would alternate it. You could also use a triangle and an upside down triangle image to show the users in which direction they are sorting.
    Let me know if you need help as to where to add this code!

  8. #8
    SitePoint Addict Bravogolf's Avatar
    Join Date
    Aug 2004
    Location
    All over really :)
    Posts
    205
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks very much for your help, nsj, I got the above working great

    I hate to push it but how do I insert code to show the arrows depending on the sort descending or ascending? I have a general idea, I think, but can't get it to work.

    Here's my code

    PHP Code:
        $orderby = $_GET['o']; 
        

        
        $dir=$_GET['d']; 
        if(!isset($orderby)){ 
            $orderby = "SalesID"; 
        } 
        
        if(!isset($dir)){ 
            $dir = "ASC"; 
        } 
        if(strcmp($dir,"ASC")!=0 and strcmp($dir,"DESC")!=0){ 
            $dir = "DESC"; 
        } 
        
        if($orderby == NULL)
        {
            $order="SalesID";
        }
        $query = "SELECT * FROM leads ORDER BY $orderby $dir"; 
        $result=mysql_query ($query) or die ("Oh oh"); //(mysql_error()); 
        if(strcmp($dir,"ASC")==0){ 
            $ddir = "DESC"; 
        }else{ 
            $ddir = "ASC"; 
        }
        
        $colourcount=1;

        echo "<table class='ex' cellpadding='4' cellspacing='0'>\n";
        echo "<tr><td class='header' width='7' align='center'"?> onClick="MyWindow=window.open('reglist.php?o=SalesID&d=<?=$ddir?>','_parent');"><? echo "ID"showarrow(SalesID); echo" </td></tr>\n";

    ....

    function 
    showarrow($FieldID)
        {
            if(
    $orderby=$FieldID)
            {
                if(
    $dir='ASC')
                {
                    echo 
    "<img src='images/firstnew.gif'>";
                }
                else
                {
                    echo 
    "<img src='images/firstnewup.gif'>";
                }
            }
    }

  9. #9
    SitePoint Evangelist nsj's Avatar
    Join Date
    Oct 2005
    Location
    Jamaica (W.I)
    Posts
    447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    After "uh Oh" where you have this,
    if(strcmp($dir,"ASC")==0){
    $ddir = "DESC";
    }else{
    $ddir = "ASC";
    }

    alter it to the following:
    PHP Code:
    if(strcmp($dir,"ASC")==0){
            
    $ddir "DESC";
            
    $src="down";
        }else{
            
    $ddir "ASC";
            
    $src="up";
        } 

    Then in your table,
    PHP Code:
    <td align="center">
         <a href="?o=FieldName&d=<?=$ddir?>
                 <strong>ID</strong>
                 <img src="/images/arrow_<?=$src?>.gif">    # or whatever.
         </a>
    </td>
    This assumes that you named your images arrow_up.gif and arrow_down.gif.
    If they're something else, store and retrieve their full names in $src.


Bookmarks

Posting Permissions

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