Jump to content


Photo

Order by column header


  • Please log in to reply
2 replies to this topic

#1 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 14 September 2006 - 03:54 PM

Does anyone know if there's a tutorial which shows you how to 'order by' a particular column?

I'm going to have my data displayed in a table like this:







IDCountry
1England
2Ireland
3Scotland
4Wales


I want the user to be able to click on the column heading and it will sort it oposite to how it's currently sorting it, similar to this page.  If you click on the price heading it sorts it either ascending or descending, based on how it's currently displayed.

I've been messing about and haven't really come close.  I'll post some code examples shortly.

Regards
Huggie
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#2 Orio

Orio
  • Staff Alumni
  • Advanced Member
  • 2,491 posts

Posted 14 September 2006 - 03:56 PM

I am not sure if I get your point, but I think what you are looking for is:
ORDER BY column ASC and ORDER BY column DESC

Orio.
Think you're smarty?

(Gone until 20 to November)

#3 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 14 September 2006 - 04:02 PM

Here's what I have that works, but I wondered if there's an easier way?

<?php
   // Connect to my db
   include('connect.php');
   
   // Get url params for the 'order by' if they're set
   if (isset($_GET['orderby']) && isset($_GET['orderdir'])){
	   $orderby = $_GET['orderby'];
	   $orderdir = $_GET['orderdir'];
   }

   // Select all my records
   $sql = "SELECT * FROM tblCountry";
   
   // If 'order by' was set then concatenate the string
   if ($orderby){
	   $sql .= " ORDER BY $orderby $orderdir";
   }

   // Execute the SQL result
   $result = mysql_query($sql);
   if (!result){
      echo "There was a problem selecting the countries" . mysql_error();
   }
   
   // Decide what the last link pressed was and then change the next link accordingly
   $orderdir = ($orderdir == "desc") ? "asc" : "desc";

   // Here's where I output my column headings with the links
   echo <<<HTML
<table width="300">
 <tr>
  <td bgcolor="#000000">
   <table width="300" cellpadding="2" cellspacing="1" border="0">
    <tr bgcolor="#FFFFFF">
     <td width="75" align="center"><a href="datatab.php?orderby=countryID&orderdir=$orderdir">ID</a></td>
     <td width="225" align="center"><a href="datatab.php?orderby=countryName&orderdir=$orderdir">Country</td>
    </tr>

HTML;

   while($row = mysql_fetch_array($result, MYSQL_ASSOC)){
      $id = $row['countryID'];
      $country = $row['countryName'];
      echo <<<HTML
    <tr bgcolor="#FFFFFF">
     <td width="75" align="center">$id</td>
     <td width="225" align="left">$country</td>
    </tr>

HTML;
}
   echo <<<HTML
   </table>
  </td>
 </tr>
</table>
HTML;
?>

You can see what it looks like here: Dizzie.Co.Uk

Regards
Huggie
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users