Specify date in WHERE clause

Hi terryally,

If your date field is called "date", and you want to list only records from December, 2009, you can use this code:

'where' => "MONTH(date) = 12 AND YEAR(date) = 2009"

I hope this helps! Please let me know if you have any questions.

Hi Chris,

This has worked well! Exactly what I am looking for.

There is a slight variation that I want. I want to list only today's and future events and I am achieving that through the following in the select statement:

'where' => 'start_date >= NOW()',

That works fine. I am getting a long list of current and future events. I now want to segment in month blocks so, I've added your solution which works absolutely fine:

'where' => 'start_date >= NOW() AND MONTH(start_date) = 2',


However what I cannot achieve is a single header for each month. I need to find a way to include the following

<?php echo date("F", strtotime($showsRecord['start_date'])) ?>

before the loop starts so it appears only once.


How do I do that?

Dear Chris,

I think I found a solution. Do you foresee any problems that this might throw up? I just replicate this for each month of the year.

<?php
list($showsRecords, $showsMetaData) = getRecords(array(
'tableName' => 'shows',
'where' => 'start_date >= NOW() AND MONTH(start_date) = 2',
'orderBy' => 'start_date ASC',
));
$showsRecord = @$showsRecords[0]; // get first record
?>
<?php if($showsRecord['start_date']): ?>
<h3><?php echo date("F", strtotime($showsRecord['start_date'])) ?></h3>
<?php foreach ($showsRecords as $record): ?>
<p>
<b><?php echo date("D, M jS Y", strtotime($record['start_date'])) ?></b><br/>
<b><a href="<?php echo $record['_link'] ?>" title="<?php echo $record['event_title'] ?>"><?php echo $record['event_title'] ?></a></b>
</p>
<?php endforeach ?>
<?php endif ?>

Regards

Hi terryally,

That's a good approach, yes. You'll want to test to make sure your code fails gracefully if no records match (you could test this by adding " AND 0 = 1" to the end of your where clause.)

Specifically, I think you may need to replace this line:

<?php if($showsRecord['start_date']): ?>

...with this:

<?php if($showsRecord): ?>

Please let me know if you have any questions.