WordPress.org

Support

Support » How-To and Troubleshooting » Add up the values of custom fields…

Add up the values of custom fields…

  • Kahil
    Participant

    @kahil

    I found a resolved topic, here, that does something real close to what I would like to do. But, I would like to restrain this to only posts of a certain category and with certain tag.

    Does anyone know how I would limit the following code by category and tag? Right now it is adding up all of the values in the database tables with the custom value field.

    <?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 );
    ?>
Viewing 15 replies - 1 through 15 (of 22 total)
  • if you are trying to limit the adding to just the posts in one category, there is nothing in your code that is limiting post selection at all. It is selecting the value from every post you have that has that meta key.

    I would say your best easiest bet is do a custom WP query, pulling all the posts in a single category – instead of displaying them, loop through them and add up the value, one by one, of all posts with that custom field key.

    For info on how to limit a query to a category or tag see this
    http://codex.wordpress.org/Template_Tags/query_posts

    For info on custom post query, see this article
    http://weblogtoolscollection.com/archives/2008/04/13/define-your-own-wordpress-loop-using-wp_query/
    That query code is set up to display the posts. You would change it from displaying to querying and adding to a total.

    Within the loop, the code you want to run on each post is
    the code you want to pull from each post is
    $amt = get_post_meta($post->ID, 'abc', true)
    then add to the total

    if ($amt) {
           $total += $amt;
         }

    This should get you started.

    Kahil
    Participant

    @kahil

    I know about custom queries and can’t seem to find the right combo…

    The code I posted was just a repost from the linked topic. I was simply asking how that code could be modded…

    I have an archive page with several loops. Each loop is already running a query that limits what is shown to the current category that I am in and to a specific tag. I do this so that posts of specific tags within a category archive are grouped together.

    Prior to any of my loops I have the following to get the category ID for the archive I am viewing…

    <!--Code to get category ID based on the category archive being viewed-->
    <?php foreach(get_the_category() as $category)
    { $thecat = $category->cat_ID; } ?>

    Prior to each of my loops I have the following to limit the loop to only show specific tags within the category archive I am viewing…

    <!--Query to group posts by tag in respect to the category archive being viewed-->
        <?php query_posts('cat=' . $thecat . '&tag=30-sheets&order=ASC'); ?>

    As you can see I am already limiting each loop to what I want, but the previous code is still pulling all the values in that database table.

    Here try this…

    <?php
    // Cat ID please
    $catid = 3;
    // Meta key name please
    $meta_key = 'miles';
    
    $allmiles = $wpdb->get_var($wpdb->prepare("SELECT sum(meta.meta_value)
    FROM $wpdb->postmeta as meta
    LEFT JOIN $wpdb->posts as posts ON posts.ID = meta.post_id
    LEFT JOIN $wpdb->term_relationships as rel ON rel.object_id = posts.ID
    LEFT JOIN $wpdb->term_taxonomy as tax ON tax.term_taxonomy_id = rel.term_taxonomy_id
    LEFT JOIN $wpdb->terms as terms ON terms.term_id = tax.term_id
    WHERE meta_key = %s
    AND terms.term_id = %d", $meta_key, $catid));
    echo '<p>Total miles is '.$allmiles . '</p>';
    ?>

    Update the key and category ID to suit…

    Kahil
    Participant

    @kahil

    We’re getting close here…

    now it is adding up all in that category… Is there a way to limit it down by one more factor? A specific tag?

    You’ll need something like this..
    http://wordpress.org/support/topic/276635?replies=23

    Combined with what i’ve posted above … or a culmination of the two so to speak..

    You might want to test it under heavy load first, before thinking about adding additional bits and pieces.. (i’m only testing in a minimal install).

    Kahil
    Participant

    @kahil

    hrmm… I’ll give it a go… that stuff looks to be a little beyond me…

    Kahil
    Participant

    @kahil

    I tried this…but no luck…

    <?php
    // Meta key name please
    $meta_key = 'Units With Overage';
    
    $tagname = is_tag('30-sheets');
    
    $allmiles = $wpdb->get_var($wpdb->prepare("SELECT sum(meta.meta_value)
    FROM $wpdb->postmeta as meta
    LEFT JOIN $wpdb->posts as posts ON posts.ID = meta.post_id
    LEFT JOIN $wpdb->term_relationships as rel ON rel.object_id = posts.ID
    LEFT JOIN $wpdb->term_taxonomy as tax ON tax.term_taxonomy_id = rel.term_taxonomy_id
    LEFT JOIN $wpdb->terms as terms ON terms.term_id = tax.term_id
    WHERE meta_key = %s
    AND terms.term_id = %d", $meta_key, $thecat, $tagname));
    echo '<p>Total miles is '.$allmiles . '</p>';
    ?>

    doesn’t the code i’ve already listed before call the category and the tag I want?

    Kahil
    Participant

    @kahil

    I tried this as well with no luck…

    <?php
    foreach(get_the_tags() as $this_tag)
    	{$this_tag->name == "30-sheets"; }
    ?>
    
        <?php
    // Meta key name please
    $meta_key = 'Units With Overage';
    
    $allmiles = $wpdb->get_var($wpdb->prepare("SELECT sum(meta.meta_value)
    FROM $wpdb->postmeta as meta
    LEFT JOIN $wpdb->posts as posts ON posts.ID = meta.post_id
    LEFT JOIN $wpdb->term_relationships as rel ON rel.object_id = posts.ID
    LEFT JOIN $wpdb->term_taxonomy as tax ON tax.term_taxonomy_id = rel.term_taxonomy_id
    LEFT JOIN $wpdb->terms as terms ON terms.term_id = tax.term_id
    WHERE meta_key = %s
    AND terms.term_id = %d", $meta_key, $thecat, $this_tag));
    echo '<p>Total miles is '.$allmiles . '</p>';
    ?>
    Kahil
    Participant

    @kahil

    @ t31os_

    lol…..yeah….so…. I looked and looked and looked at the topic you linked….aaaaaand… I got nothin. That stuff makes no sense to me at all… I’m basically learning as I go with php. I haven’t even delved into any mysql stuff…

    thank you in advance for your help everyone…

    And you think i know a how load more? lol…

    I’m still learning to ya know… 😉 I look at examples and go from there.

    Maybe an easy approach would be to query the values, then loop over them excluding the unwanted matches, rather then pulling hair over a possibly complex query..

    Ok so you select some additional rows that don’t match, but it’s easier to skip a few items in a loop then to write complex queries wouldn’t you agree?

    The issue writing the query is that post tags and categories are basically one and the same, except they have a different taxonomy name, but to select based on tag and category requires at a minimum a sub query, which is not always the easiest thing (especially for those of us that are still picking it up, lol).

    Of course, (without me testing) it could be as simple as adding …

    AND terms.term_id = X

    where X would be a tag ID…

    Not sure that would be it though.. have this nagging feeling a sub-query of some kind would be required (i could be over thinking it though).

    It may also be of benefit to post this up on the hackers mailing list, if anyone can help with complex queries/questions, it’s those guys (they know who they are)… 🙂

    Sorry for the late response, last reply was posted just before going to sleep, hence the delay.. 😉

    Kahil
    Participant

    @kahil

    I added that and nada… 🙁

    I even tried to wrap that code in a conditional to say if in this category and has this tag, then run the code… no luck, but that prolly could have just been me not knowing how to do that correctly.

    Kahil
    Participant

    @kahil

    Ok… I have an idea here that I think, at least in theory, may work…but I don’t know how to implement it. What if I wrapped the section that displays the info I want to add up and give the div a specific ID? How can the above code be modded to do that? I can change the ID for that section in each loop for simplicity.

    I know how to make a form to do this with javascript, etc… but not dynamically. Because if I make a div with a specific ID on the archive page, it will have the same ID for every post within that loop. I just can’t figure out how to make it add up the value of each instance of that div.

    Forgive me for jumping in late here. This may be totally off – but:

    From what I can tell you already have a loop that iterates through the posts you are interested in. Can you use get_post_meta() in the loop and add as you go, so you wind up with the total at the end of the loop? What am I missing?

    Kahil
    Participant

    @kahil

    I thought so too, but I guess it doesn’t work that way. I’m still looking for a solution that works. So far all I can get it to do is get the total of ALL fields that are of a tag OR category, not both.

    Will this work for you? (I hope I got all the syntax right!)

    <?php
    $miles = array();
    $meta_key = 'abc';//set this to your custom field meta key
    $my_tag = 'TagName'; //set this to your tag name - case sensitive
    $miles = $wpdb->get_col($wpdb->prepare(
    "SELECT meta_value FROM $wpdb->postmeta WHERE meta_key = %s
       AND post_id in
          (SELECT a.object_id FROM wp_term_relationships a, wp_term_taxonomy b, wp_terms c
           WHERE a.term_taxonomy_id = b.term_taxonomy_id
           AND b.term_id = c.term_id
           AND b.taxonomy = 'post_tag'
           AND c.name = %s)", $meta_key,$my_tag));
    echo 'Total miles is '.array_sum( $miles );
    ?>

    EDIT – OOPS left out the category. I’ll try again.

Viewing 15 replies - 1 through 15 (of 22 total)
  • The topic ‘Add up the values of custom fields…’ is closed to new replies.