Complicated date query

#1 / Mar 22, 2010 10:08am
Ronny
83 posts
I’ve been trying a couple of things, but I can’t seem to get the results I need. I hope you can help me out!

Situation:
I’m using XML-grab to import an xml-feed of events. I can’t change anything on the input of the xml-feed. Therefor I have to post the date of an event in a custom field. The format looks like this:
- 20-4-2010 (= April 20th 2010)
- 8-12-2010 (= December 8th 2010)

What I want:
I want to show all events from today until the end of the month. Beware that I can’t use the date from each entry, I have to use the custom field.

Here’s what I got so far:
```
{exp:weblog:entries 
  weblog="events" 
  limit="10" 
  search:event_date="{current_time format='%n-%Y'}" 
  start_on="{current_time format='%j-%n-%Y 00:00'}" 
  orderby="event_date|event_place" sort="asc|asc"}

[..]
```
At the moment the search:event_date does what I expected: it searches for all entries in this month.
By posting this I realise the start_on parameter looks at the entry-date, and I can’t use that. So here’s my question: is there any way to make sure I don’t see past events?
#2 / Mar 22, 2010 10:26am

Sue Crocker
26054 posts

Is there no way you can use the entry date as the actual date? I know that Solspace has an importer that allows for XML.
#3 / Mar 22, 2010 10:36am
Ronny
83 posts
I’ve tried Solspace’s Importer, but it doesn’t allow to import XML feeds that go deeper than the first level. Besides that, the date-format I’m using is not complete (no time and we use the european format) so I don’t expect I would be able to use Solspace’s date-field.

I’ve just been testing some more things and I did found some sort of solution:
```
{exp:weblog:entries
  weblog="events"
  search:event_date="{current_time format='%n-%Y'}"
  orderby="event_date|event_place" sort="asc|asc"}

{if event_date < "{current_time format='%j-%n-%Y'}"}
{if:else}

{title}
[..]

{/if}
{/exp:weblog:entries}
```
The problem is I can’t use the limit-parameter anymore. When I do, I get multiple empty pages before I get to see the results I want. The pages actually become too long…
#4 / Mar 22, 2010 11:01am

Sue Crocker
26054 posts

SQL query, then?
#5 / Mar 22, 2010 3:26pm
Ronny
83 posts
Hmm I’ve been working on a SQL query and I’m on a point where I don’t know what to do. I hope there’s somebody who can help me to finish this query:
```
{exp:query sql="SELECT 
    exp_weblog_data.entry_id, 
    exp_weblog_data.field_id_131 AS event_title, 
    exp_weblog_data.field_id_135 AS event_date, 
    FROM exp_weblog_data
    WHERE weblog_id = '20' AND
    exp_weblog_data.field_id_135 LIKE '%-{current_time format='%n-%Y'}' AND
    exp_weblog_data.field_id_135 >= '{current_time format='%j-%n-%Y'}'
    ORDER BY field_id_135 ASC"}
```
The data is stored like this:
1-3-2010 (march 1st 2010)
4-3-2010
8-3-2010
12-3-2010
22-3-2010
28-3-2010

With the query above I get some nice results, but there’s still a bug and I don’t know how to get rid of it. The results for the query (today - 22nd of march) above would be:

22-3-2010
28-3-2010
4-3-2010
8-3-2010

As you can see, “number 4” (or 8) is a higher number than “number 22” or “28”. So that shows up in the results as well. How can I tell mysql not do that and think like a human?

Please help… :*(

#6 / Mar 22, 2010 6:27pm

Ronny

83 posts

I’m not certain this is the best way to handle this query, but at least this works. For future reference:

SELECT 
    exp_weblog_data.entry_id, 
    exp_weblog_data.field_id_131 AS event_title, 
    exp_weblog_data.field_id_135 AS event_date,
    FROM exp_weblog_data
    WHERE weblog_id = '3' AND
    exp_weblog_data.field_id_135 LIKE '%-{current_time format='%n-%Y'}' AND
    exp_weblog_data.field_id_135+0 >= '{current_time format='%j'}' AND
    exp_weblog_data.field_id_135+0 < '32'
    ORDER BY field_id_135+0 ASC"

Explanation:

// FIND ALL EVENTS FOR THIS MONTH
exp_weblog_data.field_id_135 LIKE '%-{current_time format='%n-%Y'}' AND

// MAKE THE FIRST NUMBER OF THE DATE STRING ABSOLUTE (or whatever +0 does..)
// AND SELECT ALL DATES THAT ARE HIGHER THAN TODAY'S DATE
exp_weblog_data.field_id_135+0 >= '{current_time format='%j'}' AND

// SELECT ALL DATES UNTIL THE END OF THE MONTH; SINCE THERE ARE LESS THAN 32 DAYS IN A MONTH.
exp_weblog_data.field_id_135+0 < '32'

That last selection might not be needed…

If somebody comes up with a better solution, just let me know. This is out of my league….

#7 / Mar 22, 2010 7:24pm

ChiefAlchemist

913 posts

Hey Ronny

Yeah, EE and “events” are a bxtch 😉

For what it’s worth, what I did was to have a bit of PHP to read the current date. And from there determine my end date. From there I set up a conditional and just used brute force to do through them all. Inefficient? Yup! Sure is. I originally thought about a SQL statement but then realized I’d lose other functionality going that route. For example, this weblog uses ffMatrix. I’d sure I could write SQL to piece it all together “manually” but brute force seemed the better choice.

My code looked something like this:

<h1>VIEW EVENTS</h1>
      </div> 
<div class="c{rand-color}-content-inner-1">
<ul>
<li><a href="http://{path={segment_1}/{segment_2}/next-10-day}">Next 10 Days</a></li>
<li><a href="http://{path={segment_1}/{segment_2}/next-4-weeks}">Next 4 Weeks</a></li>
<li><a href="http://{path={segment_1}/{segment_2}/next-4-to-8-weeks}">Next 4 to 8 Weeks</a></li>
<li><a href="http://{path={segment_1}/{segment_2}/beyond-8-weeks}">Beyond 8 Weeks</a></li>
<li><a href="http://{path={segment_1}/{segment_2}/all-upcoming-events}">All Upcoming Events</a></li>
</ul>
</div>


{if ( segment_3 == "next-10-days" ) || ( segment_3 == "next-4-weeks" ) || ( segment_3 == "next-4-to-8-weeks" ) || ( segment_3 == "beyond-8-weeks" ) || ( segment_3 == "all-upcoming-events" ) || ( segment_3 == "" ) }


{exp:weblog:entries weblog="event-list" orderby="event-date" sort="asc" dynamic="off" disable="member_data|pagination|trackbacks" }
<?php
$eeSegment = "{segment_3}";
switch ( $eeSegment )
{
  case  "next-10-days":
    $date_Start =  time()+60*60*24*7*0;
    $date_End =  time()+60*60*24*10;
    break;
  case  "next-4-weeks":
    $date_Start =  time()+60*60*24*7*0;
    $date_End =  time()+60*60*24*7*4;
    break;
  case "next-4-to-8-weeks":
    $date_Start =  time()+60*60*24*7*4;
    $date_End =  time()+60*60*24*7*8;
    break;
  case "beyond-8-weeks":
    $date_Start =  time()+60*60*24*7*8;
    $date_End =  time()+60*60*24*7*800;
    break;
  case "all-upcoming-events":
    $date_Start =  time()+60*60*24*7*0;
    $date_End =  time()+60*60*24*7*800;
    break;
  default:
    $date_Start =   time()+60*60*24*7*0;
    $date_End =  time()+60*60*24*7*4;
    break;
}

if ( ( {event-date} >= $date_Start ) && ( {event-date} <= $date_End ) )
{
?>

HTML to be rendered. 

<?php
}
?>

{/exp:weblog:entries}

The segment tells the template what values to use for start and end since for this site there was more than one.

Helps?

#8 / Mar 22, 2010 7:40pm

Ronny
83 posts

Oh cool, this sure helps. I’m glad I got just one thing going on, but I do need more functionality to my page, so I definitely will use some of this. Thanks so much!
#9 / Mar 22, 2010 8:13pm

ChiefAlchemist
913 posts

Just keep in mind that you are basically going thru the WHOLE weblog. If/when it gets big there might be some performance issues. But maybe not. Always hard to say. It is brute force but it does work 😊

Thread

Ronny

Sue Crocker

Ronny

Sue Crocker

Ronny

Ronny

ChiefAlchemist

Ronny

ChiefAlchemist

Username

Password

Thread