如何从一个SQL表复制数据并在另一个不同的表中创建新条目？

Question

I have a two pronged problem with the custom application of an existing WooCommerce plugin (the Product Add-Ons extension).

I have WooCommerce/WordPress running perfectly fine and am using the Product Add-Ons extension to capture registration information for the customer's child as part of the order process.

There are 56 potential inputs (not all mandatory, so some can remain null) that a customer fills out regarding their child. This part of my process works fine.

I also have a registrant database (of youth players) that is part of a custom built league management system. That database table has 57 columns in each row.

The first problem I'm having is getting all of the data from the first table copied over to the second table and I think I understand why, I just don't know how to solve it.

Both tables exist within the same database.

The first table is called woocommerce_order_itemmeta and it's structure is as follows:

meta_id, 
order_item_id, 
meta_key, 
meta_value

The first two columns do not need to be copied over as the information they contain ONLY relates to WooCommerce itself.

The following two columns work as follows; when I create an input field as a product add-on and a customer completes an order the meta_key field contains which input it is and the meta_value contains the information the customer entered.

For example:

**meta_key** = Lastname, **meta_value** = Smith,
**meta_key** = Firstname, **meta_value** = John,

Each order could have 56 of these associated with it.

Now, once an order is flagged as completed within WooCommerce, I need to it execute a transfer/copying of the data for that specific order into a second database with a completely different structure.

The second table is signUps and it's structure is a lot more straight forward:

num, 
teamRole, 
lName, 
fName, 
mName, 
guardianMother, 
guardianMphone, 
guardianFather, 
guardianFphone

etc... for a total of 57 rows. the num column is an auto-increment integer and can be skipped.

The problem I'm having is that the columns are mismatched 4 to 57 and, from what I can gather, it's also a bit more difficult because I have to select the **meta_value** WHERE the **meta_key** is *X* and do this for each entry, to then copy the information into a more straightforward table.

I suspect that if I had a clearer structure and could just grab the value of "Lastname,Firstname,Middlename,etc" without first having to identify it as a KEY, that it would be easy to then just insert it into "lName,fName,mname,etc".

So far, I can get the following query working in phpMyAdmin:

INSERT INTO `signUps`(lName) SELECT `meta_value` FROM `woocommerce_order_itemmeta` WHERE `meta_key` = 'Lastname';

The problem kicks in when I try to find ways to add a second value to the query.

What I basically need is a way to get all of the data collected to copy to the appropriate columns in the second table.

The second problem I'm having is that we can't get anything at all to fire when I use the woocommerce_order_status_completed action hook the way the documentation says I should.

add_action( 'woocommerce_order_status_completed', 'register_player' );

The only additional caveat is that I can't change the structure of either table - they are the way they are because so many other, unrelated functions, rely heavily on both of them. (It's actually sort of frustrating.) I'd love to just use the one table instead of duplicating the information, but then I'd be re-writing someone else's entire league management application and I'm lacking in both the time and the expertise to do so.

Any solutions would be greatly appreciated. Thanks guys.

Answer 1

To answer the first prong of your problem, there are two ways I have done this in MS SQL Server, which is probably same concept in MySql: one I call the "sub-select method" and the other I call the "multiple self-joining method".

The first of these is slower, but works across any inconsistencies in what kind of "soft fields" (meta keys and values in your situation) that might exist, while the other is faster but requires this consistency or else fails entirely.

Sub-select method:

INSERT INTO signUps (lName, fName, mName)
SELECT
    (meta_value FROM woocommerce_order_itemmeta WHERE meta_key = 'Lastname' AND order_item_id = 1234) as v1,
    (meta_value FROM woocommerce_order_itemmeta WHERE meta_key = 'Firstname' AND order_item_id = 1234) as v2,
    (meta_value FROM woocommerce_order_itemmeta WHERE meta_key = 'Middlename' AND order_item_id = 1234) as v3

Multiple self-joining method:

INSERT INTO signUps (lName, fName, mName)
SELECT 
    t0.meta_value as 'Lastname',
    t1.meta_value as 'Firstname',
    t2.meta_value as 'Middlename'
FROM woocommerce_order_itemmeta t0
INNER JOIN woocommerce_order_itemmeta t1 ON t0.order_item_id = t1.order_item_id
INNER JOIN woocommerce_order_itemmeta t2 ON t0.order_item_id = t2.order_item_id
WHERE t0.order_item_id = 1234
AND t0.meta_key = 'Lastname'
AND t1.meta_key = 'Firstname'
AND t2.meta_key = 'Middlename'

You will see I also need to be sure to pass the order_item_id or else we would find first, middle, and last names from all orders in your system being copied over each time (probably not what you want). The above is also MS syntax so you might need to adjust for MySql.

As for the second part of your problem, .. I'm afraid I don't have any experience with WooCommerce. Perhaps a second, more targeted question on just this aspect might yield better results!

Good luck to you!