Date Help

Mark2024 · 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>&nbsp;</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>&nbsp;</TD><TD>".$myrow["GotDate"]."&nbsp;</TD><TD>".$myrow["PayDate"]."&nbsp;</TD><TD>&pound;".$strmoney."&nbsp;</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>&nbsp;</td>
						<td></td>
						<td></td>
                        <td></td>
                        <td></td>
					</tr>
                
                <tr>
						<td>&nbsp;</td>
						<td>TOTAL DUE</td>
						<td>&pound;<?=number_format($totmoney, 2);?>&nbsp;</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'); ?>

Barand · 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 |
+----+------------+

Mark2024 · 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>&nbsp;</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>&nbsp;</TD><TD>".$myrow["gotdate"]."&nbsp;</TD><TD>".$myrow["paydate"]."&nbsp;</TD><TD>&pound;".$strmoney."&nbsp;</TD>";
    				
                    echo "</TR>";
                    
                    $totmoney = $totmoney + (int)$myrow["money"];
                }
                
                
                ?>
                
                
                   <tr>
						<td>&nbsp;</td>
						<td></td>
						<td></td>
                        <td></td>
					</tr>
                
                <tr>
						<td>&nbsp;</td>
						<td>TOTAL DUE</td>
						<td>&pound;<?=number_format($totmoney, 2);?>&nbsp;</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') ?>

dodgeitorelse3 · October 30

Barand has already posted how to solve it.

Edited October 30 by dodgeitorelse3
Typo

Phi11W · November 1

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.

Sign In

Date Help

Recommended Posts

Mark2024

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Mark2024

Link to comment

Share on other sites

dodgeitorelse3

Link to comment

Share on other sites

Phi11W

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information