0

I need some help with this one please.

I am trying to return only the rows from the mysql table where the difference between today's date and 'next_due' is 7 days or more. The code I have so far is...

  $stmt = $conn->prepare("SELECT * FROM activities WHERE userId = ? AND status = ? ");
  $stmt->bind_param("ss", $userId, $status);
  $stmt->execute();
  $result = $stmt->get_result();
    if($result->num_rows === 0) echo "zero";
    else {
        while($row = mysqli_fetch_array($result)) {
          $activity_id          =   $row['id'];
          $title                =   $row['title'];
          $next_due             =   $row['next_due'];

          $next_due = strtotime($next_due);
          $next_due = date("d/m/Y", $next_due);
        }
      }

This returns all rows where the $status is set to 'open', but I only want to return those rows where the difference between today's date and the date entered in 'next_due' is greater than 7 days.

Help is appreciated

Richard Summerfield
  • 7
  • 2

1 Answers1

0

You can research about a mysql function called datediff(). It returns the difference between two dates in days.

https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_datediff

  • Hi Junior. Thanks for the advice I’ve read about datediff, but from the examples given on dev.mysql.com it is unclear how I would use datediff to return the information I’m after from my table. – Richard Summerfield Mar 11 '19 at 06:34
  • You could do something like: SELECT * FROM activities WHERE userId = ? AND status = ? and DATEDIFF(next_due,'$today') > 7 The variable $today contains the current data with format YYYY-MM-DD and next_due is a table field. –  Mar 11 '19 at 11:03
  • It is possible to use DATEDIFF(next_due,'$today') >= 7 It's not a good practice to use Select * from . Take a look at these tips: https://stackoverflow.com/questions/3639861/why-is-select-considered-harmful
     –  Mar 11 '19 at 11:13
  • Thanks Junior. I didn't realize the DATEDIFF was that simple. I'll try it and let you know. Thanks for the guidance on using * too. – Richard Summerfield Mar 11 '19 at 16:11