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.

I have custom post type product like wp ecommerce.

There are 30k posts in it. Also have relate custom taxonomy. Basically am trying to export those posts in csv, but query_posts with so many records is giving me trouble...

Any tips???

share|improve this question

3 Answers 3

up vote 1 down vote accepted

WordPress query mechanism drag whole set of results into memory so they are not suited for such large chunks of information.

You can use numberposts and offset in get_posts() to split process into chunks. I am not sure if you will be able to do it in single run even then, so you might need to save how many chunks you processed and start from there next time.

share|improve this answer
    
Exactly what i am thinking, but yet again as i asked how woild i know how many total chunks to process.. i.e how would i get total no. of posts... –  Rajeev Vyas Jul 22 '11 at 13:51
1  
@Rajeev Vyas why bother counting? I'd just do this until get_posts() returns nothing, meaning that you run out of posts. –  Rarst Jul 22 '11 at 13:56

I recently had to do an export for a blog with 34K posts and 75K comments. I was able to get it to work by modifying the WordPress Advanced Export plugin and increasing max_execution_time in php.ini from 30 seconds to around 5 minutes.

The plugin grabs a snapshot of all the post ids in the db then runs the batches in 20 post increments and loops back through until its done. I changed the increments to 40 which sped it up and allowed it to complete in a reasonable amount of time.

Here is an example of how the query is run:

The $where clause below is defined by a long list of if else conditionals based on the plugin options.

$post_ids = $wpdb->get_col("SELECT ID FROM $wpdb->posts $where ORDER BY post_date_gmt ASC");

$pass = 0;
$passes = 1000 + count($categories);
while ( ( $cat = array_shift($categories) ) && ++$pass < $passes ) {
    if ( $cat->parent == 0 || isset($cats[$cat->parent]) ) {
        $cats[$cat->term_id] = $cat;
    } else {
        $categories[] = $cat;
    }
}
unset($categories);


<?php if ($post_ids) {
        global $wp_query;
        $wp_query->in_the_loop = true;  // Fake being in the loop.
        // fetch 20 posts at a time rather than loading the entire table into memory
        while ( $next_posts = array_splice($post_ids, 0, 20) ) {
            $where = "WHERE ID IN (".join(',', $next_posts).")";
            $posts = $wpdb->get_results("SELECT * FROM $wpdb->posts $where ORDER BY post_date_gmt ASC");
                foreach ($posts as $post) {
                    setup_postdata($post); ?>
share|improve this answer
    
get_col will not work if there are 30k records in that query.... get_col internally uses for loop, which will nor run for 30k time(no. of time a loop is executed is limited in apache/php).. –  Rajeev Vyas Jul 23 '11 at 12:14
    
You have to raise your max execution time in php. We run this export every week. –  Chris_O Jul 23 '11 at 21:53

You could make a query by month or year to reduce the number of posts. Then you would have a couple of smaller .csv that you can merge together.

Also, you can try to export in .csv directly from the DB in phpMyAdmin.

share|improve this answer
    
phpMyAdmin will not be usefull, these are actually products and not post so no monthly archive will not be logical... I am trying now to give option to export in chunks of thousand... can i get total no. posts for any custom post type?? so i can divided them in chunks of thousand .... –  Rajeev Vyas Jul 22 '11 at 13:28
    
"export […] in phpMyAdmin" phpMyAdmin is also bound to the execution time limits for PHP-scripts; if thats still not working, one may use mysqldump from a console. e.g. mysqldump --host=DB_HOST -u DB_USER -p --databases DB_NAME --tables TABLE_NAME | gzip -9 > dump-date +%Y-%m-%d_%Hh%Mm%Ss`.sql.gz` @Rajeev Vyas "phpMyAdmin will not be usefull, these are actually products" what does that have to with phpMyAdmin—you may export any table or dataset you want… –  feeela Jul 22 '11 at 13:32
    
@feeela phpMyAdmin will not be usefull bcoz am trying to provide an interface for admin(non technical) to export products relating custom taxonomies and want csv in specific format not just dump the table. e.g want only product title and status(custom taxonomy) to export... –  Rajeev Vyas Jul 22 '11 at 13:38

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.