Converting events website to PW

[jacmaes]

I'd like to convert an existing Website of mine (that does not use a CMS) to PW to be able to update it easily. Problem is, I don't know how to deal with more complex queries such as this one:

<?
$tomorrow = date('Y-m-d',mktime(0,0,0,date('m'), date('d')+1, date('Y')));

// Tomorrow's events
$query_tomorrow = "SELECT id, city, event, category, description, DATE_FORMAT(date_start,'%M %D') as datestart, DATE_FORMAT(date_end,'%M %D') as dateend FROM events WHERE disabled != '1' AND (
(date_start = '$tomorrow')
OR (date_start <= '$tomorrow' AND date_end >= '$tomorrow')
)
ORDER BY date_start DESC";
?>

I want to show tomorrow's events, so I'm selecting all events that I marked as active (disabled != '1', because they have not been cancelled for example) and that are either:

1. ongoing but will not end today,

2. or will start tomorrow.

How would I achieve the exact same query with PW?

Thanks in advance.

[ryan]

This selector should accomplish that:

$pages->find("template=event, date_end>=tomorrow, date_start<=tomorrow +1 day, sort=-date_start"); 

For disabled events, I would suggest just using PW's 'unpublished' or 'hidden' status, which will eliminate them from your queries automatically. Though you certainly could add an extra "toggle_disabled" checkbox if you wanted to, and include that in your selector.

[jacmaes]

Thanks a million, Ryan. It's the AND - OR that that I didn't know how to reproduce with the API. As usual, it's concise and intuitive. I can't conceive starting a project without PW now!

[ryan]

Btw, not sure if this was clear, but I'm using the terms "tomorrow" and "tomorrow +1 day" literally in the selector. You don't have to substitute a variable for those, as it should recognize those terms (it uses PHP's strtotime to translate internally).

[jacmaes]

Thanks for the clarification. Even shorter that way! Would it work the same for "today" then?

Also, now that I'm reading your code again, I'm not sure I understand

date_start<=tomorrow +1 day

Finally, In my current setup, one-day events do not have an end date, .i.e. I specify a start date but leave the date_end field blank. Would I need to do it anyway with the proposed code? I'm probably making things overly complex. Dates are confusing.

[ryan]

Would it work the same for "today" then?

It'll work with anything that strtotime() recognizes, and I think 'today' is recognized by strtotime.

Finally, In my current setup, one-day events do not have an end date, .i.e. I specify a start date but leave the date_end field blank. Would I need to do it anyway with the proposed code?

Yes, you would need the date_end field set. Date calculations use unix timestamps so if date_end is participating in any logic, then it would need to carry the date that the event ends, whether it's the same as date_start or some time later. If you don't want to have to remember to do that when editing an event, it could be automated fairly easily with a simple module (let me know if you want more details).