1. Home
  2. Forums Index
  3. Server Side / PHP Server Side Scripting 10:43 am May 1, 2026

Forum Moderators: coopster

Message Too Old, No Replies

How to sort my table by any column by click on the column's header

I'm beginner to php database and i need your help for my thesis

         

ivyleong

9:31 am on Jan 3, 2009 (gmt 0)

10+ Year Member



Hi, i am doing a thesis to enhance a database under my University server. I need to change the static table after the search page to a dynamic one where users can view data by sorting any column they like by click on the column name. I have no idea on it, anyone can help me? Thanks.
The original coding is too complex and make me, a starter blur on it, hopefully you can understand it and please help me and tell me where should i correct it.. Thanks, cheers :)

<?
$cat=$_POST['cat'];
$subcat=$_POST['subcat'];
$subcat3=$_POST['subcat3'];
$quer="MF";
$quer2="MP";
$quer3="Others";

$query="SELECT * FROM `$subcat3`"; //MySQL query
$numresults = mysql_query ($query) or die ( "<center>Couldn't execute query<center>" );
//$row= mysql_fetch_array ($numresults);

if
($subcat== $quer)
{
echo "<center><h1> <i>" . $subcat3 ."</i> </center></h1>"; //display results in tabular format

echo " <table width=774 align=center height=69 border=1 bordercolor=#000000>
<tr bordercolor=1 bgcolor=#0099FF>
<td width=148><div align=center class=style7><strong><span class=style2>Sample_ID</span></strong></div></td>
<td width=148<div align=center class=style7><strong><span class=style2>Taxcode</span></strong></div></td>
<td width=148<div align=center class=style7><strong><span class=style2>SL</span></strong></div></td>
<td width=148<div align=center class=style7><strong><span class=style2>TL</span></strong></div></td>
<td width=148<div align=center class=style7><strong><span class=style2>BD</span></strong></div></td>
<td width=148><div align=center class=style7><strong><span class=style2>WT</span></strong></div></td>
<td width=148><div align=center class=style7><strong><span class=style2>Sex</span></strong></div></td>
<td width=148<div align=center class=style7><strong><span class=style2>Mat</span></strong></div></td>
<td width=148><div align=center class=style7><strong><span class=style2>Time</span></strong></div></td>
<td width=148<div align=center class=style7><strong><span class=style2>Gear</span></strong></div></td>
</tr>";

while ($row= mysql_fetch_array ($numresults)){ //loop until all records from the table are selected
echo "<tr bordercolor=1>";
echo "<td>". $row[ 'SAMPLE_ID' ] ."</td>";
echo "<td>". $row[ 'TAXCODE' ] ."</td>";
echo "<td>". $row[ 'SL' ] ."</td>";
echo "<td>". $row[ 'TL' ] ."</td>";
echo "<td>". $row[ 'BD' ] ."</td>";
echo "<td>". $row[ 'WT' ] ."</td>";
echo "<td>". $row[ 'SEX' ] ."</td>";
echo "<td>". $row[ 'MAT' ] ."</td>";
echo "<td>". $row[ 'TIME' ] ."</td>";
echo "<td>". $row[ 'GEAR' ] ."</td>";
echo "</tr>";}
echo "</table>";
echo "<center><h3>Click on the link to view as Excel file: <a href='excel/". $subcat3 .".xls'>". $subcat3 ."</a></h3>"; //Link to Excel file
echo "<center><input type=button value='Search another record' onClick=goToURL()></center>";}

if
($subcat== $quer2)
// If category selected from page "search3.php" is the same as the values given
{
echo "<center><h1> <i>" . $subcat3 ."</i> </center></h1>"; //display results in tabular format

echo " <table width=774 align=center height=69 border=1 bordercolor=#000000>
<tr bordercolor=1 bgcolor=#0099FF>
<td width=148><div align=center class=style7><strong><span class=style2>Sample_ID</span></strong></div></td>
<td width=148><div align=center class=style7><strong><span class=style2>Taxcode</span></strong></div></td>
<td width=148><div align=center class=style7><strong><span class=style2>NO</span></strong></div></td>
<td width=148<div align=center class=style7><strong><span class=style2>CL</span></strong></div></td>
<td width=148<div align=center class=style7><strong><span class=style2>WT</span></strong></div></td>
<td width=148<div align=center class=style7><strong><span class=style2>SEX</span></strong></div></td>
<td width=148<div align=center class=style7><strong><span class=style2>MAT(gm)</span></strong></div></td>
<td width=148><div align=center class=style7><strong><span class=style2>Gear</span></strong></div></td>
</tr>";

while ($row= mysql_fetch_array ($numresults)){ //loop until all records from the table are selected
echo "<tr bordercolor=1>";
echo "<td>". $row[ 'SAMPLE_ID' ] ."</td>";
echo "<td>". $row[ 'TAXCODE' ] ."</td>";
echo "<td>". $row[ 'NO' ] ."</td>";
echo "<td>". $row[ 'CL' ] ."</td>";
echo "<td>". $row[ 'WT' ] ."</td>";
echo "<td>". $row[ 'SEX' ] ."</td>";

echo "<td>". $row[ 'MAT' ] ."</td>";
echo "<td>". $row[ 'GEAR' ] ."</td>";
echo "</tr>";}
echo "</table>";
echo "<center><h3>Click on the link to view as Excel file: <a href='excel/". $subcat3 .".xls'>". $subcat3 ."</a></h3>"; //Link to Excel file
echo "<center><input type=button value='Search another record' onClick=goToURL()></center>";}

if
($subcat== $quer3)// If category selected from page "search3.php" is the same as the values given
{
echo "<center><h1><i>" . $subcat3 ."</i> </center></h1>"; //display results in tabular format

echo " <table width=774 align=center height=69 border=1 bordercolor=#000000>
<tr bordercolor=1 bgcolor=#0099FF>
<td width=148<div align=center class=style7><strong><span class=style2>Date</span></strong></div></td>
<td width=148><div align=center class=style7><strong><span class=style2>Month</span></strong></div></td>
<td width=148><div align=center class=style7><strong><span class=style2>Time</span></strong></div></td>
<td width=148><div align=center class=style7><strong><span class=style2>Taxcode</span></strong></div></td>
<td width=148><div align=center class=style7><strong><span class=style2>Sample_ID</span></strong></div></td>
<td width=148><div align=center class=style7><strong><span class=style2>SL</span></strong></div></td>
<td width=148><div align=center class=style7><strong><span class=style2>TL</span></strong></div></td>
<td width=148><div align=center class=style7><strong><span class=style2>BD</span></strong></div></td>
<td width=148><div align=center class=style7><strong><span class=style2>WT</span></strong></div></td>
<td width=148><div align=center class=style7><strong><span class=style2>Sex</span></strong></div></td>
<td width=148<div align=center class=style7><strong><span class=style2>MAT</span></strong></div></td>
<td width=148<div align=center class=style7><strong><span class=style2>Gear</span></strong></div></td>
<td width=148<div align=center class=style7><strong><span class=style2>Record</span></strong></div></td>
<td width=148><div align=center class=style7><strong><span class=style2>CL</span></strong></div></td>


</tr>";

while ($row= mysql_fetch_array ($numresults)){ //loop until all records from the table are selected
echo "<tr bordercolor=1>";
echo "<td>". $row[ 'DATE' ] ."</td>";
echo "<td>". $row[ 'MONTH' ] ."</td>";
echo "<td>". $row[ 'TIME' ] ."</td>";
echo "<td>". $row[ 'TAXCODE' ] ."</td>";
echo "<td>". $row[ 'SAMPLE_ID' ] ."</td>";
echo "<td>". $row[ 'SL' ] ."</td>";
echo "<td>". $row[ 'TL' ] ."</td>";
echo "<td>". $row[ 'BD' ] ."</td>";
echo "<td>". $row[ 'WT' ] ."</td>";
echo "<td>". $row[ 'SEX' ] ."</td>";
echo "<td>". $row[ 'MAT' ] ."</td>";
echo "<td>". $row[ 'GEAR' ] ."</td>";
echo "<td>". $row[ 'Record' ] ."</td>";
echo "<td>". $row[ 'CL' ] ."</td>";
echo "</tr>";}
echo "</table>";
echo "<center><h3>Click on the link to view as Excel file: <a href='excel/". $subcat3 .".xls'>". $subcat3 ."</a></h3>"; //Link to Excel file
echo "<center><input type=button value='Search another record' onClick=goToURL()></center>";}

?>
</div>
</div>

</div>
</body>
</html>

eelixduppy

5:27 am on Jan 5, 2009 (gmt 0)



You are going to have to dynamically make your MySQL query to sort by the way you want it. Something like this: 

$query = "SELECT * FROM `table_name` ORDER BY `" . $col ."` ASC";

Try different things around. You can always return all the data to PHP and then sort it using you own methods, but that is going to prove more work.

ivyleong

2:19 am on Jan 6, 2009 (gmt 0)

10+ Year Member



how can i declare the column headers to variables? which like a link after users click on it than the data will sort by whatever column's header been clicked.. Thanks.

eelixduppy

4:56 am on Jan 6, 2009 (gmt 0)



They will have to be defined in, let's say, the URI query. For instance a link like this would sort by size: http://www.example.com/table.php?sortby=size 

$query = "SELECT * FROM `table_name` ORDER BY `" . $_GET['size'] ."` ASC";

Note that this is only an example. In order to make this script live a bunch of security measures must be taken to ensure your database doesn't get hacked.

[edited by: eelixduppy at 5:29 am (utc) on Jan. 6, 2009]

ivyleong

5:24 am on Jan 6, 2009 (gmt 0)

10+ Year Member



how can i apply this action for those column names? like the taxcode, SL, TL... Because my column names now are echo strings. I mean what should i change this?:

<td width=148><div align=center class=style7><strong><span class=style2>Sample_ID</span></strong></div></td>

eelixduppy

5:30 am on Jan 6, 2009 (gmt 0)



If I understand you correctly, your column names are going to stay the same, and in the same place. The data which is under those columns, however, will change depending on how you are sorting them. So I am not sure what you are asking me. Can you please elaborate if I didn't already answer your question.

ivyleong

5:53 am on Jan 6, 2009 (gmt 0)

10+ Year Member



yes, i need my column names stay the same and in the same place. I just wonder how to make the data in the table sorting by users wish. For example, when user click on the SL column name, then the data will sort by SL, when the user click TIME, than the data will change to order by time... so i am asking which part of the codes i need to correct so that the table can be dynamic.

eelixduppy

5:57 am on Jan 6, 2009 (gmt 0)



However you have the table populating now should be the same. All of the echoing out of the contents and everything will all be the same. The only thing that is different, which I have mentioned above, is your query, which will change the order in which the results are return from the database. You have to identify which column they want to sort by, and then adjust the query accordingly. Try to work it out with code and get back here when you have something to work off of.

ivyleong

3:05 pm on Jan 6, 2009 (gmt 0)

10+ Year Member



sorry, i tried but i can't get it.. can you please tell me how to adjust the query accordingly? i want the data can be sort by all the coulumns. For example where should i put this?:
$query = "SELECT * FROM `table_name` ORDER BY `" . $_GET['size'] ."` ASC";

eelixduppy

3:22 pm on Jan 7, 2009 (gmt 0)



It should replace your current query. Make sure to use mysql_real_escape_string [php.net] on the query variable, though.
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

  1. Home
  2. Forums Index
  3. Server Side / PHP Server Side Scripting 10:43 am May 1, 2026