WordPress › Support » Add up the values of custom fields...

Kahil
Member
Posted 2 years ago #
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 );
?>
```
stvwlf
Member
Posted 2 years ago #
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
Member
Posted 2 years ago #
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...
```

<?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...
```

    <?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.

Mark / t31os
Moderator
Posted 2 years ago #

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
Member
Posted 2 years ago #

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?
Mark / t31os
Moderator
Posted 2 years ago #

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
Member
Posted 2 years ago #

hrmm... I'll give it a go... that stuff looks to be a little beyond me...

Kahil
Member
Posted 2 years ago #

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
Member
Posted 2 years ago #

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
Member
Posted 2 years ago #

@ 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...
Mark / t31os
Moderator
Posted 2 years ago #
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
Member
Posted 2 years ago #

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
Member
Posted 2 years ago #

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.
vtxyzzy
Member
Posted 2 years ago #

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
Member
Posted 2 years ago #

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.

vtxyzzy
Member
Posted 2 years ago #

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.

vtxyzzy
Member
Posted 2 years ago #

If only this is all correct!!

<?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
$my_cat = 'MyCategory';
$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)
   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 = 'category'
       AND c.name = %s)", $meta_key,$my_tag,$my_cat));
echo 'Total miles is '.array_sum( $miles );
?>

Kahil
Member
Posted 2 years ago #

It returned a value of zero
vtxyzzy
Member
Posted 2 years ago #

Did you make sure you matched the meta_key, tag name, and category name exactly as shown in the database? If not, a value of zero will be returned.

Can you test each of the SELECT queries using phpMyAdmin (or similar) to be sure you have each one correct?
Kahil
Member
Posted 2 years ago #

They are correct. I've done this before. I can get it to show a sum, but only for either the tag or the category...I can't figure out how to get it to narrow it down by both factors.
vtxyzzy
Member
Posted 2 years ago #

If both of the SELECT object_id subqueries works to give the correct list of object_id's, make sure that they have id's in common. If they do, I am at a loss to explain the result.

I am out of ideas - sorry.
Kahil
Member
Posted 2 years ago #

Ok...I have another idea that maybe someone could help me with on this...

ok...what if I give this section an ID. Is there a way to create a form of sort that would add up the values of each occurrence of that ID?
adambundy
Member
Posted 1 year ago #

I am trying to do the same thing but without the sum. I just want a list of unique meta_key values for a given category (the current category).

Topic Closed

This topic has been closed to new replies.

WordPress.org

Forums

Add up the values of custom fields... (23 posts)

Topic Closed

About this Topic

Tags

Code is Poetry