You have two options: the Wordpress way and vice versa, SQL.
Just before I start, you should never hardcode the db name, the problem is that you will need to change the db name in all requests, where is the hard code if you move the code to another site with a different db name, also when changing the db name on the site you are working on. This can lead you to a wild goose chase if you forget about it.
You should always always sanitize and verify the input to ensure that any malicious code is embedded in your site. SQL injection is common, and many hackers use SQL injection to hack a site
SQL
SQL gives a little less control because it is less dynamic. For example, if you need to get an invoice only for a certain term, you will need to directly change the request or you will need to implement some kind of filter system.
As I said before, never write down db hardcode names, rather use the wpdb class to set the prefix. This will avoid problems in the future. In addition, as I said, you need to sanitize to avoid SQL injection, in which case we will use the prepare method of the wpdb class to care for sanitation and will protect against SQL injection.
Here is the function I commented here and there to make sense:
function get_post_meta_key_count( $key = '', $value = '', $type = 'post', $status = 'publish' ) { // Use $wpdb to avoid bugs and errors, do not hardcode db names global $wpdb; if( empty( $key ) ) return; // Set the default WHERE clause where we return the count of the key regardless of value $where = $wpdb->prepare( " pm.meta_key = '%s' AND p.post_type = '%s' AND p.post_status = '%s' ", $key, $type, $status ); // If a value is specified, add that to the WHERE clause if ( $value ) { $where .= $wpdb->prepare( " AND pm.meta_value = '%s' ", $value ); } // Query the db to return the post count according to key and value if value is set $count = $wpdb->get_var( " SELECT count(DISTINCT pm.post_id) FROM {$wpdb->postmeta} pm JOIN {$wpdb->posts} p ON (p.ID = pm.post_id) WHERE {$where} " ); return $count; }
What I did here, I set a parameter if you ever need to count a specific meta value for a particular meta key.
USING
Now you can use the function as follows:
echo get_post_meta_key_count( 'my_key' ); for the number of posts meta_key my_key for the default post and only published posts
echo get_post_meta_key_count( 'my_key', '', 'custom_post_type', 'trash' ); for the meta_key column meta_key for the my_key custom message custom_post_type and only processed messages
echo get_post_meta_key_count( 'my_key', 'my_value' ); for the number of messages meta_key my_key and for meta_value , my_value for the default post and only published messages
WP_Query
If you are looking for a built-in way to do this (this should always be your first option), you can use the WP_Query class to do this.
The problem with using WP_Query is that it can be very expensive if not used correctly. Many people shun WP_Query because of this or unknowingly run queries that are quite expensive and unnecessary. So we’ll look at a way to do this as fast as a custom SQL query.
The real advantage of WP_Query over a custom SQL query is that you can adjust the query by simply passing the necessary parameters, WP_Query will always take care of the hard work to build the correct SQL query according to the passed parameters.
Let's take a look at WP_Query optimization to simply return the number of posts
First, we will only request one entry. WP_Query is created by default so that no matter how many queries are requested (1, 100 or all messages), WP_Query will continue to search for all messages matching the query, although it has already found and returned the requested number of messages. The reason for this is pagination. To correctly calculate pagination, WP_Query needs to know how many posts there are, which matches the query.
So WP_Query continues to browse db after returning the number of queries requested to count all messages matching the query. This $found_posts counter is stored in the $found_posts request, and this number is used in combination with posts_per_page , which is used to calculate the number of pages.
This does not work for get_posts , although get_posts uses WP_Query . get_posts passes 'no_found_rows' => true to WP_Query , which splits the query as soon as the number of requested messages is found. This legally breaks the pagination, so get_posts is such a huge headache to draw pages correctly
Secondly, we will request only one message identifier to be requested, and not the full WP_Post object. This saves query time.
So, consider this function:
function get_post_meta_key_count( $key = '', $value = '', $args = [] ) { // If the $key value is empty, bail if( empty( $key ) ) return; // Set the defaults and override any value set for the specific default $args['posts_per_page'] = 1; $args['fields'] = 'ids'; if ( $value ) { $args['meta_query'][] = [ 'key' => $key, 'value' => $value ]; } else { $args['meta_query'][] = [ 'key' => $key, ]; } $q = new WP_Query( $args ); // Return the post count return $q->found_posts; }
USING
WP_Query uses the post and publish message type by default as the message status, so we do not need to set this for regular published messages. I have included a third parameter called $args , this parameter takes the exact same parameters as the WP_Query class, since they are all passed directly to WP_Query . So you can add any parameters here to get the score from a specific meta key or value
echo get_post_meta_key_count( 'my_key' ); for the number of posts meta_key my_key for the default post and only published posts
echo get_post_meta_key_count( 'my_key', 'my_value' ); for the number of messages meta_key my_key and for meta_value , my_value for the default post and only published messages
echo get_post_meta_key_count( 'my_key', 'my_value', ['post_type'=>'cpt', 'cat'=>1] ); for the number of meta_key messages meta_key and for meta_value , my_value for the custom cpt message cpt and only published messages from category 1 identifier
24-message meta_key USE TEST
As you can see, the difference in performance is not significant, therefore the WP_Query method will be a better option, as it is much more dynamic, albeit a little slower