Specify date range in URL search

6 posts by 2 authors in: Forums > CMS Builder
Last Post: May 23, 2014   (RSS)

By gkornbluth - May 23, 2014

Hi nmsinc,

If I understand you correctly, here's a recipe from the CMSB Cookbook ( http://www.thecmsbcookbook.com ) that you might be able to adapt to return your desired date range of records using the updatedDate field.

Hope it helps,

Jerry Kornbluth

LISTING RECORDS WITHIN A DATE RANGE

Here’s one that came in extremely handy.

I had an organization that had events that spanned a period of time and a special reception somewhere within those dates.

My client wanted to list all of the events on their list page, but wanted to make a special list at the top of the page for those events with receptions that were going to occur within the upcoming week.

Thanks to Dave Edis of Interactive Tools (again) who freely admits that date math makes his head hurt, we came up with the following.

First I created a date field in my ‘date_compare_test’ multi record editor called ‘reception_date’.

Then came the task of creating a list viewer that would list only the events I wanted in the appropriate groups.

Dave came up with the idea of putting 2 viewers on my list viewer page and using 2 different ‘where’ statements in the get records calls to separate the content that could show in each area.

Here's the 'where' statement that we used to show reception_ dates for the upcoming week, as you suggested.

_____ code ________________________________________________

'where' => '(NOW() + INTERVAL 7 DAY) >= reception_date AND reception_date >= TIMESTAMP(CURDATE(), "00:00:00")',

__________________________________________________________

And here's the 'where' statement that we used to show all the other events (and leave out those already listed)

_____ code ________________________________________________

'where' => 'reception_date <= TIMESTAMP(NOW()) OR reception_date >= TIMESTAMP(NOW()+ INTERVAL 7 DAY)',

__________________________________________________________

So here’s how the complete viewer code looked.

_____ code ________________________________________________

<!– Start of first list viewer code–>

<?php header('Content-type: text/html; charset=utf-8'); ?>
<?php

require_once "/path_to_your_server/cmsAdmin/lib/viewer_functions.php";

list($date_compare_testRecords, $date_compare_testMetaData) = getRecords(array(
'Table name' => 'date_compare_test',
'where' => '(NOW() + INTERVAL 7 DAY) >= reception_date AND reception_date >= TIMESTAMP(CURDATE(), "00:00:00")',
));
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title></title>
<meta http-equiv="Content-Type" content="text/html;charset=UTF-8" />

<hr />

EVENTS WITH RECEPTIONS TODAY THROUGH 7 DAYS IN THE FUTURE<br /><br />
<?php foreach ($date_compare_testRecords as $record): ?>
Title: <?php echo $record['title'] ?><br />
The Reception Date is <?php echo date("D, M jS, Y g:i a", strtotime($record['reception_date'])) ?> <br />
<?php endforeach ?>
<hr />

<!– Start of second list viewer code–>

<?php

list($date_compare_testRecords, $date_compare_testMetaData) = getRecords(array(
'Table name' => 'date_compare_test',
'where' => 'reception_date < TIMESTAMP(CURDATE(), "00:00:00") OR reception_date > TIMESTAMP(NOW()+ INTERVAL 7 DAY)',
));
?>

EVENTS WITH RECEPTIONS BEFORE TODAY OR MORE THAN 7 DAYS IN THE FUTURE<br /><br />
<?php foreach ($date_compare_testRecords as $record): ?>

Title: <?php echo $record['title'] ?><br />
The Reception Date is <?php echo date("D, M jS, Y g:i a", strtotime($record['reception_date'])) ?> <br />
<?php endforeach ?>

</body>
</html>

__________________________________________________________

Dave explained that for date queries,

“The first step is to figure out if you need to do it in MySQL or PHP. If you are able to do it in MySQL it's often simpler.

You can find a list of MySQL date/time functions here:

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

And here's some examples:

NOW() - Gives you the current date and time: 2010-03-01 09:41:50
(NOW() + INTERVAL 7 DAY) - Get date/time 7 days from now: 2010-02-22 09:45:25
CURDATE() - Gives you the current date only: 2010-03-01
TIMESTAMP() - Format value as date/time, or if two values, add them together
TIMESTAMP(CURDATE()) - Get date/time at beginning of day: 2010-03-01 00:00:00
TIMESTAMP(CURDATE(), “23:59:59") - Get date/time at end of day 2010-03-01 23:59:59

And you can test all these with the MySQL Console Plugin by entering SELECT followed by the function. So: SELECT NOW() in the Mysql Console returns: 2010-03-01 09:41:50

So the first step is to figure out the values you want to compare. My guess is you want:
The date 7 days from now: (NOW() + INTERVAL 7 DAY)
The reception date: reception_date
The date at the end of today: TIMESTAMP(CURDATE(), “23:59:59")

If you write it out in English first it's way easier:
- If the reception_date is 7 days or less from now
- AND the reception_date hasn't already passed

I like to arrange my code so if reads like a time range with the test date in the middle like this:

_____ code ________________________________________________

start_date >= test_date AND test_date >= end_date

__________________________________________________________

So that would be:

_____ code ________________________________________________

(NOW() + INTERVAL 7 DAY) >= reception_date AND reception_date >= TIMESTAMP(CURDATE(), “00:00:00")

__________________________________________________________

The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

By nmsinc - May 23, 2014

Thanks for the help - I have coded my $where statement as you suggested and I'm still having no luck. I'm sure it's just a punctuation error: Any ideas?

$where = "member_company = '".intval($CURRENT_USER['member_company_accounts'])."' and remove_record_from_listing = 0 and claim_status != 'Final' and acknowledgment != 'Accepted' and (NOW()+INTERVAL 2 DAY) >= updatedDate";

nmsinc

By gkornbluth - May 23, 2014

Hi nmsinc,

I think you're mixing up php and mysql. Unless I'm mistaken, NOW() is a mysql function and will only work in the list records 'where' =>

Here's a where statement that you can dissect for the syntax

<?php
    list($e_blast_events_noticeRecords, $e_blast_events_noticeMetaData) = getRecords(array(
    'tableName'   => 'e_blast_events_notice',
      'where' => '((NOW() + INTERVAL 7 DAY) >= opening_reception_date_and_start_time AND opening_reception_date_and_start_time >= TIMESTAMP(CURDATE(), "00:00:00")) OR ((NOW() + INTERVAL 7 DAY) >= event_start_date AND event_start_date >= TIMESTAMP(CURDATE(), "00:00:00")) AND apbc_event != 1',   
      
      'orderBy'=> 'neverRemove ASC, event_start_date ASC',
      ));  
      
    ?>

Hope that helps.

Jerry Kornbluth

The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

By nmsinc - May 23, 2014

Hi Jerry,

I am using it in a list statement - please see below:

 $where = "member_company = '".intval($CURRENT_USER['member_company_accounts'])."' and remove_record_from_listing = 0 and status != 'Final' and acknowledgment != 'Accepted' and (NOW()+INTERVAL 2 DAY) >= updatedDate";
 
  list($submissionRecords, $submissionMetaData) = getRecords(array(
    'tableName'   => 'submission',
    'where'       => $where,
    'orderBy'     => 'num',
  ));

Thanks - nmsinc

nmsinc

By gkornbluth - May 23, 2014 - edited: May 23, 2014

I figured that, but I don't think you can use NOW() when defining the variable $where (that's a PHP thing not a MYSQL thing.)

You might think about using an if statement in your foreach loop and using something like the PHP function "time"

I'd try defining a few variables for current time, and time plus 2 days, and then compare those.

Something like:

<?php
$plus2days = time() +(2 * 24 * 60 * 60);
                   // 2 days; 24 hours; 60 mins; 60secs
                   ?>
   <?php // echo $plus2days?>      

<?php
$updatedDate = strtotime($record['updatedDate'])

           ?>
                  
   <?php // echo $updatedDate?>  

<?php if($plus2days >= $updatedDate ):?>

Show the data

<?php endif ?>

See if that approach works for you (no guarantees)

There are also some interesting ideas on the http://www.php.net/manual/en/function.date-add.php and other date function pages

Jerry Kornbluth

The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php