Help with $where statement

2 posts by 2 authors in: Forums > CMS Builder
Last Post: July 29, 2014   (RSS)

By claire - July 29, 2014

You'll have to change this to use SQL datetime notation. This should work instead:

$where2 = "":
$where2 = 'remove_record_from_listing = 0';

$where3 = "";
  $oneYearBack = date('Y-m-d H:i:s', (time()-60*60*24*365) );
  $where3 = "(createdDate > '".$oneYearBack."')";
  $where3 = "$where2 AND $where3";
   
  
  // load records from 'items_submission'
  list($items_submissionRecords, $items_submissionMetaData) = getRecords(array(
    'tableName'   => 'items_submission',
    'where'       => $where3,
    'loadUploads' => false,
    'allowSearch' => false,
  ));

The best way to handle date calculations in PHP/SQL is to use the current UNIX timestamp from the time() function. You can use it as a base reference to go forward or back as much as you want, just by adding or subtracting seconds. In this case, you want to go back a year, so the calculation works like so:

time() - (60 seconds X 60 minutes X 24 hours X 365 days)

Then the 'Y-m-d H:i:s' format for date() produces a SQL datetime.

Here's some info on the Unix timestamp if you've never heard of it before: http://www.unixtimestamp.com/index.php

Hope this helps!

--------------------

Claire Ryan
interactivetools.com

Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/