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.

Yes, the graceful failure test worked.

I've also changed the code as advised and it provides the same result as with the previous code.

Thanks for your very efficient and effective help.