WARNING: ANSWER NOT COMPLETE - CODE NEEDS SOME CHANGES & EXPLANATION. WILL BE UPDATED SOON
I'm not familiar with the Woocommerce plugin and couldn't have tested this code, so don't delete yours. Chceck if it works and gives you sameexpected result. I can't tell if you really use the Woocommerce data structure properly, but using the woocommerce_order_itemmeta table in this context scares the hell out of me and if there's no better access to ordered items then you're the last one to blame for unefficient code.
The tweaks are based on some ground rules of querying database (mentioned in other answers as well):
- Avoid queries inside loops
- Ask for as accurate result as you can (don't fetch sth you won't use)
First one could be achieved, but second needs some hack'ish and not fully efficient workarounds, because ability to limit and sort results is limited by given data structure (see comments within code).
// Find first item id with future booking date so theto LIMITease couldnext be(slow) usedquery.
// IIf amstring notformat sureis known and consistent think of thefiltering date/timewith stringmysql format,STR_TO_DATE() sofunction.
// I can'twill compareuse usingphp STR_TO_DATEstrtotime() to filter them instead.
$items = $wpdb->get_results("
SELECT m1.order_item_id AS id, m1.meta_value AS date, m2.meta_value AS time
FROM {$wpdb->prefix}woocommerce_order_itemmeta m1
INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta m2 ON m2.order_item_id = m1.order_item_id
WHERE m1.meta_key = 'Booking Date'
AND m2.meta_key = 'Booking Time'
ORDER BY m1.order_item_id ASC
");
$now = time();
$data = array();
foreach ($items as $item) {
if$timestamp (= strtotime($item->date . ' ' . $item->time);
if ($timestamp > $now) {
$first_item = $item // $timestamp is used only for sorting and will be unset later,
// may be used instead 'date' and 'time' fields though.
$data[$item->id;>id]['timestamp'] break;= $timestamp;
}
}
// Initially sorted array with ids as key.
// Other data will be inserted here from next query
// thus the order will be maintained.
asort($data);
// list of ids that match future timestamp
// (can't simply LIMIT to 10 because upcoming conditions could filter them out)
$item_list = implode(', ', array_keys($data));
// helper array for fast check and key assign
$meta_keys = array(
'Booking Date' => 'date',
'Booking Time' => 'time',
'First Name - First Name' => 'fname',
'Church Information - Church Name' => 'church',
'Church Information - City' => 'city',
'Church Information - State' => 'state'
);
// Slow query, but only one - nested loop queries are killers.
$item_meta = $wpdb->get_results("
SELECT m.order_item_id, m.meta_key, m.meta_value
FROM {$wpdb->prefix}woocommerce_order_itemmeta m
INNER JOIN {$wpdb->prefix}woocommerce_order_items i ON m.order_item_id = i.order_item_id
INNER JOIN {$wpdb->prefix}posts p ON p.ID = i.order_id
INNER JOIN {$wpdb->prefix}term_relationships r ON r.object_id = p.ID
WHERE r.term_taxonomy_id = 11
AND p.post_type = 'shop_order'
AND p.post_status = 'publish'
AND m.order_item_id >=IN ({$first_item$item_list}
ORDER BY m.order_item_id ASC
LIMIT 10)
");
foreach ($item_meta as $item) {
if (!isset($meta_keys[$item->meta_key])) { continue; }
$id = $item->order_item_id;
$key = $meta_keys[$item->meta_key];
$data[$id][$key] = $item->meta_value;
}
$count = 0;
foreach ($data as $item) {
// skip results not found by second query
if (!isset($item['time'])) { continue; }
$time = $item['time'];
$date = $item['date'];
unset($item['time'], $item['date'], $item['timestamp']);
$details = implode(',<br>', $item);
?>
<div class="wc-upcoming-booking">
<div class="wc-upcoming-time">
<span class="upcoming-hour"><?php echo $time; ?></span>
<span class="upcoming-date"><?php echo $date; ?></span>
</div>
<div class="wc-upcoming-details">
<?php $detailsecho $details; ?>
</div>
</div>
<?php
$count++;
if ($count == 10) { break; }
}
Note: I assume that sorting by Booking Time/Date may differ from sorting by order_item_id. (Further optimization possible if assumption is wrong).
