removeDate in where

5 posts by 2 authors in: Forums > CMS Builder
Last Post: March 21, 2017   (RSS)

By Mikey - March 16, 2017

I've been racking my brain over this for a few hours and I'm making no headway.

I'm using the Zicky Calendar and I have a page set up to show the events per month, but I do not want to list any events that have a removeDate with date/time entries assigned to them.

Some events will have removeDate assigned, other events will not and can be left blank.

I'm trying to create a 'where' that will list only monthly events which the removeDate is not past-tense OR events that do not have removeDate assigned.

Here's the code with the 'where' and what I've tried. Presently as it is all events are listed regardless of the removeDate,

$monthNames = Array("Jan.", "Feb.", "Mar.", "Apr.", "May", "June", "July", "Aug.", "Sept.", "Oct.", "Nov.", "Dec."); 
if (!isset($_REQUEST["month"])) $_REQUEST["month"] = date("n"); 
if (!isset($_REQUEST["year"])) $_REQUEST["year"] = date("Y"); 
 
$cMonth = $_REQUEST["month"]; 
$cYear = $_REQUEST["year"]; 
 
$prev_year = $cYear; 
$next_year = $cYear; 
 
$prev_month = $cMonth-1; 
$next_month = $cMonth+1; 
 
if ($prev_month == 0 ) { 
$prev_month = 12; 
$prev_year = $cYear - 1; 

if ($next_month == 13 ) { 
$next_month = 1; 
$next_year = $cYear + 1; 
}

list($eventsRecords, $eventsMetaData) = getRecords(array( 
    'tableName' => 'events', 
    'where'     => "(removeDate >= NOW()) OR ". mysql_escapef('MONTH(date) = ? AND YEAR(date) = ?', $cMonth, $cYear)."",
    'orderBy'     => 'date',
    'ignoreRemoveDate' =>  true,
));

Thanks for any suggestions, Zick

By Dave - March 20, 2017

Hi Zicky, 

I'm trying to create a 'where' that will list only monthly events which the removeDate is not past-tense OR events that do not have removeDate assigned.

Unassigned dates are stored as "0000-00-00 00:00:00"  How about this? 

'where'     => "removeDate >= NOW() OR removeDate = '0000-00-00 00:00:00' ", // removeDate is in future or not set

Let me know if that works for you.

Dave Edis - Senior Developer

interactivetools.com

By Mikey - March 20, 2017 - edited: March 20, 2017

Hey Dave,

I didn't explain myself well enough. I should have said it like this.

I'm trying to create a 'where' that will list only monthly events which the removeDate is not past-tense OR events that do not have removeDate assigned, while retaining the month-by-month event listings request.

And here's what I tried

'where'     => "removeDate >= NOW() OR removeDate = '0000-00-00 00:00:00' AND ". mysql_escapef('MONTH(date) = ? AND YEAR(date) = ?', $cMonth, $cYear)."", // removeDate is in future or not set AND retains month and year request

It works - sort of. It does list the events with removalDates, and events without a removeDate assigned. However the events with removalDates that are not past-tense are listed for every month now - regardless of the monthly listing of events.

What's I'm trying to achieve is to use list events MONTH by MONTH, but do not list any events that have a removalDate greater than now, otherwise show the month's events.

For example:
MARCH

Sky Diving, March 20 with a removal date of March 20 at 5:00pm (should not show for March monthly events listings once 5:01pm March 20th arrives.)
Canoeing, March 22 no removal date assigned
Kayaking, March 23 no removal date assigned

APRIL
Rock Climbing, April 12 with a removal date of April 12 at 4:30pm (should not show for April monthly events listings once 4:30:01pm April 12th arrives.)
Canoeing, April 12 no removal date assigned
Kayaking, April 13 no removal date assigned

However this is how it appears at the moment:
MARCH

Sky Diving, March 20
Canoeing, March 22 no removal date assigned
Kayaking, March 23 no removal date assigned

APRIL
Sky Diving, March 20 (this is being carried over to the next month because of the removeDate has not not expired, and it's ignoring the month by month request)
Rock Climbing, April 12
Canoeing, April 12 no removal date assigned
Kayaking, April 13 no removal date assigned

removeDate in where, removeDate, zicky calendar

By Mikey - March 20, 2017 - edited: March 20, 2017

Dave,

I think I got this working. I've ran a few removeDate test and other possible date related scenarios to see if I could come across any issues. Found a few other things I needed to deal with, but after a little more work everything appears to be working now. Thanks for the help.

Below are lines of code I've used on various pages.

On the Month-by-Month listings I used:

<?php
$monthNames = Array("Jan.", "Feb.", "Mar.", "Apr.", "May", "June", "July", "Aug.", "Sept.", "Oct.", "Nov.", "Dec."); 
if (!isset($_REQUEST["month"])) $_REQUEST["month"] = date("n"); 
if (!isset($_REQUEST["year"])) $_REQUEST["year"] = date("Y"); 
 
$cMonth = $_REQUEST["month"]; 
$cYear = $_REQUEST["year"]; 
 
$prev_year = $cYear; 
$next_year = $cYear; 
 
$prev_month = $cMonth-1; 
$next_month = $cMonth+1; 
 
if ($prev_month == 0 ) { 
$prev_month = 12; 
$prev_year = $cYear - 1; 

if ($next_month == 13 ) { 
$next_month = 1; 
$next_year = $cYear + 1; 
}

$removalDate = mysql_escapef('MONTH(date) = ? AND YEAR(date) = ?', $cMonth, $cYear)." AND (removeDate >= NOW() OR removeDate = '0000-00-00 00:00:00') ";

// get event records for the selected month 
list($eventsRecords, $eventsMetaData) = getRecords(array( 
  'tableName' => 'events',
  'where'     => $removalDate,
  'orderBy'     => 'date',
  'ignoreRemoveDate' =>  true,
)); 

// organize events into an array indexed by 'day', each element being a list of events on that day 
$eventsByDay = array(); 
foreach ($eventsRecords as $event) {  
  $day = intval(date('d', strtotime($event['date'])));
  if (!array_key_exists($day, $eventsByDay)) { $eventsByDay[$day] = array(); } 
  $eventsByDay[$day][] = $event; 
}
?>

On the all events listings I used:

// load records
  list($eventsRecords, $eventsMetaData) = getRecords(array(
    'tableName'   => 'events',
    'where' => "(date >= NOW() OR removeDate >= NOW())",
    'perPage'     => '6',
    'orderBy'     => 'date',
    'loadUploads' => true,
    'allowSearch' => true,
    'ignoreRemoveDate' =>  true,
  ));

On the event details page I used:

  // load record from 'events'
  list($eventsRecords, $eventsMetaData) = getRecords(array(
    'tableName'   => 'events',
    'where'       => whereRecordNumberInUrl(0)." AND (removeDate >= NOW() OR removeDate = '0000-00-00 00:00:00') ",
    'loadUploads' => true,
    'allowSearch' => false,
    'limit'       => '1',
    'ignoreRemoveDate' =>  true,
  ));
  $eventsRecord = @$eventsRecords[0]; // get first record
  if (!$eventsRecord) { redirectBrowserToUrl("https://".$_SERVER['HTTP_HOST']."/events.php"); } // redirect if no record found or removeDate has past

I can't say this code is clean, but it does seem to be doing what I need.

Zicky