php - Returning and Compiling MYSQL data for WooCommerce Product Orders -

what trying achieve way search our woocommerce orders based on item sku in orders have been made. once sku searched, display items orders skus containing search query, , give data it.

i've got close not sure going wrong here.

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 code, setup return items based on order number searching mysql database , combining data 2 tables share 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 connect 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 * wp_woocommerce_order_items t1 inner join wp_woocommerce_order_itemmeta t2 on t1.order_item_id = t2.order_item_id 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 current given result order number search of "3003", , can see repeating each item new row (it seems caused based on copying new row each meta_value each item): http://tinyurl.com/npml5jn

thank much!

you don't need run custom mysql queries against of this. dropping db creds in not cool.

first, seem little backwards on running queries within wordpress/woocommerce. there's class called wc_order let pull through data needed.

$order = new wc_order('3003'); 

this returns object can kinds of neat things to--specifically, order items.

$items = $order->get_items(); 

this return array of items/products purchased in order.

the skew data going associated product rather order or order-item meta data. depending on extension installed, info such skew may or may not added order item meta when order created. sure fire way run function post-meta data based on prodcut id of order item.

//  '&' symbol allows store data array foreach ( $items &$item ) :      $productid = $item['product_id'];      //get array of      $sku = get_post_meta($productid, 'sku', true);      $item['sku'] = array( 'productid' => $productid, 'sku' => $sku );  endforeach; 

you iterate through $items display need.

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

if you're trying access data outside of wordpress plugin theme, woocommerce rest api. hope helps.