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.

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

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!