1

I asked this in Stackoverflow but figured this would be a more specific domain to answer my question...

I have a large mysql table (in woocommerce) where people can purchase items more than once.

My goal is to be able to group the quantity of the product purchased by user_id so that each row is user and product specific.

user ID | Product | quantity | billing address 
23      | chair   | 4        | 22 Bank street
42      | chair   | 12       | 123 Smith Road
88      | chair   | 5        | 3 Parker avenue

etc

I found this code, but have been unable to generate the table I would need. In future, I would like to add the ability to only show specific products in the table (via SKU) and perhaps their status. I would add drop down menus to dynamically alter the sql query so the table generates what I want. But figuring out how to get the table to generate in the first place is my number one priority!

<?php 
if (!is_user_logged_in() || !current_user_can('manage_options')) wp_die('This page is private.');

$con=mysqli_connect("ip","user","pass","database");
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$result = mysqli_query($con,
"select p.user_id,
       max( CASE WHEN pm.meta_key = '_sku' and p.ID = pm.post_id THEN pm.meta_value END ) as _sku,
       max( CASE WHEN pm.meta_key = '_amount' and p.ID = pm.post_id THEN pm.meta_value END ) as _amount,
       max( CASE WHEN pm.meta_key = '_billing_address_1' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_address_1,
       group_concat(distinct oi.order_item_name separator '|' ) as order_items
from wp_posts p join
    wp_postsmeta pm
    on p.ID = pm.post_id join
    wp_woocommerce_order_items oi
where p.post_type = 'shop_order' and
      p.post_status = 'wc-completed' and
      oi.order_item_name = 'Product Name'
group p.user_id");
echo "<table>";
while($row = mysqli_fetch_array($result))
{
echo "<tr style='font-size: 0.665em'>";
echo "<td>"  . $row['user_id'] . "</td>";
echo "<td>" . $row['amount'] . "</td>";
echo "<td>" . $row['billing_address_1'] . "</td>";
echo "</tr>";
}
echo "</table>";
?>
  • I have a large mysql table (in woocommerce) --- Do you mean i have a woocommerce shop with products OR do you really have a custom DB table? Don't use mysqli in WP, use $wpdb, new WP_Query($args) or get_posts($args) to get data from the DB. – Bjorn Jun 10 '18 at 19:07
  • Nope, its the standard woocommerce tables. I was advised against using those commands... Would they work? Can I use sum arguments with them? – Nicholas Ravanelli Jun 10 '18 at 19:14
  • Actually I would need to make SUM() and GROUP arguments... I've yet to find any info on this/how – Nicholas Ravanelli Jun 10 '18 at 19:28
  • I'm working on a ready to go code snippet for you wait a few minuts – Bjorn Jun 10 '18 at 19:40
  • Awesome, thanks @Bjorn I've been wrestling this idea for a while... – Nicholas Ravanelli Jun 10 '18 at 20:34
0

WooCommerce: Get Order info for all users.

I was trying to help you get going.. After a while i had a complete solution :S...

So here it is, a tested single shortcode that outputs your <table> (WooCommerce 3+ only):

add_shortcode('user_order_info_table', 'sc_user_order_info_table_callback');
function sc_user_order_info_table_callback() {

  $result_array = array();
  $users = get_users();

  if($users) {

    foreach ($users as $user) {
      $products_ordered = array();

      $order_args = array(
        'posts_per_page' => -1,
        'meta_key'    => '_customer_user',
        'meta_value'  => $user->ID,
        'post_type'   => 'shop_order',
        'post_status' => 'wc-completed', // only get completed orders
      );
      $orders = get_posts($order_args);

      if($orders) {
        foreach ($orders as $order) {
          $wc_order = wc_get_order( $order->ID );

          foreach ($wc_order->get_items() as $product) {
            $wc_product = wc_get_product($product->get_product_id());

            if($wc_product) { // make sure the product still exists
              if( ! isset( $products_ordered[$product->get_product_id()] ) ) {
                $products_ordered[$product->get_product_id()] = array(
                  'name' => $product->get_name(),
                  'qty' => $product->get_quantity(),
                );
              } else {
                $products_ordered[$product->get_product_id()]['qty'] = $products_ordered[$product->get_product_id()]['qty'] + $product->get_quantity();
              }

              // get sku example
              //$wc_product->get_sku();
            }
          }
        }
      }

      $customer = new WC_Customer( $user->ID );
      $billing_data = $customer->get_billing('view');
      $user_billing_address1 = $billing_data['address_1'];

      // we have collected all data, save it to array
      $result_array[$user->ID] = array(
        'products_ordered' => $products_ordered,
        'address1' => $user_billing_address1,
      );

    }
  } else {

  }

  // shortcode html output begins hebrev
  $return_html = '<table><thead><tr><td>User ID</td><td>Product</td><td>Quantity</td><td>billing address 1</td></tr></thead><tbody>';

  foreach ($result_array as $user_id => $data) {
    if( isset($data['products_ordered']) && $data['products_ordered'] ) {
      foreach ($data['products_ordered'] as $product_id => $product_data) {
        $return_html .= '<tr>';
        $return_html .= '<td>'.$user_id.'</td>';
        $return_html .= '<td>'.$product_data['name'].'</td>';
        $return_html .= '<td>'.$product_data['qty'].'</td>';
        $return_html .= '<td>'.$data['address1'].'</td>';
        $return_html .= '</tr>';
      }
    }
  }

  $return_html .= '</tbody></table>';

  return $return_html;

}

Place this snippet in your (child) theme functions.php.

Use the shortcode [user_order_info_table] in posts and pages wherever you want.

See it @ work here in my sandbox.

NOTE: it works, but this is not a 'final release' ready for shipping. You probably want to do some more error handling etc.

Regards, Bjorn

  • one word. Legend. Can I omit the functions.php and embed directly into my php page which is for admins only? The reason I ask is because I plan I using this data and manipulating it with additional commands (arithmetic calculations against the quantity integers). and then append it to a mySQL table – Nicholas Ravanelli Jun 10 '18 at 21:45
  • it depends. How are you loading that php file? Why a php file? If you want to restrict content on WP pages use a user control plugin like members. Create a page, set access to admin users only, and place my shortcode on that page and you're done. For extra protection, do current_user role checking inside my function. Please set my answer as accepted if you like it, thanks :-)! – Bjorn Jun 10 '18 at 21:55
  • For the total amount of products ordered per person, I want to be able to multiply it by a coefficient which I place in a textbox above the generated table, then a column will autogenerate the new variable (product of amount ordered * coeffiecient). My logic is so I do not need to make multiple pages for each product.. and i also like to learn more coding as I go, hence why I veere away from shortcodes whenever possible... – Nicholas Ravanelli Jun 10 '18 at 22:04
  • I would still use the shortcode, and with wp_ajax i would control the <table> on-the-fly. Ajax maybe a bit much now, keep things simple in the beginning. Also, i recommend you first learn how to debug code, google 'wp_debug'. It's the first thing developers do when code isn't working or does something unexpected. Good luck! – Bjorn Jun 10 '18 at 22:12
  • Cheers. I will definitely be trying to solve that riddle! – Nicholas Ravanelli Jun 10 '18 at 22:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.