Am running a db query here, and using mysqli with MYSQLI_USE_RESULT in order to execute the query faster. But there is php manipulation of data needed here, so it's not as simple as just writing to a CSV file on each row returned. I know that the database query is finishing (even though it is taking a long time), but the db query does finish, so the problem has to be in the PHP memory side, cause I'm getting a 504 Gateway Timeout Error. My code for this is as follows:
global $wpdb, $root_dir;
if (!defined('ABSPATH'))
$root_dir = dirname(__FILE__) . '/';
else
$root_dir = ABSPATH;
require_once($root_dir . 'wp-config.php');
$wp->init();
$wp->parse_request();
$wp->query_posts();
$wp->register_globals();
$start_date = !empty($_GET['start']) ? DateTime::createFromFormat('Y-m-d', $_GET['start']) : '';
$end_date = !empty($_GET['end']) ? DateTime::createFromFormat('Y-m-d', $_GET['end']) : '';
$append = array();
if (!empty($start_date))
$append[] = $start_date->format('Y-m-d');
if (!empty($end_date))
$append[] = $end_date->format('Y-m-d');
$filename = 'user-export' . (!empty($append) ? '_' . implode('_', $append) : '');
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header('Content-Description: File Transfer');
header("Content-type: text/csv");
header("Content-Disposition: attachment; filename=\"" . $filename . ".csv\"");
header("Expires: 0");
header("Pragma: public");
ini_set('memory_limit', '-1');
ini_set('max_execution_time', '-1');
set_time_limit(0);
$headers = array('Order Date/Time', 'Name', 'Billing Address', 'Shipping Address', 'Phone', 'Email Address');
$meta_keys = array('_billing_first_name', '_billing_last_name', '_billing_address_1', '_billing_address_2', '_billing_city', '_billing_state', '_billing_postcode', '_billing_email', '_billing_country', '_billing_phone', '_shipping_address_1', '_shipping_address_2', '_shipping_city', '_shipping_state', '_shipping_postcode', '_shipping_country');
$csv_data = $emails = $order_ids_skipped = $meta = array();
// Using an unbuffered query...
$mysqli = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
if (mysqli_connect_errno()) {
printf("Connect failed: %s
", mysqli_connect_error());
exit();
}
$uresult = $mysqli->query("
SELECT p.ID, p.post_date, pm.meta_key, pm.meta_value
FROM {$wpdb->posts} AS p
INNER JOIN {$wpdb->postmeta} AS pm ON (pm.post_id = p.ID AND pm.meta_key IN ('" . implode("','", $meta_keys) . "'))
WHERE p.post_type = 'shop_order' AND p.post_status IN ('wc-sent-delivery', 'wc-awaiting-delivery', 'wc-completed')" . (!empty($start_date) && !empty($end_date) ? " AND (p.post_date >= '" . $start_date->format('Y-m-d') . " 00:00:00' AND p.post_date <= '" . $end_date->format('Y-m-d') . " 23:59:59')" : "") . "
ORDER BY p.post_date DESC", MYSQLI_USE_RESULT);
if ($uresult)
{
while ($order_query = $uresult->fetch_assoc())
{
// If the email already exists, continue...
if ($order_query['meta_key'] == '_billing_email' && !empty($emails) && in_array($order_query['meta_value'], $emails))
{
if (isset($csv_data[$order_query['ID']]))
unset($csv_data[$order_query['ID']]);
if (isset($meta[$order_query['ID']]))
unset($meta[$order_query['ID']]);
$order_ids_skipped[] = $order_query['ID'];
continue;
}
if (in_array($order_query['ID'], $order_ids_skipped)) continue;
if (!isset($csv_data[$order_query['ID']]))
$csv_data[$order_query['ID']] = array(
'order_date' => $order_query['post_date']
);
$meta[$order_query['ID']][$order_query['meta_key']] = $order_query['meta_value'];
if ($order_query['meta_key'] == '_billing_email')
$emails[] = $order_query['meta_value'];
}
}
$uresult->close();
if (!empty($meta))
{
foreach($meta as $order_id => $meta_data)
{
$billing = array(
'addr1' => !empty($meta_data['_billing_address_1']) ? $meta_data['_billing_address_1'] : '',
'addr2' => !empty($meta_data['_billing_address_2']) ? $meta_data['_billing_address_2'] : '',
'city' => !empty($meta_data['_billing_city']) ? $meta_data['_billing_city'] : '',
'state' => !empty($meta_data['_billing_state']) ? $meta_data['_billing_state'] : '',
'zip' => !empty($meta_data['_billing_postcode']) ? $meta_data['_billing_postcode'] : '',
'country' => !empty($meta_data['_billing_country']) ? $meta_data['_billing_country'] : ''
);
$shipping = array(
'addr1' => !empty($meta_data['_shipping_address_1']) ? $meta_data['_shipping_address_1'] : '',
'addr2' => !empty($meta_data['_shipping_address_2']) ? $meta_data['_shipping_address_2'] : '',
'city' => !empty($meta_data['_shipping_city']) ? $meta_data['_shipping_city'] : '',
'state' => !empty($meta_data['_shipping_state']) ? $meta_data['_shipping_state'] : '',
'zip' => !empty($meta_data['_shipping_postcode']) ? $meta_data['_shipping_postcode'] : '',
'country' => !empty($meta_data['_shipping_country']) ? $meta_data['_shipping_country'] : ''
);
$csv_data[$order_id]['name'] = !empty($meta_data['_billing_last_name']) ? trim($meta_data['_billing_first_name']) . ' ' . trim($meta_data['_billing_last_name']) : trim($meta_data['_billing_first_name']);
$csv_data[$order_id]['billing'] = implode(', ', array_filter($billing));
$csv_data[$order_id]['shipping'] = implode(', ', array_filter($shipping));
$csv_data[$order_id]['phone'] = !empty($meta_data['_billing_phone']) ? $meta_data['_billing_phone'] : '';
$csv_data[$order_id]['email'] = !empty($meta_data['_billing_email']) ? $meta_data['_billing_email'] : '';
}
if (!empty($csv_data))
build_csv_file($csv_data, $headers);
}
// No need to continue here.
exit;
function str_putcsv($input, $delimiter = ',', $enclosure = '"')
{
// $fp = fopen('php://temp', 'r+b');
$fp = fopen('php://output', 'r+b');
fputcsv($fp, $input, $delimiter, $enclosure);
rewind($fp);
rtrim(stream_get_contents($fp), "
");
fclose($fp);
// return $data;
}
function build_csv_file($data, $headers)
{
global $root_dir;
$output = '';
if (empty($data))
return array();
if (!empty($headers))
str_putcsv($headers, ',', '"') . PHP_EOL;
foreach($data as $part)
str_putcsv($part, ',', '"') . PHP_EOL;
}
Saving this file as testing.php and executing the following url like so from the root of wordpress:
https://example.com/testing.php?start=2010-03-05&end=2018-03-05
Just returns a 504 gateway error. This is definitely a PHP Memory Limit problem, however, I thought that a CSV file can be streamed and even using php://output here. Not sure how to better handle this in order to reduce PHP Memory being used, or if there is even a way to do this properly while still maintaining the data correctly.
Can anyone help me please? Very much appreciated. I am using PHP version 7.2 I believe. You'd think that php would be able to handle memory better these days. I believe my PHP Memory is maxed out at 1024MB. I seem to be exceeding the PHP Memory limit all of the time anymore. Is becoming a real nuisance.
