Order by two columns at the same time

[brattt]

I have 2 tables which I am joining: tbl_members and tbl_events.

tbl_members holds all the members information and tbl_events holds all the information for different events that members have attended.

I have joined the tables with the following:

SELECT tbl_members.memberID, tbl_members.firstName, tbl_members.lastName, tbl_events.memberID, tbl_events.date, tbl_events.eventName, tbl_events.points, tbl_events.sw_points
FROM tbl_events LEFT JOIN tbl_members ON tbl_events.memberID = tbl_members.memberID
ORDER BY tbl_members.lastName ASC

As I need to display all the members and the events associated with them at the same time, I am using a nested repeat region:

<?php  $lastTFM_nest = "";?>
<table width="100%" border="2" cellpadding="2" cellspacing="0" bordercolor="#FFFFFF" bgcolor="#CCCCCC"> <tr>
    <td class="header">NAME</td>
    <td class="header">DATE</td>
    <td class="header">EVENT</td>
    <td class="header">POINTS</td>
    <td class="header">SW POINTS </td>
      </tr>
  <?php do { ?>

<tr>
  <td><?php $TFM_nest = $row_allMemberPointsRS['memberID'];
if ($lastTFM_nest != $TFM_nest) { 
	$lastTFM_nest = $TFM_nest; ?>  <span class="header"><?php echo $row_allMemberPointsRS['firstName']; ?> <?php echo $row_allMemberPointsRS['lastName']; ?>        </span>
      <?php } //End of Basic-UltraDev Simulated Nested Repeat?></td>
      <td class="content"><?php echo $row_allMemberPointsRS['date']; ?></td>
      <td class="content"><?php echo $row_allMemberPointsRS['eventName']; ?></td>
      <td class="content"><?php echo $row_allMemberPointsRS['points']; ?></td>
      <td class="content"><?php echo $row_allMemberPointsRS['sw_points']; ?></td>
    </tr>
    <?php } while ($row_allMemberPointsRS = mysql_fetch_assoc($allMemberPointsRS)); ?>
  <tr>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
</table>

This works fine and the results look like this: http://www.cimdev.com.au/ahsca/intranet/test.html

My problem is that I need to order the date column as well. Could anyone give me an idea as to how I might acheive this as I am already ordering the recordset by 'lastname' as that is the column being used to filter the nested region.

[mikeyo]

in your sql query add the date in the order by:

SELECT tbl_members.memberID, tbl_members.firstName, tbl_members.lastName, tbl_events.memberID, tbl_events.date, tbl_events.eventName, tbl_events.points, tbl_events.sw_points
FROM tbl_events LEFT JOIN tbl_members ON tbl_events.memberID = tbl_members.memberID
ORDER BY tbl_members.lastName,  tbl_events.date ASC

[brattt]

Thanks Mikeyo. That works great but I now need my date to be formatted correctly. In the database they are in the following format: 22/10/2005

Which displays incorrectly so I tried this to change the format with no luck:

SELECT tbl_members.memberID, tbl_members.firstName, tbl_members.lastName, tbl_events.memberID, date_format(tbl_events.date, '%M %D, %Y') as date , tbl_events.eventName, tbl_events.points, tbl_events.sw_points
FROM tbl_events LEFT JOIN tbl_members ON tbl_events.memberID = tbl_members.memberID
ORDER BY tbl_members.lastName ASC,  tbl_events.date ASC

Any suggestions?

[Houdini]

How do you want to display your date month-day-year month/day/year 2 digit month and day and 2 digit year or four digit year? without specifying how you wish to format the date it would be hard to answer correctly.

Something is kind of wrong if your date format is dd/mm/yyyy because the date type format with MySQL is yyyy-mm-dd using the DATE type so apparnetly you are entering it as a VARCHAR or CHAR or something besides a DATE.