Sorting table columns

[jcruz900]

I'm having trouble sorting my column tables. When a title of a column is clicked, it is sorted alphabetically or by number smallest to biggest. I'm also having trouble formatting the date (Born and Died dates). Currently it looks like this YYYY-MM-DD. I need it to look like this MM-DD-YYYY.

Would something like this work?

$query = "SELECT * FROM table_name WHERE DATE_FORMAT(Born,'%c/%d')='" . date('n/d') . "'";

echo "$query\r\n";
$result = mysql_query($query) or die("Query failed: $query " . mysql_error());
while ($line = mysql_fetch_assoc($result)) {
  echo "Born: " . date('m/d/Y',strtotime($line['Born'])) . "\r\n";
  echo "Died: " . date('m/d/Y',strtotime($line['Died'])) . "\r\n";
  print_r($line);
}

Here is what I have so far. Any input would help! Thanks for looking.

<TABLE WIDTH="618" HEIGHT="40" BORDER="0" align="center" CELLPADDING="3" CELLSPACING="5" BGCOLOR="#FFFFFF">
<TR bgcolor="#F0F0F0">



<TD width="180" HEIGHT="40" ALIGN=bottom ID="header"><div align="center"><em><FONT FACE="Verdana, Arial, Helvetica, sans-serif" SIZE="1" COLOR="#8A8A8A">Name </FONT></em></div></TD>
<TD width="90" ALIGN=middle ID="header"><div align="center"><em><FONT COLOR="#8A8A8A" SIZE="1" face="Verdana, Arial, Helvetica, sans-serif"><a href="index.php?sort=Born">Birth</a></FONT></em></div></TD>
<TD width="100" ALIGN=middle ID="header"><div align="center"><em><FONT COLOR="#8A8A8A" SIZE="1" face="Verdana, Arial, Helvetica, sans-serif"><a href="index.php?op=sort_Died">Death</FONT></em></a></div></TD>
<TD width="60" ALIGN=middle ID="header"><div align="center"><em><FONT COLOR="#8A8A8A" SIZE="1" face="Verdana, Arial, Helvetica, sans-serif"><a href="index.php?op=sort_Friendship">Friendship</FONT></em></div></a></TD>
<TD width="118" ALIGN=middle ID="header"><div align="center"><em><FONT COLOR="#8A8A8A" SIZE="1" face="Verdana, Arial, Helvetica, sans-serif">Cause Of Death </a></FONT></em></div></TD>
</TR>



<?php

include 'db.php'; 



$query = 'SELECT FirstName,LastName,Born,Died,Friendship,CauseOfDeath FROM table_name ORDER BY LastName';

$result = mysql_query($query) 
	or die("Retrieve query failed: $query " . mysql_error());

echo "<center><strong>"  . mysql_num_rows($result) . " record" . 
  ((mysql_num_rows($result) != 1) ? "s" : "") . 
  " returned. </center></strong><br />\r\n";


mysql_query($query) or die ( mysql_error()); 


$num=mysql_num_rows($result); 
$i=0; 

if(isset($GET['op'])) {
switch($op) {
case "sort_LastName";
$query = "SELECT FirstName,LastName,Born,Died,Friendship FROM table_name ORDER BY LastName";
break;
case "sort_Born";
$query = "SELECT FirstName,LastName,Born,Died,Friendship FROM table_name ORDER BY Born";
break;
case "sort_Died";
$query = "SELECT FirstName,LastName,Born,Died,Friendship FROM table_name ORDER BY Died";
break;
case "sort_Friendship";
$query = "SELECT FirstName,LastName,Born,Died,Friendship FROM table_name ORDER BY Friendship";
break;
}
} else { // use default sorting
$query = "SELECT * FROM table_name";
}


$result = mysql_query($query);



while ($i< $num) 
{
	$a=mysql_result($result,$i,'LastName');
	$b=mysql_result($result,$i,'FirstName'); 
	$c=mysql_result($result,$i,'Born');
	$d=mysql_result($result,$i,'Died');
	$e=mysql_result($result,$i, 'Friendship');
	$f=mysql_result($result,$i,'CauseOfDeath');
		
	print ($i % 2) ? "<tr bgcolor=\"D4D3EB\">" : "<tr bgcolor=\"FFFFFF\">";
	print "<td align=left>$a , $b</td>";
	print "<td align=left>$c</td>";
	print "<td align=left>$d</td>";
	print "<td align=left>$e</td>";
	print "<td align=left>$f</td>";
	

	++$i; 
}

/* Can't seem to get this one to work either 

$orderBy = array('Born', 'Died', 'CauseOfDeath'); 

$order = 'CauseOfDeath'; 

if(isset($_GET['orderBy']) && in_array($_GET['orderBy'], $orderBy)) {
	$order = $_GET['orderBy']; 
}
$query = 'SELECT * FROM table_name  ORDER BY $order';
*/
		

/* I've tried this. Doesn't work. 

$sort = 
$query = "SELECT * FROM table_name";

if($_GET['sort'] == 'Born')
{
	$query .= "ORDER BY Born"; 
}
elseif($_GET['sort']=='Death')
{
	$query .= "ORDER BY Died"; 
}
elseif(isset($_GET['sort']) && ($_GET['sort']=='Friendship'))
{
	$query .= "ORDER BY Friendship, LastName,FirstName"; 
}
elseif(isset($_GET['sort']) && ($_GET['sort']=='Cause Of Death'))
{
	$query .= "ORDER BY CauseOfDeath,LastName,FirstName,Born, Died"; 
}
else{
	$query;
}*/



?>

[cwarn23]

For the user to be able to resort the table upon clicking the column heading I would suggest making each column having a heading like index.php?orderby=birth then in the php code you can check if the $_GET variable exists and if it does then to sort by that in the mysql query.