How to best group field values by date?

(@pdclark)

See WP_Query date parameters, especially year and monthnum, and get_post_meta().

Plugin Support

(@pdclark)

The following PHP, when installed as a plugin for example at wp-content/plugins/shortcode-food-journal/shortcode-food-journal.php, or to your theme’s functions.php, or to a Code Snippet plugin, will enable a shortcode [food-journal] which does what I think you’re describing. It goes from the current month back to January 2000. 2000 can be changed to another starting year on the line foreach( range( date('Y'), 2000 ) as $year ) {

<?php
/**
 * Plugin Name: Shortcode: Food Journal
 * Description: Output <code>food</code> counts per month and year with shortcode <code>[food-journal]</code>.
 * Plugin Author: ??δ???
 * Author URI: https://pd.cm/
 * Plugin URI: https://www.ads-software.com/support/topic/how-to-best-group-field-values-by-date/
 * Version: 1
 */

add_shortcode(
	'food-journal',
	function( $atts, $content, $shortcode_tag ){
		ob_start();

		$base_query_args = [
			'post_type' => 'journal',
			'post_status' => 'published',
			'posts_per_page' => -1,
			'fields' => 'ids',
		];

		echo '<ul>';
		$food_by_year = [];

		// Current year back to 2000 in decending order.
		foreach( range( date('Y'), 2000 ) as $year ) {

			$food_by_year[ $year ] = 0;

			// December to January in decending order.
			foreach( range( 12, 1 ) as $month ) {
				// Skip months in the current year after the current month.
				$this_months_timestamp = strtotime( $year . '-' . $month );
				if ( $this_months_timestamp > time() ) {
					continue;
				}

				$this_months_post_ids = get_posts(
					array_merge(
						$base_query_args,
						[
							'year' => $year,
							'monthnum' => $month,
						]
					)
				);

				$this_food = $this_months_food = 0;

				if ( ! empty( $this_months_post_ids ) ) {
					foreach( $this_months_post_ids as $post_id ) {
						$this_food = (int) get_post_meta( $post_id, 'food', true );
						$this_months_food += $this_food;
						$food_by_year[ $year ] += $this_food;
					}
				}

				printf(
					'<li>Month %s, Food: %d</li>',
					date( 'F Y', $this_months_timestamp ),
					$this_months_food
				);
			}

			printf(
				'<li>Year %s, Food: %d</li>',
				$year,
				$food_by_year[ $year ]
			);
		}

		echo '</ul>';

		return ob_get_clean();
	}
);

Thread Starter

(@youknowmenot)

Wow, that looks perfect, thank you! I have integrated it to my tables design, but is there a way to call the year and monthly value separately so I can style them in their own table rows? I know I can do it by duplicating the shortcode and commenting the year/month printf’s in your code but it looks like a bit of an overkill. I would love to toy with that code and get more field values that way!

Plugin Support

(@pdclark)

Add another foreach after the others which iterates over $food_by_year as $year => $food.

Here:

<?php
/**
 * Plugin Name: Shortcode: Food Journal
 * Description: Output <code>food</code> counts per month and year with shortcode <code>[food-journal]</code>.
 * Plugin Author: ??δ???
 * Author URI: https://pd.cm/
 * Plugin URI: https://www.ads-software.com/support/topic/how-to-best-group-field-values-by-date/
 * Version: 2
 */

add_shortcode(
	'food-journal',
	function( $atts, $content, $shortcode_tag ){
		ob_start();

		$base_query_args = [
			'post_type' => 'journal',
			'post_status' => 'published',
			'posts_per_page' => -1,
			'fields' => 'ids',
		];

		?>
		<style>
			#food-by-month th { background-color: rgba( 10, 50, 10, .8 ); color: #fff; }
			#food-by-month tr:nth-child(even) { background-color: rgba( 10, 50, 10, .3 ); }
			#food-by-year th { background-color: rgba( 10, 10, 50, .8 ); color: #fff; }
			#food-by-year tr:nth-child(even) { background-color: rgba( 10, 10, 50, .3 ); }
		</style>
		<table id="food-by-month"><tr><th>Month</th><th>Food</th></tr>
		<?php
		$food_by_year = [];

		// Current year back to 2000 in decending order.
		foreach( range( date('Y'), 2000 ) as $year ) {

			$food_by_year[ $year ] = 0;

			// December to January in decending order.
			foreach( range( 12, 1 ) as $month ) {
				// Skip months in the current year after the current month.
				$this_months_timestamp = strtotime( $year . '-' . $month );
				if ( $this_months_timestamp > time() ) {
					continue;
				}

				$this_months_post_ids = get_posts(
					array_merge(
						$base_query_args,
						[
							'year' => $year,
							'monthnum' => $month,
						]
					)
				);

				$this_food = $this_months_food = 0;

				if ( ! empty( $this_months_post_ids ) ) {
					foreach( $this_months_post_ids as $post_id ) {
						$this_food = (int) get_post_meta( $post_id, 'food', true );
						$this_months_food += $this_food;
						$food_by_year[ $year ] += $this_food;
					}
				}

				printf(
					'<tr><td>%s</td><td>%d</td></tr>',
					date( 'F Y', $this_months_timestamp ),
					$this_months_food
				);
			}

		}

		?>
		</table>
		<table id="food-by-year"><tr><th>Year</th><th>Food</th></tr>
		<?php
		foreach( $food_by_year as $year => $food ) {
		    printf(
		        '<tr><td>%d</td><td>%d</td></tr>',
		        $year,
		        $food
		    );
		}
		echo '</table>';

		return ob_get_clean();
	}
);

Plugin Support

(@pdclark)

Just in case, the same thing, table-based, but with years first:

<?php
/**
 * Plugin Name: Shortcode: Food Journal
 * Description: Output <code>food</code> counts per month and year with shortcode <code>[food-journal]</code>.
 * Plugin Author: ??δ???
 * Author URI: https://pd.cm/
 * Plugin URI: https://www.ads-software.com/support/topic/how-to-best-group-field-values-by-date/
 * Version: 3
 */

add_shortcode(
	'food-journal',
	function( $atts, $content, $shortcode_tag ){
		ob_start();

		$base_query_args = [
			'post_type' => 'journal',
			'post_status' => 'published',
			'posts_per_page' => -1,
			'fields' => 'ids',
		];

		?>
		<table id="food-by-month"><tr><th>Month</th><th>Food</th></tr>
		<?php
		$food_by_year = [];

		// Current year back to 2000 in decending order.
		foreach( range( date('Y'), 2000 ) as $year ) {

			$food_by_year[ $year ] = 0;

			// December to January in decending order.
			foreach( range( 12, 1 ) as $month ) {
				// Skip months in the current year after the current month.
				$this_months_timestamp = strtotime( $year . '-' . $month );
				if ( $this_months_timestamp > time() ) {
					continue;
				}

				$this_months_post_ids = get_posts(
					array_merge(
						$base_query_args,
						[
							'year' => $year,
							'monthnum' => $month,
						]
					)
				);

				$this_food = $this_months_food = 0;

				if ( ! empty( $this_months_post_ids ) ) {
					foreach( $this_months_post_ids as $post_id ) {
						$this_food = (int) get_post_meta( $post_id, 'food', true );
						$this_months_food += $this_food;
						$food_by_year[ $year ] += $this_food;
					}
				}

				printf(
					'<tr><td>%s</td><td>%d</td></tr>',
					date( 'F Y', $this_months_timestamp ),
					$this_months_food
				);
			}

		}

		?>
		</table>
		<?php
			$food_by_month_html = ob_get_clean();
			ob_start();
		?>
		<style>
			#food-by-month th { background-color: rgba( 10, 50, 10, .8 ); color: #fff; }
			#food-by-month tr:nth-child(even) { background-color: rgba( 10, 50, 10, .3 ); }
			#food-by-year th { background-color: rgba( 10, 10, 50, .8 ); color: #fff; }
			#food-by-year tr:nth-child(even) { background-color: rgba( 10, 10, 50, .3 ); }
		</style>
		<table id="food-by-year"><tr><th>Year</th><th>Food</th></tr>
		<?php
		foreach( $food_by_year as $year => $food ) {
		    printf(
		        '<tr><td>%d</td><td>%d</td></tr>',
		        $year,
		        $food
		    );
		}
		echo '</table>';

		return ob_get_clean() . $food_by_month_html;
	}
);

Plugin Support

(@pdclark)

https://developer.www.ads-software.com/reference/functions/get_post_meta/

For getting more field values, it’s possible to save on SQL queries by calling get_post_meta( $post_id ) (without the second or third arguments) once for each month. This will return an array of all meta for the post.

Plugin Support

(@pdclark)

And if outputting variables that are strings (rather than integers or formatted dates), one should wrap variables in appropriate escaping functions. e.g., esc_html(), strip_tags(), etc. It wasn’t necessary in the examples above because all variable datatypes were cast as integers with (int) or %d and the only string %s output was coming from date().

Thread Starter

(@youknowmenot)

You are next level! Thank you for all the explanations. What if the value entered is 0.5 or 1.5, how do I go about displaying increments?

Plugin Support

(@pdclark)

Use (float) instead of (int) to support decimals; currency field types instead of number field types; format to the number of decimals you want with https://www.php.net/manual/en/function.number-format.php

Thread Starter

(@youknowmenot)

A little late but thanks again for trying to help me out. It sounded simple but I couldn’t manage to make it work. I changed int to float and also the field type into currency but I don’t understand your third instruction – isn’t that automatically done by POD in the field settings? Here is a screenshot:

https://i.imgur.com/XOZIian.png

And here is the code of the function that I need to have a decimal point on:

add_shortcode( ? ? 'ricerolls-journal-years', ? ? function( $atts, $content, $shortcode_tag ){ ? ? ? ? ob_start(); ? ? ? ? $base_query_args = [ ? ? ? ? ? ? 'post_type' => 'daily_journal', ? ? ? ? ? ? 'post_status' => 'published', ? ? ? ? ? ? 'posts_per_page' => -1, ? ? ? ? ? ? 'fields' => 'ids', ? ? ? ? ]; ? ? ? ? echo '<tr>'; ? ? ? ? $ricerolls_by_year = []; ? ? ? ? // Current year back to 2023 in decending order. ? ? ? ? foreach( range( date('Y'), 2023 ) as $year ) { ? ? ? ? ? ? $ricerolls_by_year[ $year ] = 0; ? ? ? ? ? ? // December to January in decending order. ? ? ? ? ? ? foreach( range( 1, 12 ) as $month ) { ? ? ? ? ? ? ? ? // Skip months in the current year after the current month. ? ? ? ? ? ? ? ? $this_months_timestamp = strtotime( $year . '-' . $month ); ? ? ? ? ? ? ? ? if ( $this_months_timestamp > time() ) { ? ? ? ? ? ? ? ? ? ? continue; ? ? ? ? ? ? ? ? } ? ? ? ? ? ? ? ? $this_months_post_ids = get_posts( ? ? ? ? ? ? ? ? ? ? array_merge( ? ? ? ? ? ? ? ? ? ? ? ? $base_query_args, ? ? ? ? ? ? ? ? ? ? ? ? [ ? ? ? ? ? ? ? ? ? ? ? ? ? ? 'year' => $year, ? ? ? ? ? ? ? ? ? ? ? ? ? ? 'monthnum' => $month, ? ? ? ? ? ? ? ? ? ? ? ? ] ? ? ? ? ? ? ? ? ? ? ) ? ? ? ? ? ? ? ? ); ? ? ? ? ? ? ? ? $this_ricerolls = $this_months_ricerolls = 0; ? ? ? ? ? ? ? ? if ( ! empty( $this_months_post_ids ) ) { ? ? ? ? ? ? ? ? ? ? foreach( $this_months_post_ids as $post_id ) { ? ? ? ? ? ? ? ? ? ? ? ? $this_ricerolls = (float) get_post_meta( $post_id, 'rice_rolls', true ); ? ? ? ? ? ? ? ? ? ? ? ? $this_months_ricerolls += $this_ricerolls; ? ? ? ? ? ? ? ? ? ? ? ? $ricerolls_by_year[ $year ] += $this_ricerolls; ? ? ? ? ? ? ? ? ? ? } ? ? ? ? ? ? ? ? } ? ? ? ? ? ? } ? ? ? ? ? ? printf( ? ? ? ? ? ? ? ? '<td colspan="2" bgcolor="#222222" style="white-space:nowrap;border:1px dotted #666666;width:460px;">Year %s</td> ? ? ? ? ? ? ? ? <td id="tdArchive" bgcolor="#000000" style="width:50px;"> %d </td></tr>', ? ? ? ? ? ? ? ? $year, ? ? ? ? ? ? ? ? $ricerolls_by_year[ $year ] ? ? ? ? ? ? ); ? ? ? ? } ? ? ? ? echo ' ? ? ? ? </tr>'; ? ? ? ? return ob_get_clean(); ? ? } );

And it’s still rounding up the numbers. I tried floatval () too – same story.

This reply was modified 1 year, 10 months ago by youknowmenot.

Thread Starter

(@youknowmenot)