Adding Data to Customer Table At Checkout

[lolawson]

This should be a not-too-complicated task, but I need a nudge in the right direction.

 

When customer purchases product x with attribute y, upon checkout I'd like to have a field in the customer table updated with the latest purchase-related info. Currently we're doing it manually each time an order comes in, but I'd like to automate the process.

 

I'm clear on needing to join the Customers to Orders table on customer_id, to the Orders_Products table on orders_id, to the Orders_Products_Attribute tables on products_id and I need to update the customers, table setting customer.variable = orders_products_attribute.products_options_value where products_id='x', but beyond that....

 

Would someone guide me on syntax to make it all work? Thanks in advance.

[♥geoffreywalton]

http://www.w3schools.com/sql/sql_update.asp

 

Should get you started

[lolawson]

Thanks for the reply, Geoffrey. I've got the general SQL syntax down, but making it work within the framework of checkout_process.php--and in php in general--is a stretch for me.

 

I think I'm missing a step or twelve, but here's what I've written so far, and inserted just before the customer email is generated in checkout_process.php:

 

//-------insert membership data into Customers Table -----

$membership_query = "SELECT c. customers_id, c.customers_membership_type, 
o.date_purchased, opa.products_options_value
                           FROM " . TABLE_CUSTOMERS . " c
                           LEFT JOIN " . TABLE_ORDERS . " o
                            ON c.customers_id=o.customers_id
                           LEFT JOIN " . TABLE_ORDERS_PRODUCTS . " op
                            ON o.orders_id=op.orders_id
                           LEFT JOIN " . TABLE_ORDERS_PRODUCTS_ATTRIBUTES . " opa
                            ON op.products_id=opa.orders_products_id
                           WHERE c.customers_id = '" . $customer_id . "'";


tep_db_query("update " . TABLE_CUSTOMERS . " set c.customers_dob
= o.date_purchased, 
c.customers_membership_type = opa.products_options_value where op.products_id='2' and 
c.customers_id = '" . $customer_id . "'");

//------------//

 

but obviously, this alone doesn't work. Any hints?