admin管理员组文章数量:1289875
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:
- Removes data I previously inserted
- Formats and distributes the source data to create new ...
- product records
- category records
- 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/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.
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:
- Removes data I previously inserted
- Formats and distributes the source data to create new ...
- product records
- category records
- 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/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.
Share Improve this question edited Jan 9, 2018 at 8:10 admcfajn 1,3262 gold badges13 silver badges30 bronze badges asked Mar 31, 2016 at 23:20 Ben StevensBen Stevens 411 silver badge4 bronze badges1 Answer
Reset to default 2I 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;
本文标签： woocommerce offtopicProgrammatically adding product attributes to WPWoo commerce
版权声明：本文标题：woocommerce offtopic - Programmatically adding product attributes to WPWoo commerce 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.betaflare.com/web/1741405358a2376913.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论