CODEWITHSUNDEEP
phpmysqlcodeigniterdate
user3574766
user3574766

Reputation: 631

Select from database where date is less than a supplied date

I'm trying to pull rows from a database where the date is within two weeks of the current date, I'm using CodeIgniter and this is my model:

function GetToDoItems($userID)
{
  $range = date('Y-m-d', strtotime("+2 weeks"));
  $query = $this->db->query("SELECT * FROM FYP_Milestones
                            INNER JOIN FYP_Modules ON FYP_Milestones.ModuleID = FYP_Modules.ID
                            WHERE FYP_Milestones.MilestoneDue < $range
                            ORDER BY FYP_Milestones.MilestoneDue ASC
                            ");
  return $query->result();
}

It runs this query:

SELECT * FROM FYP_Milestones INNER JOIN FYP_Modules ON FYP_Milestones.ModuleID = FYP_Modules.ID WHERE FYP_Milestones.MilestoneDue < 2016-04-14 ORDER BY FYP_Milestones.MilestoneDue ASC

I have a row in the database like so: enter image description here

Considering 2016-04-07 is less than 2016-04-14 by 7 days I'm expecting that row alone to be pulled, but SQL is returning an empty result , why is this the case?

Upvotes: 0

Views: 285

Answers (2)

Sankar V
Sankar V

Reputation: 4128

Add single quotes to the date value like:

$query = $this->db->query("SELECT * FROM FYP_Milestones
                        INNER JOIN FYP_Modules ON FYP_Milestones.ModuleID = FYP_Modules.ID
                        WHERE FYP_Milestones.MilestoneDue < '".$range."'
                        ORDER BY FYP_Milestones.MilestoneDue ASC
                        ");

Upvotes: 1

Garytje
Garytje

Reputation: 874

You actually don't need PHP for the range, use SQL:

WHERE FYP_Milestones.MilestoneDue < (CURDATE() + INTERVAL 2 WEEK)

Upvotes: 5

Related Questions