removeDate in where

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,

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

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