CODEWITHSUNDEEP
phpmysql
user3584968
user3584968

Reputation: 353

MYSQL/PHP how to minus 7 days from date?

I am using a system that tells me how many days out of 7 days it has taken for a user to re-submit data in a form to the database. The below script measures how many days have past from the original start date / when the form was originally filled out to when it was completed.

Basically, I am trying to echo 2 different measures, if the days that have past from the start date are less than 7, then it should echo out (number of days) out of 7 past

i.e. 1 day of 7 past or 4 days of 7 past

and if it has gone over 7days, it should then say (number of days) overdue. The problem I am getting is that I am allowing my users 7 days to submit the form. Bearing this in mind, they are not overdue therefore until it has gone past the 7 days timeframe, therefore if a user takes 8 days to complete the form they should only be one day overdue, however my script currently says 8 days overdue, as it takes into consideration the earlier 7.

Is there a way I can minus 7 days to show once a user goes over the 7 day time frame?

<?php include 'config.php';
     $data = mysql_query("SELECT *, TIMESTAMPDIFF(DAY, date, CURDATE()) AS expire_date FROM supplier_session ORDER BY expire_date ASC") 
     or die(mysql_error()); 

     echo "<table class=\"table\" style=\"width:995px;  font-family: 'Lucida Grande', Tahoma, Verdana, Arial, sans-serif;
     font-size:11px;\" >

<tr>
    <td style=\"width:100px;\">ID:</td><td>Company Name:</td><td>Company Reg No:</td><td>Application Started:</td><td style=\"width:200px;\">Application Duration:</td><td style=\"width:100px;\">Date:</td><td>Status:</td></tr>";


     while($row = mysql_fetch_array( $data )) { 
        $days = $row['expire_date'];
        $when = $days*0; 
        $str = $row['expire_date'];
        $str2 = substr($str, 0); // "quick brown fox jumps over the lazy dog."
       if ($when <= 31){
       echo "<tr><td style=\"width:100px;\"><p>".$row['id'] . "</p></td>"; 
       echo "<td style=\"width:150px;\"><p>".$row['company_name'] . "</p></td>"; 
       echo "<td style=\"width:150px;\"><p>".$row['company_reg_number'] . "</p></td>";
       echo "<td>"; echo date('d/m/Y',strtotime($row['date'])); echo "</td>";
       
      
       if ($days >= 8) {
            echo "<td style=\"width:200px;\"><p>{$str2} days overdue</td>";
       }
       
        elseif ($when <= 7){
            
        
             echo "<td style=\"width:200px;\"><p>{$str2} of 7 days past</td>";
        }
        
        

          
        }
     

        echo "<tr>";
      }
     
    
      echo "</table>"; //Close the table in HTML
      
     
    ?>

Upvotes: 22

Views: 61798

Answers (5)

Dharman
Dharman

Reputation: 33238

You can use strtotime() but a better option is to use DateTime class. It's easier to use and offers more.

A compact way of subtracting 7 days:

$date = '2020-02-03';
echo date_create($date)
    ->modify('-7 days')
    ->format('Y-m-d');

This is compacted into chained methods, but you can create an instance of the class on its own and perform actions on the object. 

$dateObject = new \DateTime($date);
$dateObject->modify ('-7 days');

The same can be done in SQL, so if you have no reason to do it in PHP you should do it in MySQL.

Upvotes: 5

Sayeed roshan
Sayeed roshan

Reputation: 179

Tested , and its working fine You can Subtract date in php as below.

$test_date = "2020-02-02";
date("Y-m-d", strtotime("-2 days", strtotime($test_date)));

Upvotes: -1

user1805543
user1805543

Reputation:

Given answers are just calculating for the year, month and day which is not calculating for hours, minutes, etc..

So, if you updated your database last night at 23:59 then it will show 1 day past after 2 minutes at 00:01.

Correct answer of this question should be formated like this

date('Y-m-d H:i:s'); will give us year, month, day, hour, minute and second of the current date Now or expire_date.

$expire_date = '2020-01-23 10:12:13'; //Output of the date we mentioned above 
$a = date('Y-m-d H:i:s', strtotime('-7 days', strtotime($expire_date)));
echo $a; //Correct result of 7 days past, 2020-01-16 10:12:13

Upvotes: 1

Dishant Bhatt
Dishant Bhatt

Reputation: 111

$date = $row['expire_date']; //date from database 
$str2 = date('Y-m-d', strtotime('-7 days', strtotime($date)));

Upvotes: 11

dev4092
dev4092

Reputation: 2891

If you want 7 days back from today you can use this:

date('Y-m-d', strtotime('-7 days'))

Upvotes: 53

Related Questions