Sorting By Header

[Ache]

How to change the code so that the generated table is sortable by header?

<?php/// In order to use this script freely/// you must leave the following copyright/// information in this file:/// Copyright 2006 www.turningturnip.co.uk/// All rights reserved.include("connect.php");$query="SELECT * FROM free ";$result=mysql_query($query);$num = mysql_num_rows ($result);mysql_close();echo "<table cellspacing=\"2\" cellpadding=\"2\" border=\"0\">\n";echo "	<tr><th>Naam</th>\n";echo "		<th>Telefoon</th>\n";echo "		<th>Antw. Nr.</th>\n";echo "		<th>Postcode</th>\n";echo "		<th>Plaats</th>\n";echo "		<th>Omschrijving</th>\n";echo "	</tr>\n";if ($num > 0 ) {$i=0;while ($i < $num) {$naam = mysql_result($result,$i,"naam");$telefoon = mysql_result($result,$i,"telefoon");$antwoordnummer = mysql_result($result,$i,"antwoordnummer");$postcode = mysql_result($result,$i,"postcode");$plaats = mysql_result($result,$i,"plaats");$omschrijving = mysql_result($result,$i,"omschrijving");$id = mysql_result($result,$i,"id");echo "<tr><td></tr>$naam</td>";echo "<td>$telefoon</td>";echo "<td>$antwoordnummer</td>";echo "<td>$postcode</td>";echo "<td>$plaats</td>";echo "<td>$omschrijving</td></tr>";++$i; } } else { echo "The database is empty"; }echo "</table>\n";echo "<br><br>";?>

[justsomeguy]

Make each header a link that includes the column name to sort by. e.g.:<a href="page.php?sort=naam">In the PHP, you can check $_GET['sort'] to see if a sort column was given and, if so, add it to the SQL query in the ORDER BY clause.

[Ache]

With the answer from justsomeguy (and Google) I came to the code below.I know soemthing has to change at the "if ($num > 0 ) {" part.But sofar I am not suvvesfull.

<table cellspacing="2" cellpadding="2" border="0">	<tr><th><span title="naam"><a href="test.php?sort=naam">Naam</a></span></th>		<th><span title="tele"><a href="test.php?sort=tele">Telefoon</a></span></th>		<th><span title="antw"><a href="test.php?sort=amtw">Antw. Nr.</a></span></th>		<th><span title="post"><a href="test.php?sort=post">Postcode</a></span></th>		<th><span title="plaa"><a href="test.php?sort=plaa">Plaats</a></span></th>		<th><span title="omsc"><a href="test.php?sort=omsc">Omschrijving</a></span></th>	</tr><?php$sort = "DESC";if(isset($_GET["sort"]) && !empty($_GET["sort"]))	{		$sort = ($_GET["sort"]=="ASC") ? "DESC" : "ASC";	}$order_query = " ORDER BY naam,tele,antw,post,plaa,omsc $sort";if( isset($_GET["orderBy"]) && !empty($_GET["orderBy"]) )	{		 switch ($_GET["orderBy"])		{			case naam:			$order_query = " ORDER BY naam $sort";			break;			case tele:			$order_query = " ORDER BY tele $sort";			break;			case tele:			$order_query = " ORDER BY antw $sort";			break;			case tele:			$order_query = " ORDER BY post $sort";			break;			case tele:			$order_query = " ORDER BY plaa $sort";			break;			case tele:			$order_query = " ORDER BY omsc $sort";			break;			default:			$order_query = " ORDER BY naam,tele,antw,post,plaa,omsc $sort";			break;		}	}if ($num > 0 ) {$i=0;while ($i < $num) {$naam = mysql_result($result,$i,"naam");$telefoon = mysql_result($result,$i,"telefoon");$antwoordnummer = mysql_result($result,$i,"antwoordnummer");$postcode = mysql_result($result,$i,"postcode");$plaats = mysql_result($result,$i,"plaats");$omschrijving = mysql_result($result,$i,"omschrijving");$id = mysql_result($result,$i,"id");echo "<tr>";echo "<td>$naam</td>";echo "<td>$telefoon</td>";echo "<td>$antwoordnummer</td>";echo "<td>$postcode</td>";echo "<td>$plaats</td>";echo "<td>$omschrijving</td></tr>";++$i; } } else { echo "The database is empty"; }echo "</table>\n";echo "<br><br>";?>

[justsomeguy]

This stuff:case naam:needs to be quoted:case 'naam':For this:

if(isset($_GET["sort"]) && !empty($_GET["sort"]))	{		$sort = ($_GET["sort"]=="ASC") ? "DESC" : "ASC";	}

$_GET['sort'] is never going to be ASC or DESC, it's going to be a column name. $sort will always be "ASC".With this stuff:

if ($num > 0 ) {$i=0;while ($i < $num) {

$num isn't defined anywhere, I don't know what you're trying to do with that.And, most importantly, you're never running the SQL query. You start using mysql_result without ever having gotten the results in the first place.

[Ache]

Ok, placed the case naam etc in quotes (case 'naam')

$_GET['sort'] is never going to be ASC or DESC, it's going to be a column name. $sort will always be "ASC".

That would do for me.

$num isn't defined anywhere, I don't know what you're trying to do with that.

I know, that is why I said that it has to be changed.

And, most importantly, you're never running the SQL query. You start using mysql_result without ever having gotten the results in the first place.

I know, but I don't know sql at all and only recently started to learn php. That is why I am askig for help

[justsomeguy]

Ok, placed the case naam etc in quotes (case 'naam')

All of the cases need to be quoted, not just the one. It looks like you have all the other cases checking for the same thing though ("tele").

That would do for me.

OK, then you might as well remove the code that is setting $sort and just set it yourself to "ASC".

I know, that is why I said that it has to be changed.

Yes, it does have to be changed. If you don't know what the code is doing there, then either research it to figure out what it's doing, or remove it and see what happens. You shouldn't have code in there that you don't know what it does, either learn what it does or take it out. You removed this piece of code that you originally had:

$query="SELECT * FROM free ";$result=mysql_query($query);$num = mysql_num_rows ($result);mysql_close();

That executes the query and also sets $num to be the number of records that the query returned. I'm not sure why you took that out, but it looks like you did. All you really need to do is move that code down below the switch statement, above the while loop, so that it runs the query with the added ORDER BY stuff on the end. You need to run the query right before you check what $num is in the if statement. You can join the queries like this:$query="SELECT * FROM free " . $order_query;If you print out $query after that, you'll see that it also contains the ORDER BY columns.Also, just for the sake of making the code easier to read, you can replace this block:

if ($num > 0 ) {$i=0;while ($i < $num) {$naam = mysql_result($result,$i,"naam");$telefoon = mysql_result($result,$i,"telefoon");$antwoordnummer = mysql_result($result,$i,"antwoordnummer");$postcode = mysql_result($result,$i,"postcode");$plaats = mysql_result($result,$i,"plaats");$omschrijving = mysql_result($result,$i,"omschrijving");$id = mysql_result($result,$i,"id");echo "<tr>";echo "<td>$naam</td>";echo "<td>$telefoon</td>";echo "<td>$antwoordnummer</td>";echo "<td>$postcode</td>";echo "<td>$plaats</td>";echo "<td>$omschrijving</td></tr>";++$i; } } else { echo "The database is empty"; }

with this:

if ($num > 0 ){  $i=0;  while ($i < $num)  {	$naam = mysql_result($result,$i,"naam");	$telefoon = mysql_result($result,$i,"telefoon");	$antwoordnummer = mysql_result($result,$i,"antwoordnummer");	$postcode = mysql_result($result,$i,"postcode");	$plaats = mysql_result($result,$i,"plaats");	$omschrijving = mysql_result($result,$i,"omschrijving");	$id = mysql_result($result,$i,"id");	echo "<tr>";	echo "<td>$naam</td>";	echo "<td>$telefoon</td>";	echo "<td>$antwoordnummer</td>";	echo "<td>$postcode</td>";	echo "<td>$plaats</td>";	echo "<td>$omschrijving</td></tr>";	++$i;  }}else{  echo "The database is empty";}

It's the same code, just formatted to be easier to read.

[Ache]

Before I forget, thanks for your help. I really appreciate it and I am learning from it. I hope I understand/do everything correctly. After (again) making changes I now have the code as shown below. Did I do that right?

<?phpinclude("connect.php");?><table cellspacing="2" cellpadding="2" border="0"> <tr><th><span title="naam"><a href="test.php?sort=naam">Naam</a></span></th> <th><span title="tele"><a href="test.php?sort=tele">Telefoon</a></span></th> <th><span title="antw"><a href="test.php?sort=amtw">Antw. Nr.</a></span></th> <th><span title="post"><a href="test.php?sort=post">Postcode</a></span></th> <th><span title="plaa"><a href="test.php?sort=plaa">Plaats</a></span></th> <th><span title="omsc"><a href="test.php?sort=omsc">Omschrijving</a></span></th> </tr><?php$sort = "DESC";if(isset($_GET["sort"]) && !empty($_GET["sort"])) { $sort = ($_GET["sort"]=="ASC") ? "DESC" : "ASC"; }$order_query = " ORDER BY naam,tele,antw,post,plaa,omsc $sort";if( isset($_GET["orderBy"]) && !empty($_GET["orderBy"]) ) { switch ($_GET["orderBy"]) { case 'naam': $order_query = " ORDER BY naam $sort"; break; case 'tele': $order_query = " ORDER BY tele $sort"; break; case 'antw': $order_query = " ORDER BY antw $sort"; break; case 'post': $order_query = " ORDER BY post $sort"; break; case 'plaa': $order_query = " ORDER BY plaa $sort"; break; case 'omsc': $order_query = " ORDER BY omsc $sort"; break; default: $order_query = " ORDER BY naam,tele,antw,post,plaa,omsc $sort"; break; } }$query="SELECT * FROM free ";$result=mysql_query($query);$num = mysql_num_rows ($result);mysql_close();if ($num > 0 ){ $i=0; while ($i < $num) { $naam = mysql_result($result,$i,"naam"); $telefoon = mysql_result($result,$i,"telefoon"); $antwoordnummer = mysql_result($result,$i,"antwoordnummer"); $postcode = mysql_result($result,$i,"postcode"); $plaats = mysql_result($result,$i,"plaats"); $omschrijving = mysql_result($result,$i,"omschrijving"); $id = mysql_result($result,$i,"id"); echo "<tr>"; echo "<td>$naam</td>"; echo "<td>$telefoon</td>"; echo "<td>$antwoordnummer</td>"; echo "<td>$postcode</td>"; echo "<td>$plaats</td>"; echo "<td>$omschrijving</td></tr>"; ++$i; }}else{ echo "The database is empty";}echo "</table>\n";echo "<br><br>";?>

[justsomeguy]

Pretty much, you just need to add the order by stuff to the query.$query="SELECT * FROM free " . $order_query;

[Ache]

Pretty much, you just need to add the order by stuff to the query.$query="SELECT * FROM free " . $order_query;

Add? You mean replace?$query="SELECT * FROM free ";with$query="SELECT * FROM free " . $order_query;If I do that I get:Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in http://gratis.ache.nl/test.php on line 61

[justsomeguy]

Print $query to see what it is, apparently there's an error in it.

[Ache]

This is what I get to see:Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /storage/mijndomein/users/040228/public/sites/gratis.ache.nl/test.php on line 61SELECT * FROM free ORDER BYnaam,tele,antw,post,plaa,omsc,DESC < this is the line the query showsThe database is empty < This is weird since there are 3 sample items in the database

[justsomeguy]

The problem is the comma before DESC. You use DESC to modify any sort field. e.g.:ORDER BY naam DESC, tele ASC, antw DESC, post DESC, plaa ASC, omsc DESC

[Ache]

Thansk for your help and patience.Got it working now.Had to replace $sort with $orderBy also.