Nested results and where date is more then x away
7 posts by 3 authors in: Forums > CMS Builder
Last Post: September 30, 2008 (RSS)
We have a new project which needs some clever date work.
---------------------------------------------------
a) WHERE DATE IS...
We have a multi-record table powering news articles - a Latest News page (listing), a News article page (detail) and also an Archive News page.
A date field (format date) is included on each news article.
On the archive news page, I want it to show records from the same table that are 'old' - say over 4 months old for example.
I was wondering if there was a way to do this with a where statement based on the current date.
For example, the archive news page would have a where statement which says:
where - date (of article) is over 4 months older than today, display.And similarly on the latest news page we would have the opposite:
where - date (of article) is less than 4 months old from today, display.This way, the site will automatically archive news as it gets old.
---------------------------------------------------------------
b) On the same listing pages, we want to group individual stories in 'months', for example:
September 2008
Story 1
Story 2
August 2008
Story 3
Story 4
July 2008
Story 5
Is this possible to have it automated rather than manually hardcoding each month with the possible news stories that might appear within each month bracket?
Dave - I will email you the link and details as it'll be much easier to understand if you can see it!!
Many thanks in advance,
Rob Bathgate
DotPerformance
Re: [rjbathgate] Nested results and where date is more then x away
By Dave - September 29, 2008
This isn't supported by default but possible with some advanced Mysql.
a) Try this where for the mysql where clause:
'where' => " nameOfYourDateField <= (NOW() - INTERVAL 4 MONTH) ",
For "newer than 4 months" use > instead of <=.
b) For grouping stories by month I'll respond to your email.
Hope that helps!
interactivetools.com
Re: [Dave] Nested results and where date is more then x away
By aev - September 29, 2008
is the solution to B like this one...
http://www.interactivetools.com/forum/gforum.cgi?post=65436#65436
...otherwise it would be very interesting to see it.
-aev-
Re: [aev] Nested results and where date is more then x away
Firstly, thanks Dave for your help.
Have solved both - Dave's first answer for part A worked a treat, i.e.:
'where' => " date <= (NOW() - INTERVAL 4 MONTH) "I'll also post the solution to part B in a short while.
While I'm here, i'm trying to use the 'where' => " date <= (NOW() - INTERVAL 4 MONTH) " statement in an IF statement:
<?php if ($newsRecord['date'] <= (NOW() - INTERVAL 4 MONTH)): ?>
TEXT A
<?php else: ?>
TEXT B
<?php endif; ?>Aim of the game, to produce text depending on whether the date is less than or greater than 4 months old (from today).
However, the IF statement won't parse - Parse error: syntax error, unexpected T_LNUMBER
Putting the (NOW() - INTERVAL 4 MONTH) into " or ' makes the IF statement parse, but it doesn't work as it's simply taking (NOW() - INTERVAL 4 MONTH) to be a static bit of text to compare $date to.
Any idea what I'm missing?
Cheers
Rob
Re: [rjbathgate] Nested results and where date is more then x away
<?php foreach ($newsRecords as $record): ?>
//** DEFINE $DATE AS NEW VARIABLE FOR DATE IN RECORD:
<?php $date = date("F Y", strtotime($record['date'])); ?>
//** DISPLAY DATE IF DIFFERENT FROM LAST DATE
<?php if (@$lastDate != $date): ?>
<h6><?php echo $date ?></h6>
<?php endif ?>
//** SHOW WHAT EVER OTHER CONTENT I WANT FROM RECORDS
<p><?php echo $record['intro_content'] ?></p>
//** SET $LASTDATE VARIABLE
<?php $lastDate = $date; ?>
<?php endforeach; ?>The code will match dates down to whatever parameters you define in date("F Y", strtotime($record['date']))
i.e. using F Y = MONTH YEAR, and as such, it will match month and year (ignoring actual days) - thus giving a Monthly grouping.
Using d.m.y it will match it down to actual days... thus giving a daily grouping...
Cheers
Rob
Re: [rjbathgate] Nested results and where date is more then x away
By Dave - September 30, 2008
The "(NOW() - INTERVAL 4 MONTH)" part is MySQL and only works in MySQL queries.
You need something in PHP. Try this. Note that it's not _exactly_ the same (I'm averaging months to 30 days) but very close:
<?php $is4MonthsOld = strtotime($newsRecord['date']) < (time() - 60*60*24*30*4); ?>
<?php if ($is4MonthsOld): ?>
TEXT A
<?php else: ?>
TEXT B
<?php endif; ?>
Hope that helps!
interactivetools.com
Re: [Dave] Nested results and where date is more then x away