Sort by column heading

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
phpwannabe
Forum Newbie
Posts: 2
Joined: Tue Aug 11, 2009 8:40 am

Sort by column heading

Post by phpwannabe »

Hi
i am pulling data from the database.
i would like it to appear so that the user can click on the column head on the page and it would sort the data by the selected data head...

here is the code so far... the connection has been made to the database, and at the moment, it only pulls the data up and sorts by hotelname...

Code: Select all

 
$result = mysql_query("SELECT * FROM hotelsignup order by hotelname");
 
echo "<table border='1'>
<tr>
<th>Hotel Name</th>
<th>Address1</th>
<th>Address2</th>
<th>County</th>
<th>Hotel Star</th>
</tr>";
 
while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['hotelname'] . "</td>";
  echo "<td>" . $row['address1'] . "</td>";
  echo "<td>" . $row['address2'] . "</td>";
  echo "<td>" . $row['county'] . "</td>";
  echo "<td>" . $row['starrating'] . "</td>";
 
 
  echo "</tr>";
  }
echo "</table>";
sort($row['hotelname']);
 
mysql_close($con);
 
any ideas?
Last edited by Benjamin on Tue Aug 11, 2009 1:47 pm, edited 2 times in total.
Reason: Added [code=php] tags.
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Re: SORT BY COLUMN HEADING

Post by jayshields »

There are many ways to accomplish this. One way is to change your column headings to something like this:

Code: Select all

<th><a href="thispage.php?sortby=hotelname">Hotel Name</a></th>
<th><a href="thispage.php?sortby=address1">Address 1</a></th> 
Then change your PHP to something like this:

Code: Select all

$query = "SELECT * FROM `hotelsignup`";
if(isset($_GET['sortby']))
  $query .= " ORDER BY `" . mysql_real_escape_string($_GET['sortby']) . "`";
Obviously this could be improved upon to incorporate ascending and descending orders amongst other things.
phpwannabe
Forum Newbie
Posts: 2
Joined: Tue Aug 11, 2009 8:40 am

Re: SORT BY COLUMN HEADING

Post by phpwannabe »

Thanks very much Jamie

This is what i've tried so. and seem to be havin problems with it, nothin is appearin on the page now, and i'm thinkin its a small error on my behalf....

Code: Select all

 
$result = mysql_query("SELECT * FROM hotelsignup");
if(isset($_GET['sortby']))
$query .= " ORDER BY '" . mysql_real_escape_string($_GET['sortby']) . "'";
 
echo "<table border='1'>
<tr>
<th><a href="thispage.php?sortby=hotelname">Hotel Name</a></th>
<th><a href="thispage.php?sortby=address1">Address 1</a></th>
<th><a href="thispage.php?sortby=address2">Address 2</a></th>
<th><a href="thispage.php?sortby=starrating">Star Rating</a></th>
</tr>";
 
 
 
while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
 <a href=" "> echo "<td>" . $row['hotelname'] . "</td>";</a>
  echo "<td>" . $row['address1'] . "</td>";
  echo "<td>" . $row['address2'] . "</td>";
  echo "<td>" . $row['county'] . "</td>";
  echo "<td>" . $row['starrating'] . "</td>";
 
 
 
  echo "</tr>";
  }
echo "</table>";
Last edited by Benjamin on Tue Aug 11, 2009 1:46 pm, edited 1 time in total.
Reason: Added [code=php] tags.
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Re: SORT BY COLUMN HEADING

Post by jayshields »

Change thispage.php to whatever your file is named, and concatenate the conditional bit of the query onto the $query variable before you execute it with mysql_query().
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: SORT BY COLUMN HEADING

Post by Benjamin »

:arrow: Moved to PHP - Code
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Re: Sort by column heading

Post by jayshields »

Now that astions has sorted out your code tags you can clearly see some quote conflicts. Either escape the troublesome quotes or use some concatenation (as well as the other things I mentioned).
Post Reply