End Date - hide/remove record

5 posts by 2 authors in: Forums > CMS Builder
Last Post: July 1, 2015   (RSS)

By Zicky - June 26, 2015

I'm need to create a solution to hide/remove records from displaying on a website page.

I've created a field called "End Date" (end_date) that is left empty by default, but a date can be entered for a specific date that the record should not be displayed on the website. Problem is - I can't seem to figure this out and make it work. I know there's the removeDate option, but that will not work for this section editor.

Here's the code I'm working from and a solution I tried that did not work marked as BOLD. Anyone have any suggestions to get this working?

// search results per page
if (@$_REQUEST['searchResultsPerPage'] AND is_numeric(@$_REQUEST['searchResultsPerPage'])){
  $perPage = $_REQUEST['searchResultsPerPage'];
}
else{
  $perPage = '8';
}
  
// load records from 'documents'
list($documentsRecords, $documentsMetaData) = getRecords(array(
'tableName'   => 'documents',
'perPage'     => @$perPage ? $perPage : '8',
'loadUploads' => true,
'allowSearch' => true,
'orderBy'     => 'date DESC',
'where'     => 'end_date <= NOW()',
));

Thanks,

Zick

By Zicky - June 29, 2015

So I've tried a few different configurations with no luck as of yet.

Basically I want to use an 'end_date' field not a removeDate function, so here's what's I'm working with... and what I'm trying to achieve is have an 'end_date' date selector for a multi-section, but if nothing was entered in the "end_date" dropdown selector, then the records would remain on the website.

I think I need some method to ignore an empty "end_date' field as seen in the red bold text end_date='0000-00-00 00:00:00' of the code below... but this isn't working.
I also tried the following below with no joy:

'where'    =>   "(end_date >= NOW() OR end_date<='0000-00-00 00:00:00')",

'where'    =>   "(end_date >= NOW() OR end_date>='0000-00-00 00:00:00')",

anyone have any suggestions on how to get this to work?

// search results per page
if (@$_REQUEST['searchResultsPerPage'] AND is_numeric(@$_REQUEST['searchResultsPerPage'])){
  $perPage = $_REQUEST['searchResultsPerPage'];
}
else{
  $perPage = '8';
}
  
// load records from 'documents'
list($documentsRecords, $documentsMetaData) = getRecords(array(
'tableName'   => 'documents',
'perPage'     => @$perPage ? $perPage : '8',
'loadUploads' => true,
'allowSearch' => true,
'orderBy'     => 'date DESC',
'where'    =>   "(end_date >= NOW() OR end_date='0000-00-00 00:00:00')",
));

I also need to apply the solution for the fix to the code below.

// documents
  $searchString = '';
  //Create the where statement to be used to filter documents
  
  //if departement values have been selected....
  if(is_array(@$administrationRecord['dept_documents:values'])){
    //Count the total number if items in the array.....
    $arrayCounter = count($administrationRecord['dept_documents:values']);
    //Loop through the array....
    foreach($administrationRecord['dept_documents:values'] as $key => $filterItem){
      //create the search string that searches the departements for the associated value....
      $searchString .= "documents_category LIKE '%\t$filterItem\t%'";
      //If this is not the last item to filter by, add or to the statement....
      if(($key+1) != $arrayCounter){
        $searchString .= " OR ";
      }
    }  
  }
// documents

  // load records from 'documents'
  list($documentsRecords, $documentsMetaData) = getRecords(array(
    'tableName'   => 'documents',
    //'perPage'     => '10',
    'where'       => $searchString,
    'loadUploads' => true,
    'allowSearch' => false,
  ));

Thanks, Zick

By Dave - June 30, 2015

Hi Zick, 

Try this: 

'where'    =>   "(end_date <= NOW() OR end_date='0000-00-00 00:00:00')",

If that doesn't work, print out end_date and see what MySQL thinks it is.  Not all MySQL configurations let you store 0000-00-00 because it's not technically considered a valid date.  

Also note that you should be able to do everything you've mentioned with removedDate and neverRemove:
http://www.interactivetools.com/docs/cmsbuilder/special_fieldnames.html

Hope that helps!  Let me know any questions.

Dave Edis - Senior Developer
interactivetools.com

By Dave - June 30, 2015

Actually, no I think it is >= .  I'd try each part of the where individually and see which is limiting the results you want and print out the value of `end_date` to see what the unexpected records think the value is. 

Let me know what you find!

Dave Edis - Senior Developer
interactivetools.com