Calculating start / end dates

I'm trying to find a solution to a problem and I'm looking for some input

I've got an event schedule (using php/Mysql) where I'd select a start date and end date for an event and the main page will display events starting in the selected month while not displaying events for the month that had expired

In other words you won't see events for 01/03/2018 on 01/05/2018 because they had expired

mysqli_query ($con, "SELECT title, date, end_date, status FROM events WHERE date LIKE '$chkdate1' and end_date >= '$chkdate2' and status = '1' order by date asc");

Open in new window


Everything works fine, no problems except for one thing that I can't figure out a solution for except to do what I'm looking to do manually

As I said everything works fine, except for when date ranges extend past the current month
If I set an event to start on 01/18/2018 and end on 04/22/2018 the event displays for January, but not February, March or April

I understand why it's not, so here's my question

If start_date = 01/18/2018 and end_date = 03/22/2018 would it be possible to get the following

start_date - 01/18/2018 end_date - 01/last-day-of-month/2018
start_date - 02/01/2018 end_date - 02/last-day-of-month/2018
start_date - 03/01/2018 end_date - 03/22/2018

to be inserted into the database

I'm currently making it happen by manually entering separate events for each month I need but it's a real PITA
sometimes

Ken
LVL 14
kenfcampAsked:
Who is Participating?
 
Julian HansenConnect With a Mentor Commented:
Please can you post your code for the test you did. Inserting a bunch of dates for each event just so you can do a query seems like a really bad solution to me - I might not be understanding you 100% so I might not be hitting the target on this one.
I can't comment on your results unless I see your code

Here is what works for me

<?php
$year = isset($_REQUEST['year']) ? $_REQUEST['year'] : date("Y");
$month = isset($_REQUEST['month']) ? $_REQUEST['month'] : date("m");

$chkdate1 = "{$year}-{$month}-01";
$chkdate2 = date("Y-m-t", strtotime($chkdate1));

$query = <<< QUERY
SELECT
	`title`, 
	`date`, 
	`end_date`,
	`status`
FROM
	`events`
WHERE
	`date` <= '{$chkdate1}' AND
	`end_date` >= '{$chkdate2}' AND
	`status` = '2'
ORDER BY
	`date` asc
QUERY;	

Open in new window


Going through the code above.

The first two lines just retrieve the search values or default to current year and month if they don't exist.
NOTE: I have gone with your use of REQUEST but consider using GET instead.

Lines 5 and 6 are the key ones. Here we are constructing a date range to test for.
Line 5 creates the start of the month date using 01 for the day
Line 6 uses the date function with 't' for the day (last day of the month) to create a date representing the last day of the month.

The query now looks for all events that have a start date before chkdate1 and an end date after chkdate2.

The code is predicated on the assumption that you are using a DATE field type. Your use of LIKE in your query suggests you may not - I would change it to use DATE if that is the case.
0
 
Julian HansenCommented:
Why do you have this in your query
 date LIKE '$chkdate1' 

Open 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'

Open in new window

0
 
kenfcampAuthor Commented:
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)
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Julian HansenCommented:
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?
0
 
kenfcampAuthor Commented:
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
0
 
Julian HansenCommented:
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'

Open in new window


The above says show me all events that started before today but end after today.
0
 
kenfcampAuthor Commented:
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
0
 
kenfcampAuthor Commented:
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";

}
0
 
Julian HansenCommented:
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'"

Open in new window

0
 
kenfcampAuthor Commented:
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" 

Open 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");

Open 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
0
 
kenfcampAuthor Commented:
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
0
 
Julian HansenCommented:
Then simply change the filter to

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

Open 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
1
 
kenfcampAuthor Commented:
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
0
 
kenfcampAuthor Commented:
hold on.. I think I've got something
0
 
kenfcampAuthor Commented:
Better solution offered
0
 
kenfcampAuthor Commented:
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
0
 
Julian HansenCommented:
You are welcome.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.