Date filter function with SQL doesn’t work

I have a website where I load various activities who all have a specific date. I want to filter on those dates.

I do that with this query:

function selectAllActivities($date = false){
    $sql = "SELECT * FROM `activities`
            INNER JOIN `locations` on `activities`.`location_id` = `locations`.`id`";
    if (!empty($date)){
        $sql .=  " WHERE `activities`.`start` >= :date AND `activities`.`start` < :next_day";
    }

    $stmt = $this->pdo->prepare($sql);
    $stmt->bindValue(':date', strtotime($date));
    $stmt->bindValue(':next_day', strtotime($date.'+1 day'));
    $stmt->execute();
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

If there is no date given, the page will load all the activities, but if someone wants to select a specific date to see which activities there are on that date, I use this code:

public function index() {
    $activityDAO = new ActivityDAO();

    if (!empty($_GET['date'])) {
        $activities = $activityDAO->selectAllActivities($_GET['date']);
    } else {
        $activities = $activityDAO->selectAllActivities();
    }
    $this->set('activities',$activities);
}

The page loading of all the activities is working fine, but if I select a specific date, the array with activities stays empty and no activities will load.

Answer

Since the column is Datetime , in your sql , you are injecting timestamp value , so that cause that yor result is always empty .

Instead , you can convert Date given to datetime with the same format ‘Y-m-d-h-m’ using gmdate:

 function selectAllActivities($date = false){
        $sql = "SELECT * FROM `activities`
                INNER JOIN `locations` on `activities`.`location_id` = `locations`.`id`";
        if (!empty($date)){
            $sql .=  " WHERE `activities`.`start` >= :date AND `activities`.`start` < :next_day";
        }
        $dateTime = gmdate("Y-m-d-h-m", strtotime($date));
        $dateTimeTomorrow=gmdate("Y-m-d-h-m", strtotime($date.'+1 day'));
        $stmt = $this->pdo->prepare($sql);
        $stmt->bindValue(':date', $dateTime);
        $stmt->bindValue(':next_day', $dateTimeTomorrow);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }