Specify date in WHERE clause
6 posts by 2 authors in: Forums > CMS Builder
Last Post: February 3, 2010 (RSS)
I have a date field and I want to specify a month in the WHERE clause. How do I do this?
Re: [terryally] Specify date in WHERE clause
By Chris - February 2, 2010
Hi terryally,
If your date field is called "date", and you want to list only records from December, 2009, you can use this code:
I hope this helps! Please let me know if you have any questions.
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.
All the best,
Chris
Chris
Re: [chris] Specify date in WHERE clause
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:
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:
However what I cannot achieve is a single header for each month. I need to find a way to include the following
before the loop starts so it appears only once.
How do I do that?
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?
Re: [terryally] Specify date in WHERE clause
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.
Regards
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
Re: [terryally] Specify date in WHERE clause
By Chris - February 3, 2010
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:
...with this:
Please let me know if you have any questions.
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.
All the best,
Chris
Chris
Re: [chris] Specify date in WHERE clause
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.
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.