RemoveDate Conflict

Hi,

Your query string looks correct. What is your page actually returning? Is it returning all records regardless of your event type?

Trying using this:
list($calendar_listingsRecords, $calendar_listingsMetaData) = getRecords(array(
'tableName' => 'calendar_listings',
'where' => "removeDate >= NOW() OR removeDate='0000-00-00 00:00:00'",
'ignoreRemoveDate' => true,
'debugSql' => true,
));

This will output the actual SQL query string being used. Let me know what you get.

Hope this helps

Hi Jason, thanks for your help.

Yes, if there is a remove date applied to the listing then it will load regardless of the query string. Here's the output I get with the debug using the "?event_type=Promotion" query string:

FROM `cms_calendar_listings` as `calendar_listings`
WHERE (removeDate >= NOW() OR removeDate='0000-00-00 00:00:00' AND ((`event_type` = 'Promotion' OR `event_type` LIKE '%\tPromotion\t%')))
ORDER BY dragSortOrder DESC
SELECT * FROM `cms_uploads`
WHERE tableName = 'calendar_listings' AND
fieldName IN ('event_image') AND
recordNum IN (14,15,6,3,16,17,18,20,21,22,23)
ORDER BY `order`, num
SELECT SQL_CALC_FOUND_ROWS `accounts`.*
FROM `cms_accounts` as `accounts`
WHERE (`num` IN (1))
ORDER BY fullname, username

Listings with an event_type of "Entertainment" are loading if they have a remove date entered, but if the remove date field is blank they do not load. The one listing I have set with an event_type of "Entertainment" and no remove date is not included in the string of record numbers above (recordNum 5), but the entertainment listings that do have remove dates are included (6,16,17,18,20,21,22,23).

Hi,

The query you posted is for an event_type of "Promotion", not "Entertainment". I'm not sure if that changes things or not.

As for records with no removeDate not loading, my guess would be that the remove date field was added to the section after the record was created. This would mean that the removeDate field would have no value, not even "0000-00-00 00:00:00". There are a couple of ways to get around this, you could change your where clause like this:

list($calendar_listingsRecords, $calendar_listingsMetaData) = getRecords(array(
'tableName' => 'calendar_listings',
'where' => "(removeDate >= NOW() OR removeDate='0000-00-00 00:00:00' OR removeDate='')",
'ignoreRemoveDate' => true,

));

The other option is to leave removeDate out all together. I noticed that you're using the option "ignoreRemoveDate". If the actual value of remove date isn't important to your query, you can leave it out all together like this:

list($calendar_listingsRecords, $calendar_listingsMetaData) = getRecords(array(
'tableName' => 'calendar_listings',
'ignoreRemoveDate' => true,

));

Hope this helps

Thanks Jason, the first solution worked. We have some listings that don't have remove dates, which is why we included the ignoreRemoveDate option. However, if there is a value in that field, then we do want the removeDate function to work.

Just for clarity's sake, in case anyone else is having a similar problem, the issue wasn't that records weren't loading, it was that all records with values in the removeDate fields were loading regardless of whether the event_type was "Promotion" or "Entertainment".