Little help from my PHP coding friends please

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

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!

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"' "

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. 

mazrahi,

Thank You!!  That is exactly what I needed.  The CURDATE() worked perfectly for me.

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

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() "

Nice tip Jeff!