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.

First time poster. I'm trying to export a bunch of custom posts out to a csv file so I can easily import them into a new database. I've ran the native export to xml, but it's not the correct format I need. I need a column based format (ie. name, height, weight, etc.).

I have a custom post that has several custom fields. I'm having issues combining them all down to one single row, rather than a new row for each meta type. Is there a way to write a sql query that has numerous selects for each meta type and combines them into one row with multiple columns?

Here is what I have so far, which gives me one column and a new row for each meta type for the same Name

SELECT p.post_title AS Name
FROM wp_posts AS p
INNER JOIN wp_postmeta AS pm 
ON p.ID = pm.post_id
WHERE p.post_type = 'prospects'
ORDER BY p.post_date DESC

Now how would I start adding columns (selects) for each meta type and combine them on one row:

AND (
  SELECT m1.meta_value AS Height
  WHERE m1.meta_key = '_height'
)
AND (
  SELECT m2.meta_value AS Weight
  WHERE m2.meta_key = '_weight'
)
share|improve this question
add comment

2 Answers

up vote 2 down vote accepted

With the help of @toscho pointing out get_post();, I was able to spit out a .csv with a blank template. At first, I had memory limit issues b/c of the amount of data, so I moved the site locally and was able to get everything I need with the get_post();

This is the gist of it:

<ol>
<?php
global $post;
$args = array( 
    'numberposts' => -1,
    'orderby' => 'post_title',
    'order' => 'DESC',
    'post_type' => 'prospects',
    'post_status' => 'publish'
);
$myposts = get_posts( $args );
foreach( $myposts as $post ) : setup_postdata($post); ?>
    <li><?php echo $post->ID ?>, <?php the_title(); ?>, <?php echo get_field('prospect_profile_image'); ?>, <?php echo get_field('height'); ?>, <?php echo get_field('weight'); ?>, <?php echo get_field('born'); ?>, , <?php echo get_field('email'); ?>, <?php echo get_field('phone'); ?>, <?php echo get_field('street_address'); ?>, <?php echo get_field('city'); ?>, <?php echo get_field('state'); ?>, <?php echo get_field('zip_code'); ?>, <?php echo get_field('position_1'); ?>, <?php echo get_field('position_2'); ?>, <?php echo get_field('bats'); ?>, <?php echo get_field('throws'); ?>, <?php echo get_field('fathers_name'); ?>, <?php echo get_field('mothers_name'); ?></li>
<?php endforeach; ?>
</ol>

I used an orderlist at first to make sure I was getting all the records.

share|improve this answer
add comment

Use get_posts() and iterate over the result array. The argument 'numberposts' => -1 will produce a complete list of the posts.

If you look at WP_Query::get_posts() in wp-includes/query.php you may get an idea how terrible complex such a query is (which is no excuse for the code style in this function …).

And welcome to WordPress Stack Exchange!

share|improve this answer
    
Sorry for the delay, wasn't sure on how to subscribe to this thread. Thanks for some insight. I'll give this a try and let you know if I have any issues. –  Siebird Feb 21 '12 at 3:17
    
I'm starting off simple and then I will start to expand the export template to get the data I need. This is what I have so far and it returns the start of the template: <ul> that's it. <?php /* Template Name: Player Export */ ?> <ul> <?php global $post; $args = array( 'numberposts' => -1, 'orderby' => 'post_title', 'order' => 'ASC', 'post_type' => 'prospects', 'post_status' => 'publish' ); $myposts = get_posts( $args ); foreach( $myposts as $post ) : setup_postdata($post); ?> <li><?php the_title(); ?></li> <?php endforeach; ?> </ul> –  Siebird Feb 21 '12 at 4:10
add comment

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.