Getting post-thumbnails from another WP site

Question

I have two WordPress installations, one at http://inversekarma.in and the other at http://inversekarma.in/photos. The latter is a photoblog, and its theme uses the standard WP post thumbnails (EDIT: featured images, to be precise!). Is there a way to show the most recent thumbnails from the second site on my first site's sidebar?

Answer 1

There are (at least) two (2) ways to approach this:

1. You can query the second database which will require you to maintain the database credentials in two places, or at least in an include file, or

2. You could create a simple JSON feed for your photo blog and consume the feed on your main blog and cache it for a short time. This will cause a slight latency on page load when the cache expires but since both are on the same machine it shouldn't be an issue.

Let me know which you'd prefer and if I'll update the answer to so you how.

UPDATE

To connect to another database you can read more about it here:

• Using wpdb to connect to a separate database

So here's what I hacked together for you, a function called show_thumbnails_2nd_db() which you can copy into your theme's functions.php file:

function show_thumbnails_2nd_db() {
  global $wpdb;
  global $table_prefix;
  $save_wpdb = $wpdb;
  $save_prefix = $table_prefix;
  include_once(ABSPATH . '/photos/database-credentials.php');
  extract($database_credentials);
  $wpdb = new wpdb($DB_USER, $DB_PASSWORD, $DB_NAME, $DB_HOST);
  wp_set_wpdb_vars();

  // This is the code for featured images  
  $sql = <<<SQL
SELECT DISTINCT CONCAT('<img src="',attachment.guid,'"/>') AS url
FROM 
  {$wpdb->posts} attachment
  INNER JOIN {$wpdb->postmeta} ON attachment.ID={$wpdb->postmeta}.meta_value AND {$wpdb->postmeta}.meta_key='_thumbnail_id'
  INNER JOIN {$wpdb->posts} ON {$wpdb->posts}.ID={$wpdb->postmeta}.post_id
WHERE {$wpdb->posts}.post_status='publish'
  AND attachment.post_type='attachment'
ORDER BY attachment.ID DESC
LIMIT 10
SQL;
  $post_urls = $wpdb->get_col($sql);
  if (is_array($post_urls))
    echo implode("\n",$post_urls);

// This is the code for post thumbnails  
//   $sql = <<<SQL
// SELECT DISTINCT {$wpdb->posts}.ID
// FROM {$wpdb->posts}
// INNER JOIN {$wpdb->posts} attachment
//    ON {$wpdb->posts}.ID=attachment.post_parent
// WHERE {$wpdb->posts}.post_status='publish'
//   AND attachment.post_type='attachment'
// ORDER BY attachment.ID
// LIMIT 10
// SQL;
//   $post_ids = $wpdb->get_col($sql);
//   foreach($post_ids as $post_id) {
//     $thumbnail = get_the_post_thumbnail($post_id);
//     if ($thumbnail) {
//       echo $thumbnail;
//     }
//   }
  $table_prefix = $save_prefix;
  $wpdb = $save_wpdb;
}

Note: That the above assumes you have created a database-credentials.php in the root of your photos blog, and it should look like this:

<?php    
$database_credentials = array(
  'DB_NAME' =>  'your_database',
  'DB_USER' =>  'your_db_user',
  'DB_PASSWORD' =>  'your db password',
  'DB_HOST' =>  'localhost',
  'table_prefix' => 'your_photos_db_prefix_',
);

Then inside the /wp-config.php for your photos blog you would replace the sections of code that look like this:

// ** MySQL settings - You can get this info from your web host ** //
/** The name of the database for WordPress */
define('DB_NAME', 'your_database');

/** MySQL database username */
define('DB_USER', 'your_db_user');

/** MySQL database password */
define('DB_PASSWORD', 'your db password');

/** MySQL hostname */
define('DB_HOST', 'localhost');

And this:

$table_prefix  = 'wp_';

With this:

include_once(ABSPATH . '/database-credentials.php');

// ** MySQL settings - You can get this info from your web host ** //
/** The name of the database for WordPress */
define('DB_NAME', $database_credentials['DB_NAME']);

/** MySQL database username */
define('DB_USER', $database_credentials['DB_USER']);

/** MySQL database password */
define('DB_PASSWORD', $database_credentials['DB_PASSWORD']);

/** MySQL hostname */
define('DB_HOST', $database_credentials['DB_HOST']);

$table_prefix  = $database_credentials['table_prefix'];

If you need more explanation just ask in the comments.

Answer 2

Mike's answer is great, but I would use a different SQL query, since the GUID of the attachment post can change when you change servers or move the blog. My version also gives you access to information of the blog post, which is useful if you want to link to it.

SELECT post.ID, post.post_title, attachment_meta.meta_value AS upload_relative_path
FROM {$wpdb->posts} AS post
    JOIN {$wpdb->postmeta} AS post_meta ON (post_meta.post_id = post.ID AND post_meta.meta_key = '_thumbnail_id')
    JOIN {$wpdb->postmeta} AS attachment_meta ON (attachment_meta.post_id = post_meta.meta_value AND attachment_meta.meta_key = '_wp_attached_file')
WHERE post.post_status = 'publish'
    AND post.post_type = 'post'
ORDER BY post.post_date DESC
LIMIT 10

Instead of _wp_attached_file you can also get the _wp_attachment_metadata value, which contains extra info about the image and alternative sizes.