Calculating start / end dates

Why do you have this in your query

 date LIKE '$chkdate1' 

Select allOpen in new window

A valid event is one where the end date is in the future relative to today correct?

So why not just search for all events where the start date is less than today and the end date is greater than today

WHERE date <= NOW() AND end_date >= '$chkdate1'

Select allOpen in new window

Julian,

if (!isset($_REQUEST["month"])) $_REQUEST["month"] = date("m");
if (!isset($_REQUEST["year"])) $_REQUEST["year"] = date("Y");

$chkdate1 = "$_REQUEST[year]-$_REQUEST[month]-%";
$chkdate2 = date("Y-m-d");
 "date <= NOW( )" limits results to the current month, where as "$chkdate1" allows me to view events starting in the current month as well as any selected month.

Granted, NOW() fixes the issue for events starting in a previous month, and ending in the current month, but it doesn't resolve issues with viewing events for (example) for March that start in January or February

If I select a particular month, I only want to see events that start and/or end in that month (current month or selected)

So let's say date is
2018-25-02

And event start is 2018-01-01 and ends 2018-03-25

This event neither starts nor ends in February so does that mean it must not be visible when the viewing date is in Feb?

Right,

In that scenario I'd want to see it in January (Since it started and would need to be seen in January), February and in March

The only way I can see it happening, is when the event is saved creating multiple events

Event1 Start: 2018-01-01 End: 2018-03-25
Event1 Start: 2018-02-01 End: 2018-03-25
Event1 Start: 2018-03-01 End: 2018-03-25

Then I don't understand.

If your start date is before today and the end date is after today then you are saying you want to see it - which is what my post above was trying to achieve (assumes date is start date and end_date is end date)

WHERE date <= NOW() AND end_date >= '$chkdate1'

Select allOpen in new window

The above says show me all events that started before today but end after today.

Let me see if this helps

Same data your method vs what I'm using

Viewing with now()

January 2018 Events
Start Date        End Date
2017-11-15        2017-11-30
2017-12-06        2017-12-19
2018-01-01        2018-01-31

February 2018 Events
Start Date        End Date
2017-11-15        2017-11-30
2017-12-06        2017-12-19
2018-01-01       2018-01-31

Viewing January with out "now()"

January 2018 Events
Start Date       End Date
2018-01-01       2018-01-31
2018-01-08       2018-03-09

February 2018 Events
Start Date       End Date
2018-02-01       2018-03-09

I'm sure you'd agree that viewing events from November and December 2017 while in February 2018 isn't likely to be desirable

I was able to accomplish my goal by adjusting the following to meet my needs and incorporating it into my existing code

$start=date_create("2018-01-08");
$end=date_create("2018-04-02");

$interval = DateInterval::createFromDateString('1 month');
$period   = new DatePeriod($start, $interval, $end);

foreach ($period as $dt) {

    echo $dt->format("Y-m") . "\n";

}

I think we are at cross purposes here. I was working on the principle that for the given date you want to see what events are active.

From your example above you are wanting events for a specific month. In otherwords

// Start date of target month
$chckstart = date('Y-m-01', strtotime($chkdate1));
// End date of target month
$chkend= date('Y-m-t', strtotime($chkdate1));
// Find all events that start before the start of the target month and end on or after the last day of that month
$query = "SELECT title, date, end_date, status FROM events WHERE date <= '$chckstart' AND end_date >= '$chkend'"

Select allOpen in new window

Good morning Julian

I do appreciate your tenacity, and I have tried your solution

I've created a copy of the database being used and inserted start date 01-08-2018 end date 05-22-2018 and nothing displays for January, February, March, April, or May. Inserting start date 01-08-2018 end date 01-22-2018 resulted in the same

I've spent weeks trying to make this work before posting this question, by modifying the search results and have come to the conclusion the answer (or easiest way) would be to modify how the dates are being added to the database

To explain the code sample I provided as a solution, I'm not using this for result sorting, I'm using it to calculate dates for insertion

For example:

If I add start date - 01-08-2018 / end date - 06-22-2018

It calculates the ranges
start date 01-08-2018 / end date - 06-22-2018
start date 02-01-2018 / end date - 06-22-2018
start date 03-01-2018 / end date - 06-22-2018
start date 04-01-2018 / end date - 06-22-2018
start date 05-01-2018 / end date - 06-22-2018
start date 06-01-2018 / end date - 06-22-2018
and adds them to the database giving me a result for each month involved in the event

My query

SELECT title, date, end_date, status FROM events WHERE date LIKE '$chkdate1' and end_date >= '$chkdate2' and status = '2' order by date asc" 

Select allOpen in new window

uses $chkdate1 and $chkdate2

if (!isset($_REQUEST["month"])) $_REQUEST["month"] = date("m");
if (!isset($_REQUEST["year"])) $_REQUEST["year"] = date("Y");

$chkdate1 = "$_REQUEST[year]-$_REQUEST[month]-%";
$chkdate2 = date("Y-m-d");

Select allOpen in new window

to search for events for the selected or current month that has an end date greater or equal to the current date. Giving me results for the given month that haven't expired

There's more being done and inserted, but that's the meat of it

Ken

It does to an extent, but it doesn't

Three events created

Start Date       End Date
2017-12-11       2018-02-06
2018-01-15       2018-01-24
2018-01-01       2018-03-08

Viewing January you see
2017-12-11       2018-02-06
2018-01-01       2018-03-08

When you should see all three

Viewing February you see
2018-01-01       2018-03-08

When you should see
2017-12-11       2018-02-06
2018-01-01       2018-03-08

Viewing March you see
no events found

When you should see
2018-01-01       2018-03-08

I've beaten this to death on my own, and I haven't found an option that worked aside from inserting multiple dates

Then simply change the filter to

WHERE date <= '$chkdate2' AND end_date >= '$chkdate1'

Select allOpen in new window

By swapping them around we get all events that either terminate after the start of Jan but start before the end of Jan

Yes, that will provide the dates, but it also carries a side effect of also displaying expired events which I don't want

An event ending on 01-04-2018 (for example) shouldn't be seen on 01-08-2018

hold on.. I think I've got something

Better solution offered

Julian, you're the best

Thank you for hanging in there

Your code with the secondary change works great. All I needed to do to eliminate the expired events was insert a condition to display results if the end_date was >= the current date

This is so much better than adding a bunch of date ranges

Thanks again!

Ken

You are welcome.