PHP, MySQL date calculations

Question

I'm trying to add a number of weeks to date, both will be taken from a database, I successfully fetch both needed but I can't figure out how to make the calculations. So far I tried with "strtotime" but it gave me some weird results

    <?php 

$servername = "localhost"; 
$username = "root"; 
$password = "password"; 
$dbname = "database";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}
    
    $sql = "SELECT startdate  // <- DATE WE WANT TO ADD WEEKS TO
            FROM database
            ORDER BY id 
            DESC limit 1";
    $result = $conn->query($sql);

    if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
      $recoredDate = $row["startdate"];
    

      
    $sql2 = "SELECT ready // <- INT NUMBER REPRESENT NUMBER OF WEEKS
            FROM crop
            ORDER BY id 
            DESC limit 1";
    $result2 = $conn->query($sql2);

    if ($result2->num_rows > 0) {
    // output data of each row
    while($row = $result2->fetch_assoc()) {
      $weeks = $row["ready"];

      }
    } else {
      echo "No Result";
    }
?>

 <p>
<div align=center>

<?php echo "$recoredDate";?> - Date from DB
<p>
<?php echo "$weeks";?> Weeks to add
<p>
<?php echo "$recoredDate",strtotime ("+ $weeks week");?> Wierd result 
</div>
</p>


<?php
    }
    }
?>

How do I do this the right way? The desired result should be printing the $recoredDate + $weeks.

Ivan Gajic · Accepted Answer · 2021-01-23 18:10:49Z

1

Try with

$str = strtotime("+".$weeks." weeks", strtotime($recoredDate));
echo date("Y-m-d", $str);

Without second parameter (which is also timestamp), strtotime will return current timestamp with, in your example, added x weeks. If you want to add weeks to a date, and then use date in some format, first you must add weeks to a timestamp value of a date, and then format it with date() function.

edited Jan 23, 2021 at 18:10

answered Jan 23, 2021 at 18:06

Ivan Gajic

4864 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tom Lima · Accepted Answer · 2021-01-23 19:20:29Z

1

You can first turn weeks into days. Taking into account a week of 7 days you would have something like this:

<?php
  $date = "2019-05-10";  // Your date from database
  $weeks = 3; // Your number of weeks from database
  $days = $weeks * 7; // Transform weeks to days

  $newDate = date('Y-m-d', strtotime($date. ' + '.$days.' days')); 
  echo $newDate;
 ?>

Additionally, I would recommend that you avoid placing an SQL query inside a while loop. It is bad practice.

answered Jan 23, 2021 at 19:20

Tom Lima

1,0661 gold badge10 silver badges26 bronze badges

1 Comment

Inna Ivanova Over a year ago

Thank You Tom Lima, great example! I already used the code above since has fewer lines but thank you! I hope this will be a great example for others too!

Collectives™ on Stack Overflow

PHP, MySQL date calculations

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related