Help with $where statement
2 posts by 2 authors in: Forums > CMS Builder
Last Post: July 29, 2014 (RSS)
By nmsinc - July 24, 2014
I need to gather records that are with the last 365 days and I'm having some issues with the $where statement - see below.
The current code below fails to produce any records when their are records present!
Any help would be appreciated - nmsinc
$where2 = "":
$where2 = 'remove_record_from_listing = 0';
$where3 = "";
$monthDay = date('m/d');
$year = date('Y')-1;
$oneYearBack = "".$monthDay."/".$year."";
$where3 = "(createdDate > '".intval($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,
));
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/