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' )
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
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.
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-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!