need to sort ...not sure about arrays

lobster

Hello,

I have the following code where the results are in an array...how do I sort according to $row["DATE"]? When I tried to add sort($row["DATE"]); it gave be an error about the array. Please help.

       

<?   

    for ($i=0; $i <$num_results; $i++)
	  {
	  $bgcolor = ($c++ & 1) ? '#FFFFCC' : 'white';
	    $row = mysql_fetch_array($result);

	echo "<tr bgcolor=$bgcolor class='itintext'><td>";
	echo htmlspecialchars( stripslashes($row["DATE"]));
	echo "</td>";
	echo "<td>|</td>";
	echo "<td>";
	echo htmlspecialchars( stripslashes($row["TIME"]));
	echo "</td>";
	echo "<td>|</td>";
	echo "<td>";
	echo htmlspecialchars( stripslashes($row["DAY"]));
	echo "</td>";
	echo "<td>|</td>";
	echo "<td>";
	echo htmlspecialchars( stripslashes($row["DESCRIPTION"]));
	echo "</td></tr>";
  }
?>

bernouli

$result = mysql_query("....... ORDER BY DATE");
$row = mysql_fetch_array($result);

lobster

the dates will sort however February dates show before January because of the alphabetical month...any way to fix this?

this is my sql query:

   $query = "select DATE_FORMAT(DATE, '%M %d %Y')AS DATE, TIME, DAY, DESCRIPTION, ID
   FROM ITINERARY
   ORDER by DATE
   ";

and this is how it is appearing...

February 01 2003
January 01 2003
January 07 2003
January 08 2003

January 21 2003
March 23 2003

Thanks again...

bernouli

maybe this help

$query = "select DATE_FORMAT(DATE, '%Y %m %d')AS DATE, TIME, DAY, DESCRIPTION, ID

ahundiak

It's not always possible to get the database to sort the way you want.

But it is easy to sort an array by using the usort() function. Check the php manual for details.

lobster

The solution of
DATE_FORMAT(DATE, '%Y %m %d')AS DATE would work if I wanted it to appear numerically...so now the list looks like

2003 01 17
2003 01 18
2003 02 18

and so on...not to visually pleasing or user friendly. I need it to say

January 17, 2003
January 18, 2003
February 18, 3003

I have been trying to understand the usort() function and how I can use it but can I use it properly at all if I am getting the Month back in characters and not numbers ? Febrary will always come before January in that regard.

Can I pull the data as suggested in format:

$query = "select DATE_FORMAT(DATE, '%Y %m %d')AS DATE, TIME, DAY, DESCRIPTION, ID
   FROM ITINERARY
   ORDER by DATE

then call my array of rows:

for ($i=0; $i <$num_results; $i++)
	  {
	  $bgcolor = ($c++ & 1) ? '#FFFFCC' : 'white';
	    $row = mysql_fetch_array($result);

and somehow reformat the date after it is pulled and sorted in here?:

echo "<tr bgcolor=$bgcolor class='itintext'><td>";
		echo htmlspecialchars( stripslashes($row["DATE"]));
		echo "</td>";

ahundiak

As long as the database is giving you the records in the desired order then there is no need for usort().

What you need to do is to convert the date to a more pleasing format with something like:

function convert_date($date)
{
  $time = mktime(0,0,0,
    substr($date,5,2),  // Month
    substr($date,8,2),  // Day
    substr($date,0,4)); // Year

  return date('F d,Y',$time);
}

and use
echo htmlspecialchars(convert_date( stripslashes($row["DATE"])));

lobster

That did it! Its perfect! Thank you so much.