How to search order using database frontend short code WordPress

Question

Use the below code it may help you and sure that your sql query is correct then it will be work properly.

function wpb_demo_shortcode() {
global $wpdb;
$results = $wpdb->get_results( 
            $wpdb->prepare("SELECT DISTINCT o.order_id, o.`order_item_name`, om.`meta_value` 
                as 'bcs', (select  pm.`meta_value` from `wp_postmeta` 
                as pm WHERE o.order_item_id = om.order_item_id 
                AND pm.`post_id` = o.order_id 
                AND pm.`meta_key` = '2-certificate') 
                as 'certificate', (select  pm.`meta_value` from  `wp_postmeta` 
                as pm WHERE o.order_item_id = om.order_item_id 
                AND pm.`post_id` = o.order_id 
                AND pm.`meta_key` = '3-serial') 
                as 'serial' FROM `wp_woocommerce_order_items` 
                as o, `wp_woocommerce_order_itemmeta` 
                as om, `wp_postmeta` 
                as pm WHERE o.order_item_id = om.order_item_id 
                AND pm.`post_id` = o.order_id
                AND om.`meta_key` = 'bcs'
                AND (select  pm.`meta_value` from `wp_postmeta` 
                as pm WHERE o.order_item_id = om.order_item_id
                AND pm.`post_id` = o.order_id
                AND pm.`meta_key` = '2-certificate') IS NOT null
                AND (select  pm.`meta_value` from `wp_postmeta` as pm WHERE o.order_item_id = om.order_item_id
                AND pm.`post_id` = o.order_id
                AND pm.`meta_key` = '3-serial') IS NOT null") 
            );

$serial = (isset( $_GET['$serial'] )) ? sanitize_text_field($_GET['$serial']) : '';
?>
<form action="" method="post">
  <label for="fname">Serial number:</label><br>
  <input type="text" id="$serial" name="$serial"><br>
  <input type="submit" value="Submit">
</form>
<?php
$enteredSerial = $_GET['$serial'];

ob_start();
 echo '<table><tr>';
    echo '<th>Order ID</th>';
    echo '<th>Product Name</th>';
    echo '<th>BCS</th>';
    echo '<th>Serial</th>';
    echo '<th>Certificate</th>';
    echo '</tr>';

    foreach( $results as $result ){

        // Html display
        echo '<tr>';
        echo '<td>' . $result->order_id . '</td>';
    echo '<td>' . $result->order_item_name . '</td>';
    echo '<td>' . $result->bcs . '</td>';
    echo '<td>' . $result->serial . '</td>';
    echo '<td>' . $result->certificate . '</td>';
    echo '</tr>';
    }

    echo '</table>';
}
add_shortcode('greeting', 'wpb_demo_shortcode');

Related Posts: