Mark2024 Posted October 26 Share Posted October 26 Hello Guys, Im trying to display my records in the database by the field PayDate field as i have all different dates and they are not in order for example 29/10/2024 and 27/10/2024 and 15/09/2024 im trying to display them all in order im using the code below can anyone help me please? <?php include('styles/top.php'); ?> <div id="full"> <div id="view_box"> <ul> <li><img src="images/1.png" /></li> <!-- <li><img src="pics/2.jpg" /></li> --> <!-- <li><img src="pics/3.jpg" /></li> --> </ul> </div> <div id="button"> <ul> <!-- <li><button class="button" scroll_value="0">*</button></li> -- > <!-- <li><button class="button" scroll_value="600">*</button></li> --> <!-- <li><button class="button" scroll_value="1200">*</button></li> --> </ul> </div> <hr /><br /> <?php //error_reporting(0); if (($user_level !=1) && ($user_level !=2)){ echo "No Access Allowed"; } else { if(isset($_GET['page'])){ $page = $_GET['page']; } else { $page = 1; } if($page == '' || $page == 1){ $page1 = 0; } else { $page1 = ($page*4)-4; } $sql = "SELECT * FROM orders Where orderby= '$username' AND Status='payon' LIMIT ".$page1.", 4"; //$sql = 'SELECT * FROM orders LIMIT '.$page1.', 4'; $data = $con->query($sql); ?> <h3>View Orders</h3> <table border=1"> <tr> <th>FULL NAME </th> <th>DATE GOT </th> <th>PAY DATE</th> <th>MONEY DUE</th> <th>UPDATE ORDER STATUS</th> </tr> <tr> <td> </td> <td></td> <td></td> <td></td> <td></td> </tr> <?php $totmoney = 0; $strmoney = ""; while ($myrow = mysqli_fetch_array($data)) { $strmoney = $myrow["Money"]; $strmoney = str_replace(',','',$strmoney); if(is_numeric($strmoney)) { settype($strmoney, "float"); $strmoney = number_format($strmoney, 2); } echo "<TR><TD><a href=\"_view.php?id=".$myrow['id']."\">".$myrow["FullName"]."</A> </TD><TD>".$myrow["GotDate"]." </TD><TD>".$myrow["PayDate"]." </TD><TD>£".$strmoney." </TD>"; echo "<TD><center><a href=\"edit_order.php?id=".$myrow['id']."\"><img class=\"displayed\" alt=\"View\" src=\"images/edit.png\" width=\"175\" height=\"25\" /></a></center></TD>"; echo "</TR>"; $totmoney = $totmoney + (int)$myrow["Money"]; } ?> <tr> <td> </td> <td></td> <td></td> <td></td> <td></td> </tr> <tr> <td> </td> <td>TOTAL DUE</td> <td>£<?=number_format($totmoney, 2);?> </td> <td></td> <td></td> </tr> </table> <br /> <?php $sql = "SELECT * FROM orders Where orderby= '$username' AND Status='Layon'"; $data = $con->query($sql); $records = $data->num_rows; $records_pages = $records/4; $records_pages = ceil($records_pages); $prev = $page-1; $next = $page+1; echo '<ul class="paganation">'; if($prev >= 5){ echo '<li><a href="?page=1">First Page</a></li>'; } if($prev >=1){ echo '<li><a href="?page='.$prev.'">Previous</a></li>'; } if($records_pages >= 2 ){ for($r=1; $r <= $records_pages; $r++){ $active = $r == $page ? 'class="active"' : ''; echo '<li><a href="?page='.$r.'" '.$active.'>'.$r.'</a></li>'; } } if($next <= $records_pages && $records_pages >= 2){ echo '<li><a href="?page='.$next.'">Next</a></li>'; } if($page != $records_pages && $records_pages >= 5){ echo '<li><a href="?page='.$records_pages.'">Last Page</a></li>'; } echo '</ul>'; ?> <?php } ?> </div> <?php include('styles/bottom.php'); ?> Quote Link to comment Share on other sites More sharing options...
Barand Posted October 26 Share Posted October 26 Always store dates in yyyy-mm-dd format. Unlike your format, this format is sortable. In addition, use a DATE or DATETIME type column to enable the use of the many excellent datetime functions. There is a workaround - STR_TO_DATE() function will allow reformatting of the date string SELECT STR_TO_DATE('15/09/2024', '%d/%m/%Y') as reformatted; +-------------+ | reformatted | +-------------+ | 2024-09-15 | +-------------+ So you can... select * from test_92; +----+------------+ | id | paydate | +----+------------+ | 1 | 29/10/2024 | | 2 | 27/10/2024 | | 3 | 15/09/2024 | +----+------------+ SELECT id -> , paydate -> FROM test_92 -> ORDER BY STR_TO_DATE(paydate, '%d/%m/%Y'); +----+------------+ | id | paydate | +----+------------+ | 3 | 15/09/2024 | | 2 | 27/10/2024 | | 1 | 29/10/2024 | +----+------------+ Quote Link to comment Share on other sites More sharing options...
Mark2024 Posted October 30 Author Share Posted October 30 Hello, Im using the code below to display all my records what im trying to do it display them in order by date the only issue i can think of is that im using the field in mysqli as a varchar(50) to sotore the date like 29/04/1984 does anyone have any ideas to help solve this please? <?php include('styles/top.php'); ?> <div id="full"> <div id="view_box"> <ul> <li><img src="images/1.png" /></li> <!-- <li><img src="pics/2.jpg" /></li> --> <!-- <li><img src="pics/3.jpg" /></li> --> </ul> </div> <div id="button"> <ul> <!-- <li><button class="button" scroll_value="0">*</button></li> -- > <!-- <li><button class="button" scroll_value="600">*</button></li> --> <!-- <li><button class="button" scroll_value="1200">*</button></li> --> </ul> </div> <hr /><br /> <?php //error_reporting(0); if (($user_level !=1) && ($user_level !=2)){ echo "No Access Allowed"; } else { if(isset($_GET['page'])){ $page = $_GET['page']; } else { $page = 1; } if($page == '' || $page == 1){ $page1 = 0; } else { $page1 = ($page*4)-4; } $sql = "SELECT * FROM orders Where customer_name= '$customer_name' AND Status='Layon' LIMIT ".$page1.", 4"; //$sql = 'SELECT * FROM orders LIMIT '.$page1.', 4'; $data = $con->query($sql); ?> <h3>View Orders</h3> <table border=1"> <tr> <th>FULL NAME </th> <th>DATE GOT </th> <th>PAY DATE</th> <th>MONEY DUE</th> </tr> <tr> <td> </td> <td></td> <td></td> <td></td> </tr> <?php $totmoney = 0; $strmoney = ""; while ($myrow = mysqli_fetch_array($data)) { $strmoney = $myrow["money"]; $strmoney = str_replace(',','',$strmoney); if(is_numeric($strmoney)) { settype($strmoney, "float"); $strmoney = number_format($strmoney, 2); } echo "<TR><TD><a href=\"_view.php?id=".$myrow['id']."\">".$myrow["customer_name"]."</A> </TD><TD>".$myrow["gotdate"]." </TD><TD>".$myrow["paydate"]." </TD><TD>£".$strmoney." </TD>"; echo "</TR>"; $totmoney = $totmoney + (int)$myrow["money"]; } ?> <tr> <td> </td> <td></td> <td></td> <td></td> </tr> <tr> <td> </td> <td>TOTAL DUE</td> <td>£<?=number_format($totmoney, 2);?> </td> <td></td> </tr> </table> <br /> <?php $sql = "SELECT * FROM orders Where orderby= '$username' AND Status='Layon'"; $data = $con->query($sql); $records = $data->num_rows; $records_pages = $records/4; $records_pages = ceil($records_pages); $prev = $page-1; $next = $page+1; echo '<ul class="paganation">'; if($prev >= 5){ echo '<li><a href="?page=1">First Page</a></li>'; } if($prev >=1){ echo '<li><a href="?page='.$prev.'">Previous</a></li>'; } if($records_pages >= 2 ){ for($r=1; $r <= $records_pages; $r++){ $active = $r == $page ? 'class="active"' : ''; echo '<li><a href="?page='.$r.'" '.$active.'>'.$r.'</a></li>'; } } if($next <= $records_pages && $records_pages >= 2){ echo '<li><a href="?page='.$next.'">Next</a></li>'; } if($page != $records_pages && $records_pages >= 5){ echo '<li><a href="?page='.$records_pages.'">Last Page</a></li>'; } echo '</ul>'; ?> <?php } ?> </div> <?php include('styles/bottom.php') ?> Quote Link to comment Share on other sites More sharing options...
dodgeitorelse3 Posted October 30 Share Posted October 30 (edited) Barand has already posted how to solve it. Edited October 30 by dodgeitorelse3 Typo Quote Link to comment Share on other sites More sharing options...
Phi11W Posted Friday at 01:58 PM Share Posted Friday at 01:58 PM On 10/30/2024 at 8:12 PM, Mark2024 said: ... the only issue I can think of is that I'm using the field in mysqli as a varchar(50) to store the date like 29/04/1984 does anyone have any ideas to help solve this please? Store date values in Date fields. MySQL knows how to do "date things" with Date fields, including sorting them. It doesn't know how to do "date things" with Varchar fields. Convert your data [once], store it correctly, and wave goodbye to [almost] all your date-related problems. Regards, Phill W. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.