RemoveDate Conflict
5 posts by 2 authors in: Forums > CMS Builder
Last Post: March 31, 2011 (RSS)
By wcd - March 29, 2011
I have a page that is loading different listings based on their category, which needs to be referenced through the query string (ex: "?event_type=Entertainment"). This works fine unless I have a value entered into the removeDate field, in which case the category/query string "filter" is no longer working. I'm guessing that I need to add something to or somehow modify the where clause? Here's what I have currently:
list($calendar_listingsRecords, $calendar_listingsMetaData) = getRecords(array(
'tableName' => 'calendar_listings',
'where' => "removeDate >= NOW() OR removeDate='0000-00-00 00:00:00'",
'ignoreRemoveDate' => true,
));
list($calendar_listingsRecords, $calendar_listingsMetaData) = getRecords(array(
'tableName' => 'calendar_listings',
'where' => "removeDate >= NOW() OR removeDate='0000-00-00 00:00:00'",
'ignoreRemoveDate' => true,
));
Re: [multimedia_nj] RemoveDate Conflict
By Jason - March 30, 2011
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:
This will output the actual SQL query string being used. Let me know what you get.
Hope this helps
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
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com
Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/
Jason Sauchuk - Project Manager
interactivetools.com
Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/
Re: [Jason] RemoveDate Conflict
By wcd - March 30, 2011
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).
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).
Re: [multimedia_nj] RemoveDate Conflict
By Jason - March 31, 2011
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:
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:
Hope this helps
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
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com
Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/
Jason Sauchuk - Project Manager
interactivetools.com
Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/
Re: [Jason] RemoveDate Conflict
By wcd - March 31, 2011
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".
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".