Sum miles from all posts with Custom Field my_miles
<?php
$total_miles=0;
$meta_key = 'my_miles';//set this to your custom field meta key
$allmiles=$wpdb->get_col($wpdb->prepare("SELECT meta_value FROM $wpdb->postmeta WHERE meta_key = %s", $meta_key));
foreach ($allmiles as $miles) {
$total_miles = $total_miles + $miles;
}
echo 'Total miles is '.$total_miles;
?>
array_sum is your friend.. 😉
<?php
$miles = array();
$meta_key = 'abc';//set this to your custom field meta key
$miles = $wpdb->get_col($wpdb->prepare("SELECT meta_value FROM $wpdb->postmeta WHERE meta_key = %s", $meta_key));
echo 'Total miles is '.array_sum( $miles );
?>
You can actually drop this though, making it smaller still.
$miles = array();
get_col returns an empty array if on fail, or arrayed results on success.. meaning the array_sum will just return 0 on being an empty array.
Awesome! Thanks for the responses!
Learned that sum with get_var also works:
<?php
$meta_key = 'miles';//set this to your custom field meta key
$allmiles=$wpdb->get_var($wpdb->prepare("SELECT sum(meta_value) FROM $wpdb->postmeta WHERE meta_key = %s", $meta_key));
echo '<p>Total miles is '.$allmiles . '</p>';
?>
Yes, that’s certainly an alternateive, however….. if you sum in the query, and it fails you’ll get an error from the echo (i think), where as the array_sum won’t error because the result will still be an array.. it’ll just be 0 …
Just avoids any need to catch the error … 🙂
So would adding $allmiles = 0; before the query work? Or are you saying that a failed query would put some error message (or value) in $allmiles?
Just interested…thanks.
Not sure, i havn’t tested, but echo expects a string, when you echo the result of the query you’ll receive whatever is output on fail (if it fails).
get_col outputs an empty array on fail, array_sum will not error as long as it’s given a valid array, an empty array will simply return 0 for array_sum (no error).
Checking the docs suggests get_var returns NULL on fail.
http://codex.wordpress.org/Function_Reference/wpdb_Class#SELECT_a_Variable
So it should be fine i guess.. 😉
Is there a way to do this for only those posts with a specific tag?
