PHP: memory issues while looping thousand of items

Roger TM Published at Dev

497369784:

Roger TM

This is a WordPress with Woocommerce project.

I have to loop an array with more than 200 thousand of items. Every item is an order's ID in the data base. I have to sum the value of every order's meta to get the total sum. But the loop allways ends with memory issue.

Here is my code:

function woq_orders_total(){
    $orders = get_option( 'woq_orders_ids' ); // array of IDs
    $meta = array();

    $i = 0;
    foreach( $orders as $order ) :
        $meta[] = get_post_meta( $order, '_order_total', true );
        if ( $i % 500 == 0 )
            sleep( 5 ); // stop for 5 seconds every 500 items

        $i++;
    endforeach;

    update_option( 'woq_orders_total', array_sum( $meta ) );
}

I've try stoping every 100 items, but the problem persists. This works well with a few numbers of items, but not with this big amount.

Thanks for any help

Roger TM

I've found a solution thanks to the comment by Markus AO

Now my code looks like this:

function woq_orders_total(){
    global $wpdb;
    $orders = get_option( 'woq_orders_ids' );
    $orders = join( ',', $orders );
    $meta_key = '_order_total';

    $total = $wpdb->get_var(
        $wpdb->prepare(
            "
            SELECT SUM(meta_value)
            FROM $wpdb->postmeta
            WHERE meta_key = %s
            AND post_id IN ({$orders})
            ",
            $meta_key
        )
    );

    update_option( 'woq_orders_total', $total );
}

Also these comment in $wpdb documentation help a lot https://developer.wordpress.org/reference/classes/wpdb/#comment-1975

Thanks you all.

Collected from the Internet

Please contact [email protected] to delete if infringement.