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

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

removeDate in where, removeDate, zicky calendar

By Dave - March 21, 2017

Great, glad you got it working and thanks for sharing your code.  Cheers!

Dave Edis - Senior Developer

interactivetools.com