Charlie W Posted January 26 Share Posted January 26 This is probably a simple answer, but I've been chasing my tail for a few hours trying to find the right syntax. I'm trying to find the events that match the current month. The event_date field is of type Datetime and I want to select those that match a particular month ($currentMonth). I know I could select those with a date >= to the first day of a given month and <= the last day, but I'm trying to grab historical information over several years by month. My code: $events = $pages->find("template=event, event_date->format('m')=$currentMonth, sort=event_date"); event_date->format('m') is my failed attempt at selecting the month from a Datetime field The error: Unknown Selector operator: '[empty]' -- was your selector value properly escaped? field='event_date', value='->format('m')=01', selector: 'template=event, event_date->format('m')=01, sort=event_date' Any assistance is appreciated. Link to comment Share on other sites More sharing options...
psy Posted January 26 Share Posted January 26 2 hours ago, Charlie W said: $events = $pages->find("template=event, event_date->format('m')=$currentMonth, sort=event_date"); This is a database call so you can't perform operations on the field name. Maybe something like (untested): $events = $pages->find("template=event, sort=event_date"); $eventMonth = new PageArray(); foreach($events as $event) { $eventDate = $event->date->format("m"); if($eventDate === $currentMonth) $eventMonth->add($event); } Link to comment Share on other sites More sharing options...
da² Posted January 26 Share Posted January 26 (edited) Create 2 dates, one on the first day of the month, one on the last day, and use the selector "event_date>=begin, event_date<end". 4 hours ago, psy said: $events = $pages->find("template=event, sort=event_date"); You are potentially loading thousands of pages here. Edited January 26 by da² 1 Link to comment Share on other sites More sharing options...
Charlie W Posted January 26 Author Share Posted January 26 @psy: Thank you. I guess I could have been more concise and just asked if I can use php functions on fields in a selector statement. I thought there might be some built-in PW helper that I didn't know about. I shall restructure my code. Link to comment Share on other sites More sharing options...
psy Posted January 26 Share Posted January 26 8 hours ago, da² said: Create 2 dates, one on the first day of the month, one on the last day, and use the selector "event_date>=begin, event_date<end". You are potentially loading thousands of pages here. Aware of that. Wanted to keep the explanation short. Good idea to limited the event_date start and end 🙂 1 Link to comment Share on other sites More sharing options...
Robin S Posted February 18 Share Posted February 18 @Charlie W, you can use an SQL query to match a date column by part of the date, e.g. by month. There are different ways you could incorporate this to find the pages you want, but one simple way is to first get the IDs of all matching pages, regardless of template, published status, etc, and then use those IDs as part of a PW selector. Demo: // First get the IDs of all pages where the month of the date value is February $field_name = 'date'; // The name of your date field $table_name = "field_$field_name"; // The table name is the field name prefixed with "field_" $month_number = 2; // i.e. February, or whatever month number you want to search for $stmt = $database->prepare("SELECT pages_id from $table_name WHERE MONTH(data) = :month_number"); $stmt->bindValue(':month_number', $month_number, \PDO::PARAM_INT); $stmt->execute(); $ids = $stmt->fetchAll(\PDO::FETCH_COLUMN); $ids_str = implode('|', $ids); // Then use the IDs in a selector where you are limiting by template, applying a sort, etc. $items = $pages->find("template=event, id=$ids_str, limit=3, sort=-date"); You can also match a date by just a day number or just a year, e.g. WHERE DAY(data) = :day_number WHERE YEAR(data) = :year 2 Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now