Select data from database and list it in Loop (WordPress)

Question

I did something like this when making custom reports for WooCommerce since the data was in tables not part of WordPress. My advice to you as well, I can’t tell all that you’re trying to accomplish here but research WP_Query a lot because most any WordPress query can be accomplished with it and ONLY if it cannot be done should you consider using a $wpdb custom query like what I’m presenting here.

//Need to call the $wpdb global
global $woocommerce, $wpdb;

// Write your own custom query using $wpdb->prefix
$sql = "
  #<!--All Campaign Products and Quantity-->
  SELECT orders.order_item_id, meta_product.meta_value as product, meta_qty.meta_value as qty, pm.meta_value AS donation
  FROM {$wpdb->prefix}woocommerce_order_items AS orders
  LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS meta_product ON (orders.order_item_id = meta_product.order_item_id AND meta_product.meta_key='_product_id')
  LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS meta_qty ON (orders.order_item_id = meta_qty.order_item_id  AND meta_qty.meta_key='_qty')
  LEFT JOIN {$wpdb->prefix}postmeta AS pm ON (meta_product.meta_value = pm.post_id AND pm.meta_key = 'donation_amount')

  WHERE orders.order_item_id IN (".$this->sql_items_from_campaign($cid).")";

// Results can be parsed as OBJECT, OBJECT_K, ARRAY_A, ARRAY_N
$result = $wpdb->get_results($sql, OBJECT);

// See your results printed out (optional)
print_r($result);

// Simple PHP loop
$donations = 0;
foreach ($result as $k => $v) {
  $donations = $donations + ($v->donation * $v->qty);
}

==== UPDATE BASED ON QUESTION CHANGES =====

Couple notes, you should be able to echo $sql and then copy and past the results into the edit sql section in phpmyadmin, this is great for debugging that you’re actually getting the correct results. Keep in mind also to use things like {wpdb->prefix} so that your code doesn’t break when a different table prefix is used.

Since you were able to print result in the $results array I assume you have the response you were expecting. From there you need to use a simple PHP loop to get the data you want. (this will differ based on if you selected OBJECT, OBJECT_K, ARRAY_A, ARRAY_N for the $results)

// For OBJECT
foreach ($result as $key => $value) {
  echo $value->vehicle_id;
  echo $value->manufacturer;
  echo $value->series
  echo $value->class;
}

Related Posts: