CODEWITHSUNDEEP
mysqlsqlwordpresswoocommerceorders
Oscar
Oscar

Reputation: 55

Import orders from excel file in woocommerce

Create woocommerce order by Uploading Excel file

We have some orders from our customer that they are ordered by directly calling to us . So we have save their details in excel file with every details (customer name address etc to product name , product id , variation id , discount ,coupon details etc ).

We know how to upload data to database through excel by php code .

for a sample example 

<form name="import" method="post" enctype="multipart/form-data">
    <input type="file" name="file" /><br />
    <input type="submit" name="submit" value="Submit" />
 </form>

if(isset($_POST["submit"]))
{
$file = $_FILES['file']['tmp_name'];
$handle = fopen($file, "r");
$c = 0;

while(($filesop = fgetcsv($handle, 1000, ",")) !== false)
{
    $name = $filesop[0];
    $email = $filesop[1];

    $sql = mysql_query("INSERT INTO order (name, email) VALUES ('$name','$email')");
}

    if($sql){
        echo "Correctly uploaded";
    }else{
        echo "it's not working";
    }

}

And we know how to fetch each and every details of product .

But please help to integrate with woocommerce . Since we noticed that each order is saved in wp_posts , with post_type='shop_order', and order status as a taxonomy named shop_order_status , customer details of order is save as post_meta field ets and we know how to use each class in woocommerce for retrieving data for example WC_Order,WC_Order_Item_Meta.

Please help to complete this . If any one can explain the basic table structure of woocommerce for inserting query through sql, it is also helpful.

Thank you .

Note : We need to create sql query . We don't need to create customer and customer meta table entries , because in this order the customer who place this order is our default company customer service account . So no need to think about customer and customer meta table . The real customer information is added into order meta fields like _billing_phone,_billing_email, _shipping_address_1 etc

Upvotes: 3

Views: 1409

Answers (2)

LoicTheAztec
LoicTheAztec

Reputation: 253869

Orders are a custom post and are located first in wp_posts table with a 'post_type' like 'shop_order'.

For each post_id (in wp_posts table ID) you can retrieve all orders details in wp_postmeta table.
This orders have also a relations with wp_woocommerce_order_itemsand wp_woocommerce_order_itemmeta where they store additional details related to bought products, quantity, amounts, VAT...

This orders are related also to wp_users (as 'customer') and wp_usermeta tables. You can retrieve the customer ID in wp_postmeta table with meta_key as _customer_user.

Making sql query is going to be complicated, because you need first to create the user (wp_users and wp_usermeta tables).
Then a complex query to populate at the same time 4 tables related to orders.

--(update)-- RETRIEVING ORDERS DATA

Table wp_posts:

SELECT * FROM 'wp_posts' WHERE 'post_type' LIKE 'shop_order'

Table wp_postmeta:

SELECT * FROM 'wp_postmeta' WHERE 'post_id' = xxxx

(xxxx is the id of an order that you can find in wp_posts table)

Table wp_woocommerce_order_items:

SELECT * FROM 'wp_woocommerce_order_items' WHERE 'order_id' = xxxx

(xxxx is the id of an order that you can find in wp_posts table)

Table wp_woocommerce_order_itemmeta:

SELECT * FROM 'wp_woocommerce_order_itemmeta' WHERE 'order_item_id' = yyyy

(yyyy is the order_item_id of an order_id that you can find in wp_woocommerce_order_items)

Upvotes: 3

vijay nathji
vijay nathji

Reputation: 1638

I am not sure but this might be helful to you by doing some customizations in plugin.

https://wordpress.org/plugins/woocommerce-csvimport/

Upvotes: 0

Related Questions