Export list of pretty permalinks and post title

3 comments

1. Here’s a standalone PHP file you can save into the root of your website called something like /export.php and when you call it with your browser it will send a tab-delimited plain text list of posts with the pretty permalink, the post title and (as a bonus) the post type.

   Just load the URL in your browser and then “save as” to a text file you can then load in Excel or however else you need to process it.

   <?php
   
   include "wp-load.php";
   
   $posts = new WP_Query('post_type=any&posts_per_page=-1&post_status=publish');
   $posts = $posts->posts;
   /*
   global $wpdb;
   $posts = $wpdb->get_results("
       SELECT ID,post_type,post_title
       FROM {$wpdb->posts}
       WHERE post_status<>'auto-draft' AND post_type NOT IN ('revision','nav_menu_item')
   ");
   */
   
   header('Content-type:text/plain');
   foreach($posts as $post) {
       switch ($post->post_type) {
           case 'revision':
           case 'nav_menu_item':
               break;
           case 'page':
               $permalink = get_page_link($post->ID);
               break;
           case 'post':
               $permalink = get_permalink($post->ID);
               break;
           case 'attachment':
               $permalink = get_attachment_link($post->ID);
               break;
           default:
               $permalink = get_post_permalink($post->ID);
               break;
       }
       echo "n{$post->post_type}t{$permalink}t{$post->post_title}";
   }
   

   Hope this helps.

   -Mike

   P.S. I used the standard WordPress WP_Query() but also included a commented-out SQL in case you prefer (or need) to use it instead.

   Log in to Reply

2. answered this one on EE this morning 🙂
   http://wp.daveheavyindustries.com/2011/02/08/wordpress-permalink-via-sql/

   this query should do it for you

   SELECT  wpp.post_title,
           wpp.guid,
           wpp.post_date,
           CONCAT
           (
             wpo_su.option_value,
             REPLACE
             (
               REPLACE
               (
                 REPLACE
                 (
                   REPLACE
                   (
                     wpo.option_value, 
                     '%year%',
                     date_format(wpp.post_date,'%Y')
                   ),
                   '%monthnum%',
                   date_format(wpp.post_date, '%m')
                 ),
                 '%day%',
                 date_format(wpp.post_date, '%d')
               ),
               '%postname%', 
               wpp.post_name
             )
           ) AS permalink
     FROM wp_posts wpp
     JOIN wp_options wpo
       ON wpo.option_name = 'permalink_structure'
      AND wpo.blog_id = 0
     JOIN wp_options wpo_su
       ON wpo_su.option_name = 'siteurl'
      AND wpo_su.blog_id = wpo.blog_id
    WHERE wpp.post_type = 'post'
      AND wpp.post_status = 'publish'
    ORDER BY wpp.post_date DESC 
   

   Log in to Reply

3. I also wanted this solution and thanks @MikeSchinkle for the original solution. I did use this to export those links in plain text to excel and then build my redirect list.

   But then I found that I also wanted a solution with live, active links.

   So I used the wp_query using the post type “any” and created a page template with a search form included with the following query (customize to fit your theme as you see fit). Note I had to set the posts_per_page at -1 to return unlimited results. This returns results as: “Title – Permalink”

   <?php 
           $type = 'any';
           $args = array (
            'post_type' => $type,
            'post_status' => 'publish',
            'posts_per_page' => -1,
             'order' => 'DESC',
   
           );
           $temp = $wp_query; // assign ordinal query to temp variable for later use  
           $wp_query = null;
           $wp_query = new WP_Query($args); 
           if ( $wp_query->have_posts() ) :
               while ( $wp_query->have_posts() ) : $wp_query->the_post();
               ?>
   
                   <?php the_title(); ?> - <a href="<?php the_permalink() ?>" rel="bookmark" title="View The <?php the_title_attribute(); ?>"><?php the_permalink() ?></a><br />
   <?php    endwhile; ?>
   <?php else :
               echo '<h2>Sorry, we didnt find any results to match.  Please search again below or call us at 800-828-4228 and we will be happy to help!</h2>';
               get_search_form();
           endif;
   
     $wp_query = null; 
     $wp_query = $temp;  // Reset
   ?>
   

   Hope that helps others.

   Log in to Reply