Returning and Compiling MYSQL data for WooCommerce Product Orders

2 Views

What I am trying to achieve is a way to search our woocommerce orders based on the item sku in all the orders that have been made. Once an sku is searched, it will display all the items from all orders with skus containing the search query, and it will give the data about it.

I’ve got it very close but not sure what is going wrong here.

Read More

search code: 

<form action="url.php" method="get">
Order Number <input type="text" name="orderNumber" /><br />
<form action="url.php" method="get">
SKU <input type="text" name="itemSku" /><br />
<input type="submit" />
</form>

and here is the code, which is currently only setup to return items based on order number by searching the mysql database and combining data from two tables which share a common row “order_item_id”: 

<?php
$search_ordernumber = $_GET["orderNumber"];
$search_sku = $_GET["itemSku"];
$con=mysqli_connect("****","****","****","****");
// Check connection
if (mysqli_connect_errno()) {
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

echo "<table border='1'>
<tr>
<th>Order ID</th>
<th>Item</th>
<th>OIK</th>
<th>Quantity</th>
<th>Tax Class</th>
<th>Product ID</th>
<th>Variation ID</th>
<th>Subtotal</th>
<th>Tax</th>
<th>Subtotal Tax</th>
<th>Color</th>
<th>Size</th>
</tr>";

$moreresult = mysqli_query($con,"SELECT *
FROM wp_woocommerce_order_items AS t1
INNER JOIN wp_woocommerce_order_itemmeta AS t2 ON t1.order_item_id = t2.order_item_id WHERE order_id='$search_ordernumber'");
while($rows = mysqli_fetch_array($moreresult)) {
echo "<tr>";
echo "<td>" . $rows['order_id'] . "</td>";
echo "<td>" . $rows['order_item_name'] . "</td>";
echo "<td>" . $rows['order_item_id'] . "</td>";
//echo "<td>" . $rows['meta_key'] . "</td>";
echo "<td>" . $rows['meta_value'] . "</td>";
echo "</tr>";
}

echo "</table>";
mysqli_close($con);
?>

and here is the current given result for an order number search of “3003”, and you can see it is repeating each item as a new row (it seems to be caused based on it copying a new row for each meta_value for each item): http://tinyurl.com/npml5jn

thank you so much!

Post Views: 2

Related posts

Leave a Reply

1 comment

  1. You don’t need to run custom mySQL queries against any of this. Dropping your DB creds in like that is not cool.

    First, you seem to be a little backwards on running queries within WordPress/WooCommerce. There’s a class called WC_Order which will let you pull through data as needed.

    $order = new WC_Order('3003');

    This returns an object that we can do all kinds of neat things to–specifically, get order items.

    $items = $order->get_items();

    This will return an array of all the items/products that were purchased in this order.

    The skew data is going to be associated to a product rather than the order or order-item meta data. Depending on the extension installed, info such as skew may or may not be added to the order item meta when the order is created. A sure fire way to get it would be to run a function to get the post-meta data based on the prodcut ID of the order item.

    //  The '&' symbol allows us to store data back to the array
foreach ( $items as &$item ) :

    $productID = $item['product_id'];

    //get an array of all the 
    $sku = get_post_meta($productID, 'sku', true);

    $item['sku'] = array( 'productID' => $productID, 'sku' => $sku );

endforeach;

    You could then iterate through $items to display what you need.

    <table>
    <tr>  
        <th>Product ID</th>
        <th>SKU</th>
    </tr>
<?php foreach( $items as $item ) : ?>
    <tr>
        <td><?php echo $item['product_id']; ?></td>
        <td><?php echo $item['sku']; ?></td>
    </tr>
<?php endforeach; ?>
</table>

    If you’re trying to access the data outside of a WordPress plugin to theme, I would look into the WooCommerce REST API. Hope this helps.