Sorting of headers of table

Techissue2008

I want to do sorting for all headers of a table:

<table>
 <tr>
  <td width="10"><StudentID</td> //it is a number, click it and sorting
  <td width="100">Exam Subject</td> //it is a name, click it and sorting
  <td width="100">Date</td> //it is a date, click it and sorting
  <td width="100">Marks</td> //it is a number, click it and sorting
  </tr>
<?php
	while($row = mysql_fetch_array($result))
	{
		list($studentid, $subject, $date, $marks) = $row;
?>
		 <tr> 
		  <td width="10"><?php echo $studentid;?></td>
		  <td width="100"><?php echo $subject;?></td>
		  <td width="100"><?php echo $date;?></td>
		  <td width="100"><?php echo $marks;?></td>   		                 </tr>
 <?php
 }
?>
</table>

Piranha

I suggest that you do the headlines as links back to the same page with the same parameters. You should also add one parameter, which represents the column you want to order by. When you create the database query you check that parameter and use "order by" on the correct column.

Techissue2008

I tried what did you suggest and I faced a SQL error:

Here is the SQL:

if(isset($_GET['sort']))
{
$query =SELECT t1.uid,  t1.distid, t3.districtid, t3.district as b FROM units AS t1 left join district as t3 on t1.distid = t3.districtid ORDER BY '{$_GET['sort']}' ASC

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

I tested it in phpmyadmin and it works. But, the script has error when I pass 'b' to it.

Piranha

Do not use variables from POST/GET in the query without making sure it is safe against SQL injection. Never. Ever. Or, come to think about it, please do it if you want users to be able to get information they shouldn't get, or change or delete from the tables.
Do something like this (I know the PHP code is not ok):

$sort = $_GET['sort'];
$query = "SELECT t1.uid,  t1.distid, t3.districtid, t3.district as b FROM units AS t1 left join district as t3 on t1.distid = t3.districtid";
if ($sort == "a") {
   $query = $query + " order by column1 desc";
} else if ($sort == "b") {
   $query = $query + " order by column1 desc";
}

Then you have full control on what is happening in the query.

Techissue2008

I use the Sorting SQL on phpmyadmin and it works, however, it showed 500 error when I sort it. Here is the full code:

<html>
<script language="JavaScript">
function Sorting(id)
{
window.location.href = 'index.php?sort=' + id;
}
</script>
<body>
<?php
include 'connectDB.php';
$sort = $_GET['sort'];
$query = "SELECT t1.buildingid, t1.uid, t1.distid, t3.districtid, t3.district as b FROM buildings AS t1 left join district as t3 on t1.distid = t3.districtid";
if ($sort == "b") {
   $query = $query + " order by b asc";
} 
$result = mysql_query($query) or die('Error, query failed. ' . mysql_error());
if(mysql_num_rows($result) == 0)
{
?>
<p>There is no record.</p>
<?php
}
else
{
?>
<table width="140">
  <tr align="center" bgcolor="#E1EFF1">
  <td width="10">BuildingID</td>
  <td width="10">UserID</td>
  <td width="10">DistID</td>
  <td width="10">DistrictID</td>
  <td width="100"><a href="javascript:Sorting('b');">District</a></td>
 </tr>
<?php
	while($row = mysql_fetch_array($result))
	{
		list($buildingid, $uid, $distid, $districtid, $b) = $row;
?>
		 <tr align="center" bgcolor="#FFFFFF"> 
		  <td width="10"> 
		   <?php echo $buildingid;?>
		  </td>
		  <td width="10"> 
		   <?php echo $uid;?>
		  </td>
		  <td width="10"> 
		   <?php echo $distid;?>
		  </td>
		  <td width="10"> 
		   <?php echo $districtid;?>
		  </td>
		  <td width="100"> 
		   <?php echo $b;?>
		  </td>		  

		 </tr>
 <?php
  }
 }
?>
</table>
</body>
</html>

It can show the list, but it cannot show the page after sorting.

Any idea?

Piranha

Please post the exact query (by using echo on the $query variable), and the error you get.

[edit] Check if you can run that query in phpmyadmin as well [/edit]

Techissue2008

I added echo $query

$sort = $_GET['sort'];
$query = "SELECT t1.buildingid, t1.uid, t1.distid, t3.districtid, t3.district as b FROM buildings AS t1 left join district as t3 on t1.distid = t3.districtid";
if ($sort == "b") {
   $query = $query + " order by b asc";
} 
echo $query;
$result = mysql_query($query) or die('Error, query failed. ' . mysql_error());

Here is the error:
0Error, query failed. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '0' at line 1

It echo '0' for the query variable.

Techissue2008

I searched the append string seems wrong and so caused '0' for $query.

http://phplens.com/phpeverywhere/node/view/30

$query .= " order by b asc";

instead of $query = $query + " order by b asc";

Techissue2008

Yes, .= can fix the problem.

And, I want to ask

If user clicks the "link" header, it can sort by ASC now.

Can the user click it again, it sorts by DESC?

i.e. first click - ASC, second click - DESC

Can it be done? and what is the logic?

Thanks for your help

Piranha

My mistake. I am developing in Java now, and there you use + as concatenation in strings. In php it should be a dot. But if you had checked the code (as you always should, you don't know if someone provides code with errors) it would be no problem for you to find out.

About your second question: Think about it for a while. You don't need anything but what is already used.

Techissue2008

Could you give me a hint?

If user click the same hyperlink of the header, it still calls

if ($sort == "b") {
$query .= " order by b asc";
}

Because it still passes the 'b' to it.

Except the code can shift to call

if ($sort == "b") {
$query .= " order by b desc";
}

when the user clicks the link again.

But, how to do it with the same link and variable?

Or, it can use a flag? But, I do not know how to use it.

Please advise.

Piranha

Techissue2008 wrote:
Or, it can use a flag? But, I do not know how to use it.

To make it easy you can do it in one of 2 ways:

Use another variable.
Use another name in the variable to indicate asc/desc.

In any case just check the variables where you print the link, and make a link accordingly.

Of course there are more ways, I would use one of the above.

Techissue2008

My case is that I set the js to pass the variable to the php code

<a href="javascript:Sorting('b');">District</a>
function Sorting(id)
{
window.location.href = 'index.php?sort=' + id;
}

I do not understand how can I change the
<a href="javascript:Sorting('b');">District</a>

to be <a href="javascript:Sorting('b1');">District</a>

after I clicking the header text.

How can it shift each other for the same link?

Roger_Ramjet

Now I've posted this here with in depth examples some time ago so I'll just post the script and you can search for previous posts if all is not clear.

This is, or these are, javascripts that make html tables sortable in-page, rather than through another server call and page reload.