Page 1 of 1

Sort by column heading

Posted: Tue Aug 11, 2009 8:57 am
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?

Re: SORT BY COLUMN HEADING

Posted: Tue Aug 11, 2009 9:17 am
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.

Re: SORT BY COLUMN HEADING

Posted: Tue Aug 11, 2009 9:36 am
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>";

Re: SORT BY COLUMN HEADING

Posted: Tue Aug 11, 2009 9:49 am
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().

Re: SORT BY COLUMN HEADING

Posted: Tue Aug 11, 2009 1:47 pm
by Benjamin
:arrow: Moved to PHP - Code

Re: Sort by column heading

Posted: Tue Aug 11, 2009 4:55 pm
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).