WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Im looking to count how many meta keys exist within a post, because each time a specific user action happens, a new meta key is created with a date in it.

I have this, which does not return anything (I'm looking for a number)...

        ...
$post_title = $row->post_title;
        $id = $row->ID;

$post_count = $wpdb->get_var("
                SELECT COUNT(DISTINCT $wpdb->postmeta.meta_key) FROM $wpdb->posts
                LEFT JOIN $wpdb->postmeta 
                WHERE $wpdb->posts.post_type = 'things'
                AND $wpdb->postmeta.meta_key = 'dates' 
                AND post_id = $id
                ");

        echo $post_title . ' (' . $post_count . ')' . "\n";
    }

Similarly, I'd like to count the number of meta keys in total related to a custom post, any help on either of these would be greatly appreciated!

share|improve this question

I solved this problem with some more trial and error:

This got me the # of post meta with the correct id,

...
        $post_title = $row->post_title;
        $id = $row->ID;

$post_count = $wpdb->get_var("
                SELECT COUNT(*) FROM $wpdb->postmeta
                WHERE post_id = $id
share|improve this answer
    
Since WP retrieves and caches all the post meta to go along with the main query, you'd be better off using answer wordpress.stackexchange.com/a/223700/94267 – Andy Macaulay-Brook 5 hours ago

You have a structural problem with your data. Serialized data in the database is terrible if you need to search over pieces of that serialized data.

There is no reliable, efficient, and certainly no easy, SQL query to search over serialized data. "serialization" is a PHP mechanism. It isn't SQL. To the database that is just a string. Your only SQL choice is a regex on the string. WP_Query certainly won't do it. That functionality is not built in, probably for the reason listed above. Iterating over the data is about the only solution you have given the data structure you are dealing with.

However, the correct solution in my opinion is to alter your data structure so that you are saving granular pieces of data in the database. In other words, use a lot of different keys for the values that you need to search over.

share|improve this answer
    
The metadata is stored as unique records and the questioner wants to count those records. It doesn't matter whether some of the data is serialised or not. – Andy Macaulay-Brook May 19 at 15:29

You can simply do : count( get_post_custom_keys($pid) )

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.