Enhancing WooCommerce Order Management with PHP and SQL

WooCommerce provides a powerful API for managing orders, but sometimes you might need to interact directly with the database or use custom PHP code for specific requirements. This article will guide you through the process of creating an order from the cart, updating it, and retrieving order details using PHP code and SQL queries.

1. Creating an Order from the Cart

Creating an order from the cart involves transferring items from the cart to a new order and setting order details. Here’s a step-by-step guide with PHP code and the corresponding database interactions.

PHP Code to Create an Order

To create an order from the cart, you can use WooCommerce functions to handle the process programmatically. Here’s a sample PHP function:

// Make sure WooCommerce is loaded
if ( ! class_exists( 'WooCommerce' ) ) {
    return;
}

// Function to create an order from the cart
function create_order_from_cart() {
    $cart = WC()->cart;

    // Ensure the cart is not empty
    if ( $cart->is_empty() ) {
        return new WP_Error( 'empty_cart', 'The cart is empty.' );
    }

    // Create a new order
    $order = wc_create_order();

    // Add items from the cart to the order
    foreach ( $cart->get_cart() as $cart_item_key => $cart_item ) {
        $product_id = $cart_item['product_id'];
        $quantity = $cart_item['quantity'];
        $order->add_product( wc_get_product( $product_id ), $quantity );
    }

    // Set the billing and shipping address
    $order->set_address( $cart->get_customer(), 'billing' );
    $order->set_address( $cart->get_customer(), 'shipping' );

    // Calculate totals
    $order->calculate_totals();

    // Add order notes
    $order->add_order_note( 'Order created programmatically.' );

    // Set order status to pending
    $order->update_status( 'pending', 'Order created programmatically.' );

    // Empty the cart
    WC()->cart->empty_cart();

    return $order;
}

// Call the function to create the order
create_order_from_cart();

Database Interaction for the above PHP code

1. Insert Order into wp_posts WooCommerce stores orders as custom posts in the wp_posts table. Each order is inserted with a post_type of shop_order.

• SQL Query Example:
  INSERT INTO `wp_posts` ( `post_author`, `post_date`, `post_date_gmt`, `post_content`, `post_title`, `post_excerpt`, `post_status`, `comment_status`, `ping_status`, `post_password`, `post_name`, `to_ping`, `pinged`, `post_modified`, `post_modified_gmt`, `post_content_filtered`, `post_parent`, `guid`, `menu_order`, `post_type`, `post_mime_type`, `comment_count` ) VALUES ( 1, NOW(), NOW(), '', 'Order - 12345', '', 'wc-pending', 'closed', 'closed', 'wc_order_abc123def456', 'order-12345', '', '', NOW(), NOW(), '', 0, '', 0, 'shop_order', '', 0 );

1. Insert Order Metadata into wp_postmeta Order metadata includes billing and shipping addresses, order total, etc.

• SQL Query Example:
  INSERT INTO `wp_postmeta` (`post_id`, `meta_key`, `meta_value`) VALUES (12345, '_billing_first_name', 'John'), (12345, '_billing_last_name', 'Doe'), (12345, '_billing_address_1', '123 Main St'), (12345, '_billing_city', 'San Francisco'), (12345, '_billing_postcode', '94103'), (12345, '_order_total', '150.00'), (12345, '_order_currency', 'USD'), (12345, '_payment_method', 'paypal');

1. Insert Order Items into wp_woocommerce_order_items Each item in the order is added to this table.

• SQL Query Example:
  INSERT INTO `wp_woocommerce_order_items` (`order_item_name`, `order_item_type`, `order_id`) VALUES ('Product Name 1', 'line_item', 12345), ('Product Name 2', 'line_item', 12345);

1. Insert Order Item Metadata into wp_woocommerce_order_itemmeta Details about each item, such as quantity and price, are stored here.

• SQL Query Example:
  INSERT INTO `wp_woocommerce_order_itemmeta` (`order_item_id`, `meta_key`, `meta_value`) VALUES (456, '_product_id', '789'), (456, '_qty', '1'), (456, '_line_total', '100.00'), (457, '_product_id', '790'), (457, '_qty', '1'), (457, '_line_total', '50.00');

2. Updating an Order

Updating an order involves modifying details like quantity, adding new products, updating addresses, and changing the order status. Here’s how you can achieve that programmatically:

PHP Code to Update an Order

function update_order($order_id) {
    $order = wc_get_order($order_id);

    if (!$order) {
        return new WP_Error('order_not_found', 'Order not found.');
    }

    // Update item quantity
    foreach ($order->get_items() as $item_id => $item) {
        if ($item->get_product_id() == 789) {
            $order->update_item($item_id, array('qty' => 2)); // Change quantity to 2
        }
    }

    // Add a new product to the order
    $product_id = 791; // New product ID
    $order->add_product(wc_get_product($product_id), 1); // Add 1 unit of the new product

    // Update billing address
    $order->set_billing_first_name('Jane');
    $order->set_billing_last_name('Smith');
    $order->set_billing_address_1('456 Elm St');
    $order->set_billing_city('Los Angeles');
    $order->set_billing_postcode('90001');

    // Update order status
    $order->update_status('completed', 'Order updated programmatically.');

    // Recalculate totals
    $order->calculate_totals();

    // Save changes
    $order->save();
}

// Call the function to update the order
update_order(12345);

Database Interaction for the above code

1. Update wp_posts Table Modify the post_status to update the order status.

• SQL Query Example:
  UPDATE `wp_posts` SET `post_status` = 'wc-completed' WHERE `ID` = 12345;

1. Update wp_postmeta Table Modify metadata such as billing address.

• SQL Query Example: UPDATE `wp_postmeta` SET `meta_value` = 'Jane' WHERE `post_id` = 12345 AND `meta_key` = '_billing_first_name'; UPDATE `wp_postmeta` SET `meta_value` = '456 Elm St' WHERE `post_id` = 12345 AND `meta_key` = '_billing_address_1';

1. Update Order Items in wp_woocommerce_order_items To update quantities or add new items, modify the rows accordingly.

• SQL Query Example (Updating Quantity):
  UPDATE `wp_woocommerce_order_itemmeta` SET `meta_value` = '2' WHERE `order_item_id` = 456 AND `meta_key` = '_qty';
• SQL Query Example (Adding New Item):
  INSERT INTO `wp_woocommerce_order_items` (`order_item_name`, `order_item_type`, `order_id`) VALUES ('New Product', 'line_item', 12345); INSERT INTO `wp_woocommerce_order_itemmeta` (`order_item_id`, `meta_key`, `meta_value`) VALUES (458, '_product_id', '791'), (458, '_qty', '1'), (458, '_line_total', '20.00');

3. Getting Order Details by Order ID

To retrieve detailed information about an order, including metadata and items, you can use WooCommerce functions or SQL queries.

PHP Code to Get Order Details

function get_order_details($order_id) {
    $order = wc_get_order($order_id);

    if (!$order) {
        return new WP_Error('order_not_found', 'Order not found.');
    }

    // Retrieve order details
    $order_details = array(
        'order_id' => $order->get_id(),
        'order_status' => $order->get_status(),
        'order_total' => $order->get_total(),


 'billing_first_name' => $order->get_billing_first_name(),
        'billing_last_name' => $order->get_billing_last_name(),
        'billing_address_1' => $order->get_billing_address_1(),
        'billing_city' => $order->get_billing_city(),
        'billing_postcode' => $order->get_billing_postcode(),
        'order_items' => array()
    );

    foreach ($order->get_items() as $item_id => $item) {
        $order_details['order_items'][] = array(
            'product_id' => $item->get_product_id(),
            'product_name' => $item->get_name(),
            'quantity' => $item->get_quantity(),
            'line_total' => wc_get_order_item_meta($item_id, '_line_total', true)
        );
    }

    return $order_details;
}

// Call the function to get the order details
print_r(get_order_details(12345));

Database Interaction for the above PHP code

1. Retrieve Order from wp_posts Fetch order details from the wp_posts table using the order ID.
   SQL Query Example:SELECT * FROM `wp_posts` WHERE `ID` = 12345;

1. Retrieve Order Metadata from wp_postmeta Fetch metadata associated with the order.
   SQL Query Example: SELECT * FROM `wp_postmeta` WHERE `post_id` = 12345;

1. Retrieve Order Items from wp_woocommerce_order_items Fetch order items and their details.
   SQL Query Example: SELECT * FROM `wp_woocommerce_order_items` WHERE `order_id` = 12345;

1. Retrieve Order Item Metadata from wp_woocommerce_order_itemmeta Fetch metadata for each order item.
   SQL Query Example: SELECT * FROM `wp_woocommerce_order_itemmeta` WHERE `order_item_id` IN ( SELECT `order_item_id` FROM `wp_woocommerce_order_items` WHERE `order_id` = 12345 );

Pages: 1 2 3 4