Querying Database with wpdb

Question

I am trying to access a single value from my database through my functions.php. I have tried three different ways to get a result using a dynamic ID and none have worked. I always get NULL as the response. Using a WP_query is not possibility here, so I need to solve this using SQL.

Attempt #1:

global $post;
global $wpdb;
$post_ID = $post->ID;

$result = $wpdb->get_var($wpdb->prepare( 
"
    SELECT meta_value 
    FROM $wpdb->postmeta 
    WHERE post_id = %d
    AND meta_key = 'wpcf-release-date' 
", 
$post_ID
) );

Attempt #2:

$result = $wpdb->get_var("SELECT meta_value FROM $wpdb->postmeta WHERE meta_key = 'wpcf-release-date' AND post_id = $post_ID");

Attempt #3:

$result = $wpdb->get_var("SELECT meta_value FROM $wpdb->postmeta WHERE meta_key = 'wpcf-release-date' AND post_id = " . $post_ID);

I know my query works, I tested the SQL query in Phpmyadmin with a static ID. I can also set the ID manually in my function which will yield a result, but does not help me if it is not dynamic.

SQL Query

SELECT  `meta_value` 
FROM  `wp_postmeta` 
WHERE post_id = 249
AND meta_key =  'wpcf-release-date'

I assume I am overlooking something simple.

Answer 1

I tested your all attempts in a shortcode:

function add_test_shortcode() {
  global $wpdb;
  global $post;
  $post_ID = $post->ID;
  $wpdb->show_errors = true;
  $result = $wpdb->get_var('SELECT meta_value FROM '.$wpdb->postmeta.' WHERE meta_key="my_test" AND post_id=' . $post_ID);

  return $result;
}
add_shortcode( 'test', 'add_test_shortcode' );

It worked perfectly for me, so there might something from your own code:

1. Check for $post_ID

Make sure your $post_ID is valid, get_the_ID() might be a better replacement in so many cases than global $post;.

2. Test your generated query

You can get your last executed query using $wpdb->last_query, execute it in phpMyAdmin or any other software and check that there is really a result.

3. Look if there's any error

You can get the latest error statement using $wpdb->last_error.

My advice to you during WordPress development is that always keep WP_DEBUG on.

Answer 2

Turns out all of my attempts were viable (Thanks ehsaan for also confirming). The issue was that I was trying to get data from a field that was empty at the time of the function. (I was creating a new/updated post that hadn't updated the field yet).

Solution (delay the hook):

// Time out the custom field to update AFTER the post has been created/updated
function save_custom() {
    global $post;
    $post_ID = $post->ID;
    $result = get_post_meta( $post_ID, 'wpcf-release-date', 1 );

    // Add a new key "era"
    add_post_meta($post_ID, "era", $result, true);
    // If there is already a key "era", then update it
    if ( ! add_post_meta($post_ID, "era", $result, true) ) { 
       update_post_meta( $post_ID, "era", $result );
    }
}
add_action('save_post', 'save_custom', 0);
add_action('save_post', 'save_custom', 10);
add_action('save_post', 'save_custom', 999);