[Custom Fields] Sum the field values eg. per week, month and per day

Same response: ??

Hmmm, Custom Fields are stored in wp_postmeta table, linked to post_id’s.

I guess it’s possible to query this table for all those values if you know what posts they are referring to.

Look at this page for some examples.

Peter

Thanks. It seems pretty advance :S

I’ll have a look on that tomorrow after a good night sleep…

Well, it’s not really, you only need to get your query right:

you have to select the id’s from wp_posts where post_date matches your time frame, say a month, and use these id’s to select from the wp_postmeta table:

select sum(wp_postmeta.meta_value) from wp_postmeta,wp_posts where wp_postmeta.meta_key=’Meters’ and wp_postmeta.post_id=wp_posts.id and month(wp_posts.post_date)=12 and year(wp_posts.post_date)=2009;

Untested, but should do the trick.

EDIT: actually tested ??

Peter

Wauw you got skills…

Well, Ill will try to read howto use that snippet in my code ??

Hehe…. this doesnt print much… I must do something wrong ??

$km_sum = $wpdb->get_var($wpdb->prepare(“SELECT SUM(wp_postmeta.meta_value) FROM wp_postmeta,wp_posts WHERE wp_postmeta.meta_key=’Meters’ AND wp_postmeta.post_id=wp_posts.id AND month(wp_posts.post_date)=12 AND year(wp_posts.post_date)=2009”));
echo ‘<p>Km this periode ‘ . $km_sum . ‘</p>’;

Wauw seems like I got it now ??

$meter_sum_2009 = $wpdb->get_var($wpdb->prepare(“
SELECT SUM($wpdb->postmeta.meta_value)
FROM $wpdb->postmeta, $wpdb->posts
WHERE $wpdb->postmeta.meta_key=’Meters’
AND $wpdb->postmeta.post_id=$wpdb->posts.id
AND month($wpdb->posts.post_date)=’12’
AND year($wpdb->posts.post_date)=’2009′
“));
echo ‘<p>Meters run in 2009: ‘ . $meter_sum_2009 . ‘</p>’;

Well, I have to admit that I only tested the SQL statement, not the actually call from WordPress.

Is this what you wanted?

You now could embed this function in a shortcode and call it whenever you want.

One tip: if you always want to collect the data of a previous month, I suggest calculating the previous month like this:

WHERE (month(post_date) + year(post_date) * 12)) = (month(now()) + (year(now()) * 12) – 1)

Peter

I dont know how to fit it in and change:

//Connect to DB and calculate the sum of meters run in previous month
$meter_last_month = $wpdb->get_var($wpdb->prepare(“
SELECT SUM($wpdb->postmeta.meta_value)
FROM $wpdb->postmeta, $wpdb->posts
WHERE $wpdb->postmeta.meta_key=’Meters’
AND $wpdb->postmeta.post_id=$wpdb->posts.id
AND month($wpdb->posts.post_date)=’12’
AND year($wpdb->posts.post_date)=’2009′”));

Apologies, had been occupied elsewhere…

//Connect to DB and calculate the sum of meters run in previous month
$meter_last_month = $wpdb->get_var($wpdb->prepare(“
SELECT SUM($wpdb->postmeta.meta_value)
FROM $wpdb->postmeta, $wpdb->posts
WHERE $wpdb->postmeta.meta_key=’Meters’
AND $wpdb->postmeta.post_id=$wpdb->posts.id
AND (month($wpdb->posts.post_date) + year($wpdb->posts.post_date) * 12)) = (month(now()) + (year(now()) * 12) – 1)”));

Peter

Hey,

Thanks, and no problem. Im just glad you try to help. Its not urgent in any way…

I did test your snippet but it doesnt print anything. I did made a post set to publish date in november but it doesnt calculate anything for the previous month. There are no errors…

oke, let’s examine this:

the equation is (previous_month + (year_of_previousmonth * 12)) should be equal to (current_month + (year_of_currentmonth * 12)) -1

For November if this year that would be:
11 + (2009 * 12) = 24119

December:
12 + (2009 * 12) = 24120

Which is exactly one more than November ??

Why this construct? Try to write a query for January, summing the values of December of the previous year.

So let’s examine the previous statement, since I might have gone wrong on the parenthesis and such, and I did, or so it seems (just before the first year function should be another parenthesis)

SELECT SUM($wpdb->postmeta.meta_value)
FROM $wpdb->postmeta, $wpdb->posts
WHERE $wpdb->postmeta.meta_key=’Meters’
AND $wpdb->postmeta.post_id=$wpdb->posts.id
AND (month($wpdb->posts.post_date) + (year($wpdb->posts.post_date) * 12)) = (month(now()) + (year(now()) * 12) – 1)

Peter

Thanks again.

As you said your mysql was right!!

I did play around with the () and my Notepad++ did help me. It needed MANY () to word but now it does ??

Have a mary christmas..

Best wishes

/Frederik

And by the way here is the snippet…

$meters_last_month = $wpdb->get_var($wpdb->prepare(“
SELECT SUM($wpdb->postmeta.meta_value)
FROM $wpdb->postmeta, $wpdb->posts
WHERE $wpdb->postmeta.meta_key=’Meters’
AND $wpdb->postmeta.post_id=$wpdb->posts.id
AND ((month($wpdb->posts.post_date) + year($wpdb->posts.post_date) * 12) = ((month(now()) + year(now()) * 12) – 1))”));