Store order total and customer email in a custom table in MySQL database

Question

I am trying to pull the data from a completed checkout in the functions.php file of woocommerce. After checkout, I am trying to store a custom generated code, the email that generated the order, and the total amount of the order. I am unable to pull any data from the order, and have been unsuccesful in finding any documentation relating to it.

The data must be pulled after checkout is completed successfully.

The table has columns voucher code(the unique generated code per transaction), user email, and total price of order. I cannot find a way to grab the user email and order total after checkout. The query must insert the voucher ID which is generated within the function, the email which should be grabbed from the order details, redeemed which is set as 0, and the value which should also be grabbed from the order details.

On Woocommerce checkout page I have an internal server error and PHP Fatal error:
Call to a member function get_data() on boolean.

add_action( 'woocommerce_email_before_order_table', 'generate_code');
function generate_code(){
    $order = wc_get_order( $order_id );    
    $order_data = $order->get_data(); // The Order data
    $order_billing_email = $order_data['billing']['email'];
    $order_total_tax = $order_data['total_tax'];

    $connection = DBLogin();

    $query = "INSERT INTO test_vouchers (voucher_id, email, redeemed, value) VALUES ('$string','$order_billing_email','0','$order_total_tax')";
}

What I am doing wrong? How can I solve this error?
And how can I store order total and customer email in a custom table in MySQL database?

On the woocommerce checkout page I keep getting an internal server error, and PHP Fatal error: Call to a member function get_data() on boolean — cc38300
Clarify by editing your post, not via comments. Please read & act on minimal reproducible example. — philipxy

LoicTheAztec LoicTheAztec · Accepted Answer · 2018-07-07T00:53:11

Your question is not really clear or enough detailed. We don't know if you are trying to add this data to a custom table in your Wordpress database or in another database.

If you are trying to insert data in a custom table in your wordpress database, you should use WPDB Class (as I use in my answer), if not you should use the last code snippet.

The $order_id is not defined and your code throw an error. Also the variable $string is not defined and if it's an indexed column, it don't need to appear (like in the first code snippet below).

It seems that you are targeting "completed" orders. Using woocommerce_email_before_order_table hook will make your code to be triggered multiple times in each email notification. Also this hook is really not convenient to trigger any database changes.

The following code will be triggered when order status is "completed". So there is 2 cases:

1) Acting in the Wordpress database for a custom table:

add_action( 'woocommerce_order_status_completed', 'generate_code_on_order_complete', 20, 2 );
function generate_code_on_order_complete( $order_id, $order ){
    global $wpdb;

    $billing_email = $order->get_billing_email(); // Billing email
    $total_tax     = $order->get_total_tax();     // Billing email

    $wpdb->query("
        INSERT INTO test_vouchers (email, redeemed, value)
        VALUES ('$billing_email','0','$total_tax');
    ");
}

Code goes in function.php file of the active child theme (or active theme). tested and work.

2) Inserting data to another database than the Wordpress one (untested):

You should complete the following code to make the dabase connexion to your external database and to insert the data… Remember that $string is not defined.

add_action( 'woocommerce_order_status_completed', 'generate_code_on_order_complete', 20, 2 );
function generate_code_on_order_complete( $order_id, $order ){

    $billing_email = $order->get_billing_email(); // Billing email
    $total_tax     = $order->get_total_tax();     // Billing email

    ## To be completed by you (below) ##

    $connection = DBLogin();

    $query = "INSERT INTO test_vouchers (voucher_id, email, redeemed, value) VALUES ('$string','$billing_email','0','$total_tax')";
}

Code goes in function.php file of the active child theme (or active theme).

Store order total and customer email in a custom table in MySQL database

1 Answers