Can't get expected result from a wpdb query

Question

Having some trouble with my first attempt at a direct DB query using $wpdb. I'm using the WordTwit plugin, and trying to retrieve the short url extension for the current post that is created by the plugin. This is the code I have:

global $wpdb;
$short = $wpdb->get_var("SELECT url FROM $wpdb->prefix.'tweet_url' WHERE post_id = '$post->ID' ");

As I understand it, this should pull the value of the 'url' in the row where the 'post_id' equals the ID of the current post. However, when I echo $short to test, it's not returning anything. What am I missing?

Answer 1

I know I probably can't see the full picture, but in the excerpt you provided isn't globalizing $post.

global $wpdb, $post;
$short = $wpdb->get_var("SELECT url FROM $wpdb->prefix.'tweet_url' WHERE post_id = '$post->ID' ");

Answer 2

Once the syntax errors are fixed, (see below) your query is telling WordPress you want the url column from the tweet_url table where the post_id column is the id of the post.

Corrected syntax:

$wpdb->get_var( "SELECT url FROM $wpdb->tweet_url WHERE post_id = $post->ID " );

This results in:

WordPress database error: [Table 'wp_tweet_url' doesn't exist]

Looking at the code from the plugin you can see here how they are creating the table, http://plugins.trac.wordpress.org/browser/wordtwit/trunk/wordtwit.php?rev=581895#L1033.

The correct table name is tweet_urls so the correct syntax would be:

$sql = $wpdb->prepare( "SELECT url FROM " . $wpdb->prefix . "tweet_urls WHERE post_id = %d",  get_the_ID() );

$result = $wpdb->get_var( $sql );

var_dump( $result );