Jump to content

Date Help

Mark2024

By Mark2024
in PHP Coding Help

 Share

Recommended Posts

Mark2024 Newbie

Mark2024

Posted
Posted

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'); ?>

 

Link to comment
https://forums.phpfreaks.com/topic/325220-date-help/
Share on other sites
Barand Prolific Member

Barand

Posted
Posted

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

 

Link to comment
https://forums.phpfreaks.com/topic/325220-date-help/#findComment-1638885
Share on other sites
Mark2024 Newbie

Mark2024

Posted
  • Author
Posted

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') ?>

 

Link to comment
https://forums.phpfreaks.com/topic/325220-date-help/#findComment-1639077
Share on other sites
Phi11W Advanced Member

Phi11W

Posted
Posted
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. 

Link to comment
https://forums.phpfreaks.com/topic/325220-date-help/#findComment-1639181
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.