Getting post-thumbnails from another WP site

2 comments

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.

   Log in to Reply

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.

   Log in to Reply