API Selection string with date range

[Adam Kiss]

Hi all,

I have in my page tree:

- tournaments
   - match 1 [tpl: match]
   - match 2 [tpl: match]
   - match 3 [tpl: match]

Now, template 'match' has two dates: date_start and date_end, and I would like to select all pages, that are children of /tournaments/, do have 'match' template and today's date is in range (date_start; date_end - 1 day).

Is it possible?

[ryan]

Adam,

This is easy except for the date part, which I want to make sure I understand. You need it to match the pages that fall in today's date? To do that, I think we have to find the timestamp at 12:00 am today and 11:59 pm today, and then use that to compare with your date_start and date_end fields. Here's how I'd at least start:

<?php

// get 12:00 am today 
$today_start = strtotime(date('Y-m-d ')); 

// get 11:59 pm today. There are 86400 seconds in a day, so subtract 1 to get 11:59:59 today
$today_end = $today_start + (86400-1); 

$matches = $pages->find("template=match, parent=/tournaments/, date_start<=$today_start, date_end>=$today_end"); 

It may need tweaking to get it right, but it's a start. Having sample data to test with is key.

As for the date_end - 1 day part: I think you could accomplish that by modifying the $today_end to arrive at what you are looking for (or maybe substitute the $today_start). Though I'm not sure I understand the context well enough to propose the right solution for that part of it... I think it'll be easy though. Tell me more about that part if you'd like. 

[Adam Kiss]

I am actually so smart I solved it already, but haven't had time to post my code

<?php
$date_start = date('Y-m-d');
$date_end = date('Y-m-d', strtotime('+1 DAY',strtotime($date_start)));
$match = $pages->find('parent=/tournaments/, template=match, date_registration <= $date_start, $date >= $date_end, sort=date')->shift();

The point is, that players can register into match from one date (start of registration, synchronized with some media coverage) until 24hrs before the match starts. This code gives me one match (or none), that has already opened registration page AND is beginning first.

[ryan]

Looks like a good solution!

I would suggest one change: use get() rather than find(). It will produce the same result, except you won't have to have the shift() at the end. Not to mention, it'll be more efficient/faster since it's not attempting to retrieve more than 1 page:

<?php
$match = $pages->get("parent=/tournaments/, template=match, date_registration <= $date_start, $date >= $date_end, sort=date");

[Adam Kiss]

Good catch!

Edit: Just to clarify: get gives you automatic 'LIMIT 1' in translated SQL, correct? [or at least behaves that way]

[ryan]

Btw, looking closer, what is the strtoint() function? I don't think that's a PHP function.. one of your own?

[Adam Kiss]

I don't see no strtoint function

I posted reply between your two replies, in case you missed it

Also, re:strtoint: I edited the post to include correct strtotime function instead of said strtoint ;D, which is relique from the times years ago when I programmed in pascal/delphi and I still confuse it with strtotime

[ryan]

I started in Pascal too: "Turbo Pascal" (pre Delphi). Here's my "shareware" from 1992:  

http://thecomputerroombbs.com/3.html

(search for "datav31" DataView)

;D