1

I am inserting a quantity of products, categories and attributes to a wordpress database.

My method is to copy a selection of source data tables from my off-line database (not in wordpress format) into the wordpress database, and run a local MySQL stored procedure that:

  1. Removes data I previously inserted
  2. Formats and distributes the source data to create new ...
  3. product records
  4. category records
  5. attribute records

My products and categories appear to be uploading and displaying as expected but I'm having the devil of a job getting my attributes to behave as I would like them to.

With no attributes in the system, I run my stored procedure that processes the following data (I've summarized it for the purpose of display here):

PRODUCTS
   TID  model                             
------  ----------------------------------
     2  BREAD MP MED SLICE WHITE 800g     
     3  BREAD MP THICK SLICED WHITE 800g  
     4  BREAD MP BROWN SLICED 800g        
     5  BREAD NIMBLE WHITE 400g           
   245  TREX 500g
ATTRIBUTES
   TID  Attr_Name     Order  
------  -----------  --------
     1  FAIR TRADE          0
     2  GLUTE FREE          0
     3  MULTI PACK          0
ATTRIBUTE VALUES: (SL_ID = source data product TID )
   TID   SL_ID  Attr_ID  Attr_Value  
------  ------  -------  ------------
     5       2        1  NO          
     6       2        2  NO          
     7       2        3  NO          
     2       3        1  YES         
     3       3        2  YES         
     4       3        3  YES         
    11       4        3  MAYBE       
    10       4        2  MAYBE       
     9       4        1  MAYBE       
    12       5        1  YES         
    13       5        2  NO          
    14       5        3  MAYBE       
    16     245        1  YES         
    17     245        2  NO        

Each one of the following rows are passed into a function (detailed below: f_create_attributes) that generates what I believe to be the relevant data rows in the relevant wp tables:

ATTRIBUTE VALUES
INattr_id  INattr_name  INslug   INattr_value  
---------  -----------  -------  --------------
        1  FAIR TRADE   1-yes    YES           
        1  FAIR TRADE   1-no     NO            
        1  FAIR TRADE   1-maybe  MAYBE         
        2  GLUTEN FREE  2-yes    YES           
        2  GLUTEN FREE  2-no     NO            
        2  GLUTEN FREE  2-maybe  MAYBE         
        3  MULTI PACK   3-yes    YES           
        3  MULTI PACK   3-no     NO            
        3  MULTI PACK   3-maybe  MAYBE         

FUNCTION: f_create_attributes

DELIMITER $$

USE `someth22_martins`$$

DROP FUNCTION IF EXISTS `f_create_attributes`$$

CREATE DEFINER=`someth22_admin`@`%` FUNCTION `f_create_attributes`(INattr_id BIGINT(20), 
                            INattr_name VARCHAR(200), 
                            INslug VARCHAR(200),
                            INattr_value VARCHAR(200)) RETURNS BIGINT(20)
    DETERMINISTIC
BEGIN
DECLARE OUT_ID BIGINT(20) DEFAULT 0;
/*
ATRIBUTES
-----------------
Set some local variables
*/
SET @attr_id = INattr_id;
SET @attr_name = INattr_name;
SET @attr_value = INattr_value;
SET @slug= INslug;
SET @metakey_attribute = "x_attribute_id_map";
SET @term_id = 0;
SET @attr_name_slug = LOWER(REPLACE(@attr_name," ","-"));
/*
This is where the attribute starts life
`wp_woocommerce_attribute_taxonomies`
*/
INSERT INTO `wp_woocommerce_attribute_taxonomies` (`attribute_name`,`attribute_label`,`attribute_type`,`attribute_orderby`,`attribute_public`)
SELECT A.* FROM
(SELECT @attr_name_slug AS attribute_name, @attr_name, "text", "menu_order",0) A LEFT JOIN `wp_woocommerce_attribute_taxonomies` B ON A.attribute_name = B.attribute_name
WHERE ISNULL(B.`attribute_id`);

/*
TERMS
---------------------
Attribute values
Linked to the relevant attribute in TERM TAXONOMY
*/
INSERT INTO wp_terms (`name`, `slug`, `term_group` )
VALUES (@attr_value, @slug, 0);
/*
Collect the term ID
*/
SELECT LAST_INSERT_ID() INTO @term_id;
/*
Create TermMeta row
*/
INSERT INTO `wp_termmeta` (`term_id`,`meta_key`,`meta_value`)
VALUES (@term_id, @metakey_attribute, @attr_value);
/*
Create term_taxonomy record
*/
INSERT INTO `wp_term_taxonomy` (`term_id`,`taxonomy`)
VALUES (@term_id, 
    LOWER(
    CONCAT("pa_",@attr_name_slug
    ))
    );
SET @term_taxonomy_id = 0;
SELECT LAST_INSERT_ID() INTO @term_taxonomy_id; 

/*
Create WOOCOMMERCE termmeta records
*/  
INSERT INTO `wp_woocommerce_termmeta` (`woocommerce_term_id`,`meta_key`,`meta_value`)
VALUES (@term_id, CONCAT("order_pa_",@attr_name_slug),0);


/*
Create term_taxonomymeta row
*/
INSERT INTO `wp_taxonomymeta` (`taxonomy_id`,`meta_key`,`meta_value`)
VALUES (@term_taxonomy_id, @metakey_attribute, @attr_id);
/*
Pass back the TERM_TAXONOMY_ID
*/
SELECT @term_taxonomy_id INTO OUT_ID;
RETURN OUT_ID;
    END$$

DELIMITER ;

After processing new attributes through the function above I can view new attribute records in WP using the following SQL ...

SELECT 
A.*, B.*, C.*
#P.`ID` AS `wp_id`, P.`post_title`, Taxonomy AS `Attribute`, `name` AS `AttrValue`
FROM wp_terms A INNER JOIN `wp_term_taxonomy` B ON A.`term_id` = B.`term_id`
INNER JOIN `wp_term_relationships` C ON B.`term_taxonomy_id` = C.`term_taxonomy_id`
INNER JOIN wp_posts P ON C.`object_id` = P.`ID`
WHERE B.`taxonomy` REGEXP "^pa_";

... and I get this result

term_id  name    slug     term_group  term_taxonomy_id  term_id  taxonomy        description  parent   count  object_id  term_taxonomy_id  term_order  
-------  ------  -------  ----------  ----------------  -------  --------------  -----------  ------  ------  ---------  ----------------  ------------
    558  NO      1-no              0               396      558  pa_fair-trade                     0       0       6916               396             0
    561  NO      2-no              0               399      561  pa_gluten-free                    0       0       6916               399             0
    564  NO      3-no              0               402      564  pa_multi-pack                     0       0       6916               402             0
    557  YES     1-yes             0               395      557  pa_fair-trade                     0       0       6917               395             0
    560  YES     2-yes             0               398      560  pa_gluten-free                    0       0       6917               398             0
    563  YES     3-yes             0               401      563  pa_multi-pack                     0       0       6917               401             0
    559  MAYBE   1-maybe           0               397      559  pa_fair-trade                     0       0       6918               397             0
    562  MAYBE   2-maybe           0               400      562  pa_gluten-free                    0       0       6918               400             0
    565  MAYBE   3-maybe           0               403      565  pa_multi-pack                     0       0       6918               403             0
    557  YES     1-yes             0               395      557  pa_fair-trade                     0       0       6919               395             0
    561  NO      2-no              0               399      561  pa_gluten-free                    0       0       6919               399             0
    565  MAYBE   3-maybe           0               403      565  pa_multi-pack                     0       0       6919               403             0
    557  YES     1-yes             0               395      557  pa_fair-trade                     0       0       6920               395             0
    561  NO      2-no              0               399      561  pa_gluten-free                    0       0       6920               399             0

This is an example of the Serialized Array I generate for the relevant wp_postmeta._product_attributes row

a:3:
{
s:13:"pa_glute-free";a:6:
 {s:4:"name";s:13:"pa_glute-free";
s:5:"value";s:0:"";
s:8:"position";s:1:"0";
s:10:"is_visible";i:1;
s:12:"is_variation";i:0;
s:11:"is_taxonomy";i:1;}

s:13:"pa_multi-pack";a:6:
 {s:4:"name";s:13:"pa_multi-pack";
s:5:"value";s:0:"";
s:8:"position";s:1:"0";
s:10:"is_visible";i:1;
s:12:"is_variation";i:0;
s:11:"is_taxonomy";i:1;}

s:13:"pa_fair-trade";a:6:
 {s:4:"name";s:13:"pa_fair-trade";
s:5:"value";s:0:"";
s:8:"position";s:1:"0";
s:10:"is_visible";i:1;
s:12:"is_variation";i:0;
s:11:"is_taxonomy";i:1;}
}

In the WP admin area: PRODUCTS -> ATTRIBUTES I'm told

Name    Slug    Type    Order by    Terms
No attributes currently exist.

But, if I add a new attribute via the admin area, my inserted attributes suddenly appear along with the one that I've just added manually.

But ...they display no TERMS.

However, if I now delete the attribute I just manually created, when the screen refreshes the TERMS linked to my inserted attributes miraculously appear.

Furthermore, on the web site, attributes don't show up against my products until I perform the manual ADD / DELETE.

Presumably I could call the add_new_attribute as described here.. github.com/woothemes/woocommerce .. but I would like to perform the entire data injection process via my MySQL calls.

So, I guess I'm asking if anyone knows what the add_new_attribute call does ...

Or what the call under the attribute DELETE link does ...

a class="delete" href="edit.php?post_type=product&page=product_attributes&delete=84&_wpnonce=e270352083">Delete

... with my injected data to make it work.

Any help appreciated.

2

I think I have answer to my own question. After loading data into wp_woocommerce_attribute_taxonomies as part of the function detailed above, it is necessary to create serailised array of attributes and their meta data, and post it into wp_options.As far as I can tell wp_options contains a range of transient data used by wordpress as a way to improve performance.

The following query seems to do the trick.

/*
ATTRIBUTES
---------------
Post an entry into wp_options
option_name = "_transient_wc_attribute_taxonomies"
This record is used to populate the ADMIN AREA -> Products -> Attributes interface
.. and appears to be used to post attribute values to screen in the web -> products list pages
*/
SET SESSION group_concat_max_len=10000;
SET @RowNo = -1;

INSERT INTO `wp_options` (`option_name`,`option_value`, `autoload`)
SELECT * FROM
(
SELECT
"_transient_wc_attribute_taxonomies" AS `New_Option_Name`,
CONCAT("a:",COUNT(*),":{",
GROUP_CONCAT(option_value SEPARATOR ""),
"}") AS New_Option_Value, "yes" AS `New_Autoload`
FROM
(
SELECT
CONCAT(
"i:",(@RowNo := @RowNo + 1),";O:8:","""","stdclass","""",":6:"
,"{S:12:","""","attribute_id","""",";s:",LENGTH(attribute_id),":","""",attribute_id,"""",";"
,"S:14:","""","attribute_name","""",";s:",LENGTH(attribute_name),":","""",attribute_name,"""",";"
,"S:15:","""","attribute_label","""",";s:",LENGTH(attribute_label),":","""",attribute_label,"""",";"
,"S:14:","""","attribute_type","""",";s:",LENGTH(attribute_type),":","""",attribute_type,"""",";"
,"S:17:","""","attribute_orderby","""",";s:",LENGTH(attribute_orderby),":","""",attribute_orderby,"""",";"
,"S:16:","""","attribute_public","""",";s:",LENGTH(attribute_public),":","""",attribute_public,"""",";}"
) AS option_value
FROM `wp_woocommerce_attribute_taxonomies` 
) Calculated_values
) Formatted_Values
ON DUPLICATE KEY UPDATE option_value = Formatted_Values.New_Option_Value;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.