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;
}*/
?>