asked on

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

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

ASKER CERTIFIED SOLUTION

Kent Olsen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jsvb1977

ASKER

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