以DD / MM / YYYY格式存储在VARCHAR中的PHP日期需要在SQL查询中以ASC ORDER进行排序

as the title suggests the date format in the database in a table is stored as DD/MM/YYYY format in VARCHAR (with slashes). I want to sort the results in ascending order only. But my query is not allowing me to do so. A simple ORDER by order_date ASC does not work. The developer who did this before made this mistake of not inserting it in DATE format. Now the problem is that it has thousands of records which cannot be edited now one by one. I therefore need to have a solution to display it in ascending order by writing a PHP function. But I do not know how to. Can anybody please help?

SQL

<?php

                $this->db->select('str_to_date(your_col, '%d/%m/%Y') as my_date');
                $this->db->order_by('my_date','ASC');
                $this->db->where('customer_id',$id);
                $qry3 = $this->db->get('due');

                    foreach ($qry3->result() as $row){

                $total=$total+ ($row->due-$row->paid_amount);
                $total=$total-$row->discount_allowed;
                $total=$total+$row->credit_note_amount;
                ?>


                    <tr>
                        <td><?php echo $row->my_date?> </td>
                        <td><?php echo $row->order_no?></td>
                        <td>
                        <?php
                        if (isset($row->due)) {
                           echo "SALE A/C";
                        } else if (isset($row->particulars)) {
                          echo $row->particulars;
                        } else {
                            echo $row->particulars;
                        }
                        ?>
                        </td>
                        <td align="">
                        <?php if (isset($row->due))
                            {
                            echo $row->due;
                            } else {
                                echo $row->credit_note_amount;
                            }
                        ?>
                         </td>
                        <td align="">
                        <?php

                        if (isset($row->paid_amount)) {
                          echo $row->paid_amount;
                        } else if (isset($row->discount_allowed)) {
                            echo $row->discount_allowed;
                        } else {

                        }
                         ?> </td>
                        <td class="info"><?php echo $total?></td>



                    </tr>

                    <?php }?>

You can simply convert your varchar column as a date

  select str_to_date(your_col, '%d/%m/%Y') my_date
  from your_table  
  order by my_date ASC


  $this->db->select(' paid_amount,  discount_allowed,  str_to_date(your_col, '%d/%m/%Y') as my_date');
  $this->db->order_by('my_date','ASC');

You can try using STR_TO_DATE of MySQL

SELECT  STR_TO_DATE(yourdatefield, '%m/%d/%Y') as newcolumnname
FROM    yourtable order by newcolumnname asc