ORDER BY DATE ASC is sorting alphabetically and not by date.

jsvb1977
jsvb1977 used Ask the Experts™
on
I have a select query as shown below where i am grabbing a date from a table and formatting so that it displays in a friendly way. I need the data to ORDER BY DATE ASC.

Trouble is, when I apply the formatting [as shown below] the data is being sorted by the %W [which is the day of the week] so instead of being sorted by the actual date, it is sorting alphabetically.

Example: All the Friday's are listed first, followed by the Monday's etc...

I have also included the code where the data is being inserted into the html table if it helps.

Any ideas how to correct this?

Jason


$sql = "select EVENT_ID, TITLE, IMG, DATE_FORMAT(DATE, '%W, %M %e %Y') AS DATE, TIME, LOCATION, LEFT(DESCRIPTION, 200) AS DESCRIPTION 
        from events where DATE between curdate() and curdate() + interval 52 week 
        ORDER BY DATE ASC";
 
================================================================
 
<div class="eventsdiv">
<table class="eventstable" cellpadding="0" cellspacing="0">
<?php
	while($row = mysql_fetch_assoc($result)) {
?>
<tr>
<td class="img"><?php echo $row['IMG']; ?></td>
<td class="eventscontent">
<span class="TheTitle"><?php echo $row['TITLE']; ?></span><br />
<span class="TheDate"><?php echo $row['DATE']; ?></span><br />
<span class="TheTime"><?php echo $row['TIME']; ?></span><br />
<span class="TheLocation"><?php echo $row['LOCATION']; ?></span><br />
<span class="TheDescription"><?php echo strtok(wordwrap($row['DESCRIPTION'],150),"\n"); ?></span>
<span class="TheLink"><a href="descriptionpop.php?EVENT_ID=<?php echo $row['EVENT_ID']; ?>" onclick="return popitup('descriptionpop.php?EVENT_ID=<?php echo $row['EVENT_ID']; ?>')" >...Read more&nbsp;<img src="images/application_double.png" alt="Pop Up" border="0" height="10" width="10" /></a></span>
</td>
</tr>
<?php } ?>
</table>
</div>

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi jsvb1977,

The easiest thing to do is to change the alias in the date column.  :)

You don't have to select (display) the item that is the sort key.  If you rename the column (calling it anything but 'DATE'), the date field from the database (in the date format) will be the sort key, not the converted string that the query generates.


Good Luck,
Kent

Author

Commented:
Well done sir! It makes so much sense now.

Attached are the changes I made so that someone else may be able to benefit from an example.
$sql = "select EVENT_ID, TITLE, IMG, DATE_FORMAT(DATE, '%W, %M %e %Y') AS DATE_FORMATTED, TIME, LOCATION, LEFT(DESCRIPTION, 200) AS DESCRIPTION 
        from events where DATE between curdate() and curdate() + interval 52 week 
        ORDER BY DATE ASC";
 
====================================================================
 
<div class="eventsdiv">
<table class="eventstable" cellpadding="0" cellspacing="0">
<?php
	while($row = mysql_fetch_assoc($result)) {
?>
<tr>
<td class="img"><?php echo $row['IMG']; ?></td>
<td class="eventscontent">
<span class="TheTitle"><?php echo $row['TITLE']; ?></span><br />
<span class="TheDate"><?php echo $row['DATE_FORMATTED']; ?></span><br />
<span class="TheTime"><?php echo $row['TIME']; ?></span><br />
<span class="TheLocation"><?php echo $row['LOCATION']; ?></span><br />
<span class="TheDescription"><?php echo strtok(wordwrap($row['DESCRIPTION'],150),"\n"); ?></span>
<span class="TheLink"><a href="descriptionpop.php?EVENT_ID=<?php echo $row['EVENT_ID']; ?>" onclick="return popitup('descriptionpop.php?EVENT_ID=<?php echo $row['EVENT_ID']; ?>')" >...Read more&nbsp;<img src="images/application_double.png" alt="Pop Up" border="0" height="10" width="10" /></a></span>
</td>
</tr>
<?php } ?>
</table>
</div>

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial