Woocommerce Question posted in
The official Woocommerce documentation can be found here.

SQL query for a Woocommerce orders based on Customer ID

How can I perform a SQL query for the following?

  $args = array(
    'customer_id' => $customer_id,
    'status' => 'completed',
    'limit' => 1,
  );
  $orderslast = wc_get_orders( $args );

Here’s an error I get:

2019/08/14 09:08:03 [error] 11#11: *19 FastCGI sent in stderr: "' at line 7 for query 
          SELECT p.ID FROM wp_posts p
          INNER JOIN wp_postmeta pm ON p.ID = pm.post_id
          WHERE p.post_type = 'shop_order'
          AND p.post_status = 'wc-completed'
          AND pm.meta_key = '_customer_user'
          AND pm.meta_value = '16495'
          AND LIMIT 1
       made by require('wp-blog-header.php'), require_once('wp-includes/template-loader.php'), include('/var/www/app/app/sib-cancelled.php')
PHP message: PHP Warning:  fputcsv() expects at least 2 parameters, 1 given in /var/www/app/app/sib-cancelled.php on line 167" while reading response header from upstream, client: 172.18.0.1, server: , request: "GET /sib-cancelled/ HTTP/1.1", upstream: "fastcgi://127.0.0.1:9000", host: "localhost:4646"
Tags:

2

Answers


  1. 0

    Try this: SELECT customer_id FROM yourTableName WHERE status = 'completed' AND limit = 1

    Login or Signup to reply.
  2. 0

    Updated

    You can use the following (WPDB class SQL query) from a customer ID dynamic variable:

    global $wpdb;

$order_ids = $wpdb->get_col( "
    SELECT p.ID FROM {$wpdb->prefix}posts p
    INNER JOIN {$wpdb->prefix}postmeta pm ON p.ID = pm.post_id
    WHERE p.post_type = 'shop_order'
    AND p.post_status = 'wc-completed'
    AND pm.meta_key = '_customer_user'
    AND pm.meta_value = '$customer_id'
    ORDER BY p.ID DESC LIMIT 1
");

    You will get an array with the last order ID, from a customer ID.

    You can also use instead:

    global $wpdb;

$order_ids = $wpdb->get_col( "
    SELECT MAX(p.ID) FROM {$wpdb->prefix}posts p
    INNER JOIN {$wpdb->prefix}postmeta pm ON p.ID = pm.post_id
    WHERE p.post_type = 'shop_order'
    AND p.post_status = 'wc-completed'
    AND pm.meta_key = '_customer_user'
    AND pm.meta_value = '$customer_id'
");

    You can replace get_col() with get_var() to get a value instead of an array with a value.

    If you want to get the last order ID from an email (the customer billing email), use:

    global $wpdb;

$order_ids = $wpdb->get_col( "
    SELECT MAX(p.ID) FROM {$wpdb->prefix}posts p
    INNER JOIN {$wpdb->prefix}postmeta pm ON p.ID = pm.post_id
    WHERE p.post_type = 'shop_order'
    AND p.post_status = 'wc-completed'
    AND pm.meta_key = '_billing_email'
    AND pm.meta_value = '$billing_email'
");
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search