Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

Up until now, my client has been using WordPress posts as his way to display his products. I now need to export all these posts into a CSV format so that I can format the CSV file and import it back into WP e-Commerce.

I've looked at number of options including plugins to export the posts, exporting directly from phpMyAdmin and using the XML export option within WP but none is coming out as clean as I would like it.

I think I basically need an SQL query that I can run to export only the information from the POSTS table, excluding any other rubbish such as ID numbers, ping status, guid etc.

All I need is:

  • Post Title
  • Post Content
  • Custom Fields
  • Categories

Here's the start of my Query

 SELECT * 
 FROM  `wp_posts` 
 WHERE  `post_status` =  'publish'

EDIT: I am open to better solutions if anyone thinks there is a better way to do this?

EDIT

Here's a solution I found on SO but I am not sure where to use this?

<?php
    function array2xml($array, $name='array', $standalone=TRUE, $beginning=TRUE)
    {
    global $nested;

    if ($beginning)
    {
    if ($standalone) header("content-type:text/xml;charset=utf-8");
    $output .= '<'.'?'.'xml version="1.0" encoding="UTF-8"'.'?'.'>' . PHP_EOL;
    $output .= '<' . $name . '>' . PHP_EOL;
    $nested = 0;
    }

    // This is required because XML standards do not allow a tag to start with a number or symbol, you can change this value to whatever you like:
    $ArrayNumberPrefix = 'ARRAY_NUMBER_';

    foreach ($array as $root=>$child)
    {
    if (is_array($child))
    {
    $output .= str_repeat(" ", (2 * $nested)) . '  <' . (is_string($root) ? $root : $ArrayNumberPrefix . $root) . '>' . PHP_EOL;
    $nested++;
    $output .= array2xml($child,NULL,NULL,FALSE);
    $nested--;
    $output .= str_repeat(" ", (2 * $nested)) . '  </' . (is_string($root) ? $root : $ArrayNumberPrefix . $root) . '>' . PHP_EOL;
    }
    else
    {
    $output .= str_repeat(" ", (2 * $nested)) . '  <' . (is_string($root) ? $root : $ArrayNumberPrefix . $root) . '><![CDATA[' . $child . ']]></' . (is_string($root) ? $root : $ArrayNumberPrefix . $root) . '>' . PHP_EOL;
    }
    }

    if ($beginning)
    $output .= '</' . $name . '>';

    return $output;
    }

    //connect to database and select database (edit yourself)
    mysql_connect("localhost", "username", "password");
    mysql_select_db("databasename");

    //Get all posts whose status us published.
    $result = mysql_query("SELECT * FROM wp_posts WHERE post_status = 'publish'");
    while($row = mysql_fetch_assoc($result))
    $posts[] = $row;

    //convert to array and print it on screen:
    echo "<pre>";
    echo htmlentities(array2xml($posts, 'posts', false));
    echo "</pre>";
?>

EDIT:

Right, so I created a php file called pull-posts.php and dropped the above code into the file then saved the file in the root of my localhost installation. I then changed the DB details and ran the script. Here's the error I am getting:

Warning: mysql_connect() [function.mysql-connect]: Access denied for user 'username'@'localhost' (using password: YES) in C:\inetpub\wwwroot\Xampp-1.7.7\htdocs\project\pull-posts.php on line 40

Line 40 has this on it:

mysql_connect("localhost", "root", ""); mysql_select_db("DBName");

Any thoughts? Thanks.

share|improve this question
    
That's a SQL question and off-topic. Two notes: You need to JOIN the *_postmeta table and the * is where you insert the columns. –  kaiser Sep 3 '12 at 13:42
    
Thanks Kaiser, but where else would I ask this question if it is off topic and WordPress uses MySQL for its DB? Thanks for the step closer though. –  SixfootJames Sep 3 '12 at 13:52
    
I'd suggest SO. Tell them how the DB tables looks and what you need. But don't open another question. I already flagged it for moving it there. –  kaiser Sep 3 '12 at 13:56
    
You probably won't get away with SQL alone and this might actually be easier and little more safe with some PHP (taxonomy schema in WP is naaasty). Thus WP specific and not SO, leaving here. –  Rarst Sep 3 '12 at 14:55

1 Answer 1

You don't have to export and then re-import, you can do it all via the API in one go, no XML required. here's a quick example plugin:

<?php
/*
Plugin Name: WPA_convert_types
*/

function wpa_convert_types_page() {
    add_management_page(
        'WPA convert types',
        'WPA convert types',
        'manage_options',
        'wpa_convert_types',
        'wpa_convert_types_render_page'
    );
}
add_action('admin_menu', 'wpa_convert_types_page');

function wpa_convert_types_render_page() {

    if( isset( $_POST['wpa_do_convert'] ) ):
        $args = array(
            'posts_per_page' => -1
        );
        $posts_query = new WP_Query( $args );

        if( $posts_query->have_posts() ):
            global $post;
            while( $posts_query->have_posts() ):
                $posts_query->the_post();

                $new_product = array(
                    'post_type' => 'product',
                    'post_status' => 'publish',
                    'post_title' => $post->post_title,
                    'post_content' => $post->post_content,
                );

                $product_id = wp_insert_post( $new_product );

                // use get_post_meta, wp_get_object_terms
                // to get meta and categories,
                // use $product_id to associate with new product
                // via wp_set_object_terms, add_post_meta

            endwhile;

            echo 'done';

        endif;

    else:
        ?>
        <form method="post">
            <input type="submit" name="wpa_do_convert" value="go">
        </form>
        <?php
    endif;
}

You'll have to add meta and category handling, see the comments below the wp_insert_post line, but this should get you started.

EDIT- I keep forgetting that wp_insert_post lets you set taxonomy terms within the function, see the codex entry for adding categories.

share|improve this answer
    
Hi Milo. This is a great start, thank you very much! I am very unfamiliar with get_post_meta, wp_get_object_terms etc, but I will do some reading on this now to see if I come right with it. –  SixfootJames Sep 4 '12 at 9:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.