Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I need to display the total revenue of all orders for my products depending on if the logged-in user is the author of the product or not.

I can't use the Quantity field as a multiplier either because of the Gravity Forms add-on for Woocommerce processes it in to a meta_key/value field, same goes for the product ID reference to the order. I'm using a $wpdb query to grab my order data, but I just can't seem to write it correctly or know how to get it, my SQL skills are not exactly tip-top.

So far I've come up with this:

SELECT * FROM wp_woocommerce_order_itemmeta AS order_meta
LEFT JOIN wp_woocommerce_order_items AS order_items
    ON order_meta.order_item_id = order_items.order_item_id
LEFT JOIN wp_term_relationships AS term_rels
    ON term_rels.object_id = order_items.order_id
WHERE term_taxonomy_id = 24 # 24 being the completed order status ID

This gives me the following:

MySQL Workbench output for my query

As you can see the meta values for the Product ID and the Qty: 16/30 etc. are in the same column, I almost need them to be in the same row if possible.

After I get the query right, I can then multiply the Qty (gotten with regex) by a custom field value, and the display this on the web site.

Anyone able to point me in the right direction? :)

share|improve this question

2 Answers 2

up vote 0 down vote accepted

Got it solved. Probably not the most efficient way but it works!

SELECT product_id, meta_value FROM
    (
        SELECT order_meta.order_item_id, order_meta.meta_value AS 'product_id'
        FROM wp_woocommerce_order_itemmeta AS order_meta
        LEFT JOIN wp_woocommerce_order_items AS order_items
            ON order_meta.order_item_id = order_items.order_item_id
        LEFT JOIN wp_term_relationships AS term_rels
            ON term_rels.object_id = order_items.order_id
        WHERE term_taxonomy_id = 24
        AND order_meta.meta_key = '_product_id'
    ) AS product_ID
    JOIN
    (
        SELECT order_meta2.meta_value, order_meta2.order_item_id
        FROM wp_woocommerce_order_itemmeta AS order_meta2
        LEFT JOIN wp_woocommerce_order_items AS order_items
            ON order_meta2.order_item_id = order_items.order_item_id
        LEFT JOIN wp_term_relationships AS term_rels
                ON term_rels.object_id = order_items.order_id
        WHERE term_taxonomy_id = 24
        AND (
            order_meta2.meta_key = 'Item Details'
            OR order_meta2.meta_key = 'Product Name'
        )
    ) AS product_qty
    ON product_qty.order_item_id = product_ID.order_item_id
    WHERE product_id = " . $post->ID
share|improve this answer

List all the customer with product

//now selects all data in the table wp_postmeta where the word billing is within the value $result = mysql_query("SELECT distinct post_id FROM wp_postmeta WHERE meta_key LIKE '%billing%' ");

while($row = mysql_fetch_array($result)) { //prints the results in a basic table for each row that was retrieved

//SELECT distinct b.order_item_name, a._billing_email, a., a., a. , a. FROM wp_postmeta a , wp_woocommerce_order_items b WHERE a.post_id=b.order_id and a.meta_key like '%billing%'

//now selects all data in the table wp_postmeta where the word billing is within the value

$result3 = mysql_query("SELECT * FROM  `wp_postmeta` WHERE post_id = ".$row['post_id']  );
while($row3 = mysql_fetch_array($result3)) 
{ 
    if($row3['meta_key']=='_billing_email')
        $email = $row3['meta_value'];
    if($row3['meta_key']=='_billing_last_name')
        $name = $row3['meta_value'];
    if($row3['meta_key']=='_billing_first_name')
        $firstname = $row3['meta_value'];
    if($row3['meta_key']=='_billing_city')
        $city = $row3['meta_value'];
    if($row3['meta_key']=='_billing_postcode')
        $cp = $row3['meta_value'];
    if($row3['meta_key']=='_customer_user')
        $user = $row3['meta_value'];    
    if($user){
    $result4 = mysql_query("SELECT * FROM  wp_users WHERE ID = ".$user  );
    while($row4 = mysql_fetch_array($result4)) 
    {   
        $user_nicename = $row4['user_nicename'];
        $user_email = $row4['user_email'];

    }   
    }

}

$result2 = mysql_query("SELECT * FROM  `wp_woocommerce_order_items` WHERE order_id = ".$row['post_id']  );
while($row2 = mysql_fetch_array($result2)) 
{ 
    $cours = $row2['order_item_name'];
    echo $row['post_id'].";".$user_nicename.";".$user_email.";".$cours .";".$email.";".$name.";".$firstname.";".$city.";".$cp."<br/>";
}

}

share|improve this answer
    
this is bad code, you should use worpress api to access the DB, the code will probably break when ordpress will change the PHP api it uses to access the DB. Probably the whole idea to bypass woocomerce api and directly access the table is bad as well, but at least that was the question :( –  Mark Kaplun Feb 26 at 14:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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