Mysql sort by date not working as expected [duplicate]

Question

I am pulling records out of my mysql database using php and want to order them by a database field called expdate.

The dates for the reminders are stored in the table in this format 17-04-12 as varchar.

I am using the following code to pull all the records out and order them by the expdate column.

    <table border="0" style="text-align:left;">
      <tr style="text-align:left;">
        <th style="text-align:left;" width="200px"  scope="col">Name</th>
        <th style="text-align:left;" width="200px"  scope="col">Email</th>
      <th style="text-align:left;" width="200px" scope="col">Telephone</th>
      <th style="text-align:left;" width="200px" scope="col">Current Cover Expires</th>
      </tr>
    <?php
    $today = date("d-m-y");
    $result = mysql_query("SELECT * FROM ymeg_chronoforms_data_NewsletterSubscribe ORDER BY expdate")or die(mysql_error());

    echo '<tr style="text-align:left;">';
    while($row = mysql_fetch_array($result))
      {
        echo '<td style="text-align:left;">';  
      echo $row['name'];
      echo '</td>'; 
      echo '<td style="text-align:left;">'; 
      echo $row['email'];
         echo '</td>'; 
    echo '<td style="text-align:left;">'; 
      echo $row['tel'];
         echo '</td>'; 
     echo '<td style="text-align:left;">'; 
      echo $row['expdate'];
        echo '</td>'; 
      echo "</tr>";
      }

      ?>


    </table>

The problem is, the columns are being sorted quite randomly, it outputs the records and sorts them by this date order :

08-07-12
17-05-12
17-05-13

Answer 1

try

order by STR_TO_DATE(expdate, '%d-%m-%y')

(and next time use real Date format... for Date datas ;) )

Answer 2

Store the dates in the proper date format inside MySQL. It would be best to rearrange the format produced by the datepicker and then store the rearranged date properly inside MySQL. This should solve your issue.