以编程方式将产品属性添加到WP/Woo商务

时间：2016-03-31 作者：Ben Stevens

我正在向wordpress数据库中插入大量产品、类别和属性。

我的方法是将所选的源数据表从脱机数据库（不是wordpress格式）复制到wordpress数据库中，并运行一个本地MySQL存储过程，该过程：

删除我以前插入的数据格式化并分发源数据以创建新的

  • 产品记录
  • 类别记录
  • 属性记录
      • 我的产品和类别似乎正在按预期上传和显示，但我的任务是让属性按我所希望的那样运行。

      在系统中没有属性的情况下，我运行存储过程来处理以下数据（为了在此处显示，我对其进行了总结）：

      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
      以下每一行都被传递到一个函数中（详细说明如下：f\\u create\\u attributes），该函数生成我认为是相关wp表中的相关数据行：

      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
      功能：f\\u create\\u 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 ;
      通过上述函数处理新属性后，我可以使用以下SQL在WP中查看新属性记录。。。

      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_";
      ... 我得到了这个结果

      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
      这是我为相关wp\\U Posteta生成的序列化数组的一个示例_product\\u属性行

      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;}
}
      在WP管理区域：产品->属性我被告知

      Name    Slug    Type    Order by    Terms
No attributes currently exist.
      但是，如果我通过管理区域添加一个新属性，我插入的属性会突然与我刚才手动添加的属性一起出现。

      但是它们不显示术语。

      然而，如果我现在删除我刚刚手动创建的属性，当屏幕刷新时，链接到我插入的属性的术语奇迹般地出现。

      此外，在网站上，在我执行手动添加/删除之前，属性不会显示在我的产品上。

      大概我可以像这里描述的那样调用add\\u new\\u属性。。github.com/woothemes/woocommerce.. 但我希望通过MySQL调用执行整个数据注入过程。

      所以，我想我是在问是否有人知道add\\u new\\u属性调用的作用。。。

      或者属性DELETE链接下的调用所做的。。。

      a class="delete" href="edit.php?post_type=product&page=product_attributes&delete=84&_wpnonce=e270352083">Delete
      ... 使用我的注入数据使其工作。

      感谢您的帮助。

    1 个回复
    SO网友:Ben Stevens

    我想我对自己的问题有了答案。将数据加载到wp_woocommerce_attribute_taxonomies 作为上述函数的一部分，有必要创建属性及其元数据的序列化数组，并将其发布到wp_options.据我所知wp_options 包含wordpress用来提高性能的一系列瞬态数据。

    下面的查询似乎可以做到这一点。

    /*
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;

    标签： woocommerce-offtopic   mysql   customization   小码农  

    相关推荐

    删除所有具有编辑者角色的用户及其内容MySQL

    我有一个有数千名编辑的Wordpress网站。我不能从WP仪表板中删除它们，因为它太慢了。我需要一个查询来从PhpMyAdmin中删除所有编辑器用户及其帖子？谢谢