Little help from my PHP coding friends please

10 posts by 4 authors in: Forums > CMS Builder
Last Post: January 24, 2017   (RSS)

By kkegans - January 17, 2017

I have a list viewer and I have start date and end date fields in the list records.  What PHP code will I need to add in the foreach loop to only display items between their start and end dates?

Start Date: <?php echo date("D, M jS, Y g:i:s a", strtotime($record['start_date'])) ?>
End Date: <?php echo date("D, M jS, Y g:i:s a", strtotime($record['end_date'])) ?>

CMSB Rocks!
Thanks,
Kurt

By ross - January 18, 2017

Hi Kurt

Thanks for posting.

I'd like to clarify what you are looking for.

What I get from you post is that you want to display a list of records whose start and end dates fall within a specific range.

So you need a way to say, show me all records between January 1 and February 23.

Am I on the right track?

Thanks!

-----------------------------------------------------------
Cheers,
Ross Fairbairn - Consulting
consulting@interactivetools.com

Hire me! Save time by getting our experts to help with your project.
Template changes, advanced features, full integration, whatever you
need. Whether you need one hour or fifty, get it done fast with
Priority Consulting: http://www.interactivetools.com/consulting/

By kkegans - January 18, 2017 - edited: January 18, 2017

Ross,

More specifically show me all of the records whose start date is <= to today’s date and whose end date is > today.

These are promotions and I need them to display if they are valid today

CMSB Rocks!
Thanks,
Kurt

By ross - January 18, 2017

Hi Kurt

Great.

To get a list of records in a specific date range, I would actually use MySQL instead of PHP; it's much more efficient.

Keep in mind that date functions can get rather advanced.

What I would start with is a simple "WHERE"  in your "getRecords" like this:

// load records from 'news'
  list($newsRecords, $newsMetaData) = getRecords(array(
    'tableName'   => 'news',
    'loadUploads' => true,
    'allowSearch' => false,
    'where'       => "start_time > '2017-01-03' AND end_time < '2017-01-15' "
  ));

See how I have the "where" setup there?  

Make sure you change the field names so they match your setup. I highlighted my assumed field names in blue.  

Let me know how you make out.

Thanks!

-----------------------------------------------------------
Cheers,
Ross Fairbairn - Consulting
consulting@interactivetools.com

Hire me! Save time by getting our experts to help with your project.
Template changes, advanced features, full integration, whatever you
need. Whether you need one hour or fifty, get it done fast with
Priority Consulting: http://www.interactivetools.com/consulting/

By kkegans - January 18, 2017

Ross,


What I am confused on is how to apply the DB variables to the query and comparing them to 'today's date', is this close to the where statement needed?

'where'       => "date("D, M, Y", strtotime($record['start_date'])) < 'date("D, M, Y"' AND date("D, M, Y", strtotime($record['end_date']) > 'date("D, M, Y"' "

CMSB Rocks!
Thanks,
Kurt

By mizrahi - January 18, 2017 - edited: January 18, 2017

I think you need something like this.

'where'       => "start_date < NOW() AND end_date > NOW() "

Selects any promotion where the start_date is before now AND where end_date is after NOW

You may find that CURDATE() is a better fit than NOW(). NOW() is very specific down to the second. CURDATE() is only concerned with the date itself. 

By ross - January 19, 2017

Thanks for jumping in on this one :)  Looks like that's exactly what Kurt needed.

-----------------------------------------------------------
Cheers,
Ross Fairbairn - Consulting
consulting@interactivetools.com

Hire me! Save time by getting our experts to help with your project.
Template changes, advanced features, full integration, whatever you
need. Whether you need one hour or fifty, get it done fast with
Priority Consulting: http://www.interactivetools.com/consulting/

IF the start or end date is actually today then then the record might not show.

'where'       => "start_date < NOW() AND end_date > NOW() "

Instead you want

'where'       => "start_date <= NOW() AND end_date >= NOW() "

Jeff Shields

By ross - January 24, 2017

Nice tip Jeff!

-----------------------------------------------------------
Cheers,
Ross Fairbairn - Consulting
consulting@interactivetools.com

Hire me! Save time by getting our experts to help with your project.
Template changes, advanced features, full integration, whatever you
need. Whether you need one hour or fifty, get it done fast with
Priority Consulting: http://www.interactivetools.com/consulting/