If Statement based on Date
6 posts by 3 authors in: Forums > CMS Builder
Last Post: March 16, 2011 (RSS)
By zip222 - August 10, 2010
I have a section that uses the date field and I would like to divide up the listings on my page by items that are in the future and items that are in the past - based on the current date.
if the date of item is in the future, appear in list 1
If the date of item is in the past, appear in list 2
Here is the viewer code I am currently using, which only creates one list:
if the date of item is in the future, appear in list 1
If the date of item is in the past, appear in list 2
Here is the viewer code I am currently using, which only creates one list:
list($meetingsRecords, $meetingsMetaData) = getRecords(array(
'tableName' => 'meetings',
'loadUploads' => '0',
'allowSearch' => '0',
));<table class="listings">
<tr>
<th>Upcoming Meetings</th>
</tr>
<?php foreach ($meetingsRecords as $record): ?>
<tr>
<td>
<a href="<?php echo $record['_link'] ?>"><?php echo date("l, F j, Y / g:i a", strtotime($record['meeting_date'])) ?></a>
</td>
</tr>
<?php endforeach ?>
</table>Re: [zip222] If Statement based on Date
By zip222 - August 10, 2010
Nevermind, I figured it out. And in case anyone needs to know...
Just need to add this line:
or this...
Just need to add this line:
'where' => "meeting_date > NOW()"or this...
'where' => "meeting_date < NOW()"Re: [zip222] If Statement based on Date
By zip222 - March 16, 2011
How do I modify this where statement to include records with a meeting_date that is either equal to or after today.
basically this, which isn't working...
'where' => "meeting_date > NOW()"basically this, which isn't working...
'where' => "meeting_date > NOW() OR meeting_date = NOW()"Re: [zip222] If Statement based on Date
By Jason - March 16, 2011
Hi,
The MySQL NOW() statements give you the current time stamp to the second. If you're just interested in the day of the meeting (ie, the time of the meeting doesn't matter, you can try something like this:
First, get the current date formatted properly:
Then set up your WHERE clause like this:
Hope this helps
The MySQL NOW() statements give you the current time stamp to the second. If you're just interested in the day of the meeting (ie, the time of the meeting doesn't matter, you can try something like this:
First, get the current date formatted properly:
$currentDate = date("Y-m-d");Then set up your WHERE clause like this:
'where' => "meeting_date LIKE '$currentDate%' ",Hope this helps
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com
Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/
Jason Sauchuk - Project Manager
interactivetools.com
Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/
Re: [Jason] If Statement based on Date
By zip222 - March 16, 2011
so, not exactly...
What I am trying to do is generate a list of upcoming meetings and once a meeting date has passed I want it to no longer appear. But if there is a meeting happening today I do want it to appear. But at the moment they don't appear. I assume this is due to the fact that NOW() is based on the current time to the second and I am not inputting the time for my meetings.
I am looking for something like this...
What I am trying to do is generate a list of upcoming meetings and once a meeting date has passed I want it to no longer appear. But if there is a meeting happening today I do want it to appear. But at the moment they don't appear. I assume this is due to the fact that NOW() is based on the current time to the second and I am not inputting the time for my meetings.
I am looking for something like this...
'where' => "meeting_date > NOW() + 1 DAY",Re: [zip222] If Statement based on Date
By gkornbluth - March 16, 2011
Hi Zip 222,
I've documented a number of these types of scenarios in the "Working With Dates" section of my CMSB Cookbook http://www.thecmsbcookbook.com
Here's one of the recipes that may help:
(It's long...)
Best,
Jerry Kornbluth
COMPARING DATES
When combined with the “if” statement, this extremely useful concept can be used to show or hide fields based on a particular date, automatically archive records after a specified time period and perform many other functions.
Here’s the basic idea of how to compare a date field to today’s date on a list page. This example uses a date field called “opening_reception” in a multi record editor called “exhibitions”. The goal was to automatically hide the opening reception date after the date had passed.
First you’ll need a “foreach” statement to display each record.
_____ code ________________________________________________
<?php foreach ($exhibitionsRecords as $record): ?>
__________________________________________________________
Since dates are normally expressed as months, days and years are hard to compare mathematically, the dates are converted to the number of seconds since the Unix Epoch (January 1 1970 00:00:00 GMT)
Note that dates are referenced to local time as set in the CMSB “General Settings” information and not server time.
Dave suggested to first define a few variables and to use simple, descriptive names so that the process is easier to follow. Then set some rules for the comparisons. You can use mathematical operators like <, >, <= or >= between values to compare them in different ways.
_____ code ________________________________________________
<?php
$eventUnixTime = strtotime( $record['reception_date'] ); // seconds since 1970
$eventDateString = date("l, F jS", $eventUnixTime); // example format: Monday, June 1st
$currentUnixTime = time();
$currentDateString = date("l, F jS", $currentUnixTime);
$isEventToday = ($eventDateString == $currentDateString); // first comparison
$isEventOver = !$isEventToday && ($eventUnixTime < $currentUnixTime); // second comparison
$isFutureEvent = !$isEventOver && !$isEventToday; // third comparison
?>
__________________________________________________________
Then it becomes a simple matter of comparing the variables
_____ code ________________________________________________
<?php if ($isFutureEvent): ?>
Opening Reception: <?php echo $eventDateString ?>
<?php elseif ($isEventToday): ?>
The Opening reception is today. Don't miss it!!
<?php else: ?>
Sorry, you missed the Opening Reception.
<?php endif; ?>
__________________________________________________________
and don’t forget the endforeach statement to close your loop.
_____ code ________________________________________________
<?php endforeach ?>
__________________________________________________________
Or, Let’s say you want to group a set of meetings by future and past dates
You could use:
_____ code ________________________________________________
<?php foreach ($general_meetingsRecords as $record): ?>
<?php
$eventUnixTime = strtotime( $record['date'] ); // seconds since 1970
$eventDateString = date("l, F jS", $eventUnixTime); // example format: Monday, June 1st
$currentUnixTime = time();
$currentDateString = date("l, F jS", $currentUnixTime);
$isEventToday = ($eventDateString == $currentDateString);
$isEventOver = !$isEventToday && ($eventUnixTime < $currentUnixTime);
$isFutureEvent = !$isEventOver && !$isEventToday;
?>
<?php if ($isEventToday): ?> <p>
<div align="center"class="heading-text-bold">TONIGHT'S MEETING:</div>
</p>
<br /> <span class="body-text-bold"><?php echo date("D, M jS, Y g:i a", strtotime($record['date'])) ?>
</span>
<br />
<div align="left" class="body-text"><?php echo $record['content'] ?></div>
<hr align="left" color="#A29DB2" width="100" /><br />
<?php endif; ?> <?php endforeach ?>
</div>
<p>
<div align="center"class="heading-text-bold">UPCOMING MEETINGS:</div>
</p>
<?php foreach ($general_meetingsRecords as $record): ?>
<?php
$eventUnixTime = strtotime( $record['date'] ); // seconds since 1970
$eventDateString = date("l, F jS", $eventUnixTime); // example format: Monday, June 1st
currentUnixTime = time();
$currentDateString = date("l, F jS", $currentUnixTime);
$isEventToday = ($eventDateString == $currentDateString);
$isEventOver = !$isEventToday && ($eventUnixTime < $currentUnixTime);
$isFutureEvent = !$isEventOver && !$isEventToday;
?>
<?php if ($isFutureEvent): ?>
<br />
<span class="body-text-bold"><?php echo date("D, M jS, Y g:i a", strtotime($record['date'])) ?></span> <br />
<div align="left" class="body-text"> <?php echo $record['content'] ?></div><hr align="left" color="#A29DB2" width="100" />
<?php endif; ?> <?php endforeach ?>
<br />
<p><div align="center" class="heading-text-bold">PAST MEETINGS:</div></p>
<?php foreach ($general_meetingsRecords as $record): ?>
<?php
$eventUnixTime = strtotime( $record['date'] ); // seconds since 1970
$eventDateString = date("l, F jS", $eventUnixTime); // example format: Monday, June 1st
$currentUnixTime = time();
$currentDateString = date("l, F jS", $currentUnixTime);
$isEventToday = ($eventDateString == $currentDateString);
$isEventOver = !$isEventToday && ($eventUnixTime < $currentUnixTime);
$isFutureEvent = !$isEventOver && !$isEventToday;
?>
<?php if ($isEventOver): ?>
<br />
<span class="body-text-bold"><?php echo date("D, M jS, Y g:i a", strtotime($record['date'])) ?></span> <br />
<div align="left" class="body-text"><?php echo $record['content'] ?></div><hr align="left" color="#A29DB2" width="100" />
<?php endif; ?> <?php endforeach ?>
</div>
__________________________________________________________
Note that you have to redefine the variables for each "foreach" loop
If you want to compare other field values, just define more variables and compare those as well.
If you want to test for the values of the variable that you're using. Try something like this in your foreach loop:
_____ code ________________________________________________
<ul>
<li>Event date = <?php echo $eventDateString ?></li>
<li>Current date = <?php echo $currentDateString ?></li>
<li>$isEventToday = <?php echo $isEventToday ? "true" : "false" ?></li>
<li>$isEventOver = <?php echo $isEventOver ? "true" : "false" ?></li>
<li>$isFutureEvent = <?php echo $isFutureEvent ? "true" : "false" ?></li>
<li>$CurrentUnixTime extended = <?php echo date("l jS \of F Y h:i:s A", $currentUnixTime); ?></li>
</ul>
__________________________________________________________
I've documented a number of these types of scenarios in the "Working With Dates" section of my CMSB Cookbook http://www.thecmsbcookbook.com
Here's one of the recipes that may help:
(It's long...)
Best,
Jerry Kornbluth
COMPARING DATES
When combined with the “if” statement, this extremely useful concept can be used to show or hide fields based on a particular date, automatically archive records after a specified time period and perform many other functions.
Here’s the basic idea of how to compare a date field to today’s date on a list page. This example uses a date field called “opening_reception” in a multi record editor called “exhibitions”. The goal was to automatically hide the opening reception date after the date had passed.
First you’ll need a “foreach” statement to display each record.
_____ code ________________________________________________
<?php foreach ($exhibitionsRecords as $record): ?>
__________________________________________________________
Since dates are normally expressed as months, days and years are hard to compare mathematically, the dates are converted to the number of seconds since the Unix Epoch (January 1 1970 00:00:00 GMT)
Note that dates are referenced to local time as set in the CMSB “General Settings” information and not server time.
Dave suggested to first define a few variables and to use simple, descriptive names so that the process is easier to follow. Then set some rules for the comparisons. You can use mathematical operators like <, >, <= or >= between values to compare them in different ways.
_____ code ________________________________________________
<?php
$eventUnixTime = strtotime( $record['reception_date'] ); // seconds since 1970
$eventDateString = date("l, F jS", $eventUnixTime); // example format: Monday, June 1st
$currentUnixTime = time();
$currentDateString = date("l, F jS", $currentUnixTime);
$isEventToday = ($eventDateString == $currentDateString); // first comparison
$isEventOver = !$isEventToday && ($eventUnixTime < $currentUnixTime); // second comparison
$isFutureEvent = !$isEventOver && !$isEventToday; // third comparison
?>
__________________________________________________________
Then it becomes a simple matter of comparing the variables
_____ code ________________________________________________
<?php if ($isFutureEvent): ?>
Opening Reception: <?php echo $eventDateString ?>
<?php elseif ($isEventToday): ?>
The Opening reception is today. Don't miss it!!
<?php else: ?>
Sorry, you missed the Opening Reception.
<?php endif; ?>
__________________________________________________________
and don’t forget the endforeach statement to close your loop.
_____ code ________________________________________________
<?php endforeach ?>
__________________________________________________________
Or, Let’s say you want to group a set of meetings by future and past dates
You could use:
_____ code ________________________________________________
<?php foreach ($general_meetingsRecords as $record): ?>
<?php
$eventUnixTime = strtotime( $record['date'] ); // seconds since 1970
$eventDateString = date("l, F jS", $eventUnixTime); // example format: Monday, June 1st
$currentUnixTime = time();
$currentDateString = date("l, F jS", $currentUnixTime);
$isEventToday = ($eventDateString == $currentDateString);
$isEventOver = !$isEventToday && ($eventUnixTime < $currentUnixTime);
$isFutureEvent = !$isEventOver && !$isEventToday;
?>
<?php if ($isEventToday): ?> <p>
<div align="center"class="heading-text-bold">TONIGHT'S MEETING:</div>
</p>
<br /> <span class="body-text-bold"><?php echo date("D, M jS, Y g:i a", strtotime($record['date'])) ?>
</span>
<br />
<div align="left" class="body-text"><?php echo $record['content'] ?></div>
<hr align="left" color="#A29DB2" width="100" /><br />
<?php endif; ?> <?php endforeach ?>
</div>
<p>
<div align="center"class="heading-text-bold">UPCOMING MEETINGS:</div>
</p>
<?php foreach ($general_meetingsRecords as $record): ?>
<?php
$eventUnixTime = strtotime( $record['date'] ); // seconds since 1970
$eventDateString = date("l, F jS", $eventUnixTime); // example format: Monday, June 1st
currentUnixTime = time();
$currentDateString = date("l, F jS", $currentUnixTime);
$isEventToday = ($eventDateString == $currentDateString);
$isEventOver = !$isEventToday && ($eventUnixTime < $currentUnixTime);
$isFutureEvent = !$isEventOver && !$isEventToday;
?>
<?php if ($isFutureEvent): ?>
<br />
<span class="body-text-bold"><?php echo date("D, M jS, Y g:i a", strtotime($record['date'])) ?></span> <br />
<div align="left" class="body-text"> <?php echo $record['content'] ?></div><hr align="left" color="#A29DB2" width="100" />
<?php endif; ?> <?php endforeach ?>
<br />
<p><div align="center" class="heading-text-bold">PAST MEETINGS:</div></p>
<?php foreach ($general_meetingsRecords as $record): ?>
<?php
$eventUnixTime = strtotime( $record['date'] ); // seconds since 1970
$eventDateString = date("l, F jS", $eventUnixTime); // example format: Monday, June 1st
$currentUnixTime = time();
$currentDateString = date("l, F jS", $currentUnixTime);
$isEventToday = ($eventDateString == $currentDateString);
$isEventOver = !$isEventToday && ($eventUnixTime < $currentUnixTime);
$isFutureEvent = !$isEventOver && !$isEventToday;
?>
<?php if ($isEventOver): ?>
<br />
<span class="body-text-bold"><?php echo date("D, M jS, Y g:i a", strtotime($record['date'])) ?></span> <br />
<div align="left" class="body-text"><?php echo $record['content'] ?></div><hr align="left" color="#A29DB2" width="100" />
<?php endif; ?> <?php endforeach ?>
</div>
__________________________________________________________
Note that you have to redefine the variables for each "foreach" loop
If you want to compare other field values, just define more variables and compare those as well.
If you want to test for the values of the variable that you're using. Try something like this in your foreach loop:
_____ code ________________________________________________
<ul>
<li>Event date = <?php echo $eventDateString ?></li>
<li>Current date = <?php echo $currentDateString ?></li>
<li>$isEventToday = <?php echo $isEventToday ? "true" : "false" ?></li>
<li>$isEventOver = <?php echo $isEventOver ? "true" : "false" ?></li>
<li>$isFutureEvent = <?php echo $isFutureEvent ? "true" : "false" ?></li>
<li>$CurrentUnixTime extended = <?php echo date("l jS \of F Y h:i:s A", $currentUnixTime); ?></li>
</ul>
__________________________________________________________
The first CMS Builder reference book is now available on-line!
Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php
Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php