I have developed a php project which contains the table 'test'. The rows are fetched and shown and it is sorted by date column using ORDER BY clause in SQLquery. The following is the block of code used
<?php
foreach($stmt =$conn->query('SELECT * FROM test JOIN users USING(rollno)
WHERE rollno='.$userRow['rollno'].' order by date desc')as $row){ ?>
<button class="accordion"><b>Test on <?php echo date('d-m-Y', strtotime( $row['date'] )); ?></b></button>
<div class="panel">
<p>
<table border="1">
<?php
echo "<tr><td>Date: " . date('d-m-Y', strtotime( $row['date'] )) . "</td></tr>";
echo "<tr><td>Subject: " . $row['sub'] . "</td></tr>";
echo "<tr><td>Topic: " . $row['topic'] . "</td></tr>";
echo "<tr><td>Type: " . $row['type'] . "</td></tr>";
echo "<tr><td>Total Marks: " . $row['totl_marks'] . "</td></tr>";
echo "<tr><td>Marks Obtained: " . $row['marks'] . "</td></tr>";
echo "<tr><td>Rank: " . $row['rank'] . "</td></tr>"; ?>
</table>
</p>
</div>
<?php }?>
I want to sort the records such that the newest comes at top. The date stored in the database is in d-m-Y format. The system works fine at localhost, but when the project is made live, the records are not sorted as expected. The date column in database uses varchar type. What can be the problem?
STR_TO_DATE function in mysql can make the help. It will first convert date string in database to date type and then apply order by.
Order by query should be:
ORDER BY STR_TO_DATE(date,'%d-%m-%Y') DESC