Jump to content

Trying to use the month of a Datetime field in a page select statement [RESOLVED]


Charlie W
 Share

Recommended Posts

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

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

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 by da²
  • Like 1
Link to comment
Share on other sites

@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

  • Charlie W changed the title to Trying to use the month of a Datetime field in a page select statement [RESOLVED]
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 🙂 

  • Like 1
Link to comment
Share on other sites

  • 4 weeks later...

@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");

image.png.df3cf21305d2a097f285cfaff43c4d56.png

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

 

  • Like 2
Link to comment
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...