批量修改网站产品的 meta title
批量修改网站产品的 meta title
我有一个magento2 网站站点,在这个网站上有一个 mg1.yshuq.com 网站,这个网站上有一批产品。
在mg1.yshuq.com 站上,有一个产品属性 meta tile 的值为:产品名称–mg1.yshuq.com
我现在又创建另外一个站点mg2.yshuq.com,我将mg1.yshuq.com 网站上的产品追加到了mg2.yshuq.com 站上。但是我的产品属性 meta tile 的值仍为:产品名称–mg1.yshuq.com,我想把
meta title的值修改为 产品名称–mg2.yshuq.com 该怎么操作。
1.我们找到meta tile 产品属性存放表。
magento2 属性表 eav_attribute
select * from eav_attribute where attribute_code = 'meta_title'
我们找到属性为meta_title 的attribute_id为84,那么我们现在根据这个id的值,去查询属性的值。
我们找到存放属性的值的表 catalog_product_entity_varchar。我们根据 attribute_id = 84 查询
select * from catalog_product_entity_varchar where attribute_id = 84
根据上面sql语句我们查询结果如下:
从查询结果我们找到了
- value_id :主键自增
- attribute_id: meta tile的id
- store_id : 店铺id
- entity_id: 产品id
- value: meta title的值
2.获取所有网站 store_website 循环遍历网站
$table = $connection->getTableName('store_website');
$query = "Select * FROM " . $table . " where `name` != 'Admin'";
$logger->info("查询所有网站:".$query);
$websiteList = $connection->fetchAll($query);
$logger->info("所有网站list:".\GuzzleHttp\json_encode($websiteList));
if (!empty($websiteList) && count($websiteList) > 0) {
$webId = $websiteName = $query = '';
foreach ($websiteList as $v1 => $j1) {
$websiteName = $j1['name'];
$webId = $j1['website_id'];
}
}
mysql> select * from store_website;
+------------+-------------+-----------------+------------+------------------+------------+
| website_id | code | name | sort_order | default_group_id | is_default |
+------------+-------------+-----------------+------------+------------------+------------+
| 0 | admin | Admin | 0 | 0 | 0 |
| 1 | base | uikeyddealw | 0 | 1 | 1 |
| 2 | dev | dev | 1 | 3 | 0 |
| 3 | dev_keeysfang | dev_keeysfang.com | 1 | 2 | 0 |
+------------+-------------+-----------------+------------+------------------+------------+
4 rows in set (0.00 sec)
3. 根据website_id 循环遍历产品
//3.根据website_id 循环遍历产品 product_id
$table = $connection->getTableName('catalog_product_website');
$query = "Select * FROM " . $table . " where website_id = " . $webId;
$productList = $connection->fetchAll($query);
$logger->info("根据网站id查询所有产品:".$query);
$logger->info("网站id:".$webId."的全部产品".\GuzzleHttp\json_encode($productList));
if (!empty($productList) && count($productList) > 0) {
foreach ($productList as $v2 => $j2) {
$productId = $j2['product_id'];
$logger->info("product id:" . $productId);
}
}
mysql> select * from store_website;
+------------+-------------+-----------------+------------+------------------+------------+
| website_id | code | name | sort_order | default_group_id | is_default |
+------------+-------------+-----------------+------------+------------------+------------+
| 0 | admin | Admin | 0 | 0 | 0 |
| 1 | base | ukeydeal | 0 | 1 | 1 |
| 2 | dev | dev | 1 | 3 | 0 |
| 3 | dev_keysfan | dev_keeysfang.com | 1 | 2 | 0 |
+------------+-------------+-----------------+------------+------------------+------------+
4 rows in set (0.00 sec)
4.根据website_id 在store表查询出店铺id
//4.根据website_id 在store表查询出店铺id $table = $connection->getTableName('store'); $query = "Select * FROM " . $table . " where `website_id` =" . $webId; $storeList = $connection->fetchAll($query); $logger->info("根据website_id查询店铺列表" . $query); $logger->info("网站id: ".$webId."店铺列表".\GuzzleHttp\json_encode($storeList)); if (!empty($storeList) && count($storeList) > 0) { foreach ($storeList as $v3 => $j3) { $storeId = $j3['store_id']; } }
mysql> select * from store where website_id = 1;
+----------+---------------+------------+----------+--------------+------------+-----------+
| store_id | code | website_id | group_id | name | sort_order | is_active |
+----------+---------------+------------+----------+--------------+------------+-----------+
| 1 | ukd_en | 1 | 1 | English | 0 | 1 |
| 2 | ukd_tw | 1 | 1 | 繁体中文 | 0 | 1 |
| 5 | dev_ukeydal | 1 | 1 | 繁体中文 | 0 | 1 |
| 6 | developdealom | 1 | 1 | 繁体中文 | 0 | 1 |
+----------+---------------+------------+----------+--------------+------------+-----------+
4 rows in set (0.00 sec)
5. 获取 默认店铺admin store_id = 0 的value的值
$table = $connection->getTableName('catalog_product_entity_varchar');
$query = "Select * FROM " . $table . " where store_id = 0 and entity_id = " . $productId
. " and attribute_id = " . $attributeId;
$metaTitleValue0 = $connection->fetchAll($query);
$logger->info("获取默认值sql:".$query);
$logger->info("获取admin点meta title默认值:".\GuzzleHttp\json_encode($metaTitleValue0));
elect * from catalog_product_entity_varchar where store_id=0 and attribute_id=84 and entity_id=2;
+----------+--------------+----------+-----------+----------------------------------------------+
| value_id | attribute_id | store_id | entity_id | value |
+----------+--------------+----------+-----------+----------------------------------------------+
| 21 | 84 | 0 | 2 | Buy Anno 1800, Anno Uplay Key - ukeydeal.com |
+----------+--------------+----------+-----------+----------------------------------------------+
1 row in set (0.00 sec)
6. 根据店铺id,产品id ,属性id,查询 属性为meta_tile的值
$query = "Select * FROM " . $table . " where store_id = " . $storeId . " and entity_id = " . $productId
. " and attribute_id = " . $attributeId;
$metaTitleValue1 = $connection->fetchAll($query);
$logger->info("获取当前网站店铺产品的meta title sql:".$query);
$logger->info("获取当前网站店铺产品的meta title 的值:".\GuzzleHttp\json_encode($metaTitleValue1));
mysql> select * from catalog_product_entity_varchar where store_id=1 and attribute_id=84 and entity_id=2;
+----------+--------------+----------+-----------+-----------------------------------------+
| value_id | attribute_id | store_id | entity_id | value |
+----------+--------------+----------+-----------+-----------------------------------------+
| 9079 | 84 | 1 | 2 | Buy Anno 1800, Anno Uplay Key -ukeydeal |
+----------+--------------+----------+-----------+-----------------------------------------+
1 row in set (0.00 sec)
7.组合新值,获取store id = 0 后台默认值
//7.组合新值,获取store id = 0 后台默认值。
$mt = explode('-', $metaTitleValue0[0]['value']);
$newMetaTitleValue = $mt[0] . "-" . $websiteName;
8.meta title 不存在 就添加, 存在,就更新
if (empty($metaTitleValue1) && count($metaTitleValue1) == 0) {
$data = [
'attribute_id' => $attributeId,
'store_id' => $storeId,
'entity_id' => $productId,
'value' => $newMetaTitleValue
];
$logger->info("添加的meta title 的值".\GuzzleHttp\json_encode($data));
$connection->insert($table,$data);
}else{
$id = $metaTitleValue1[0]['value_id'];
$newMetaTitleValue = str_replace("'", ' ', $newMetaTitleValue);
$sql = "UPDATE ".$table." SET `value` = '". $newMetaTitleValue."' WHERE value_id = ".$id;
$logger->info("更新meta title 的值".$sql);
$connection->query($sql);
}
完整代码如下:
<?php
/**
* Created by PhpStorm.
* User: yshuq
* Date: 2022/7/18
* Time 2:57 PM
*/
declare(strict_types=1);
namespace Cg\MetaTitleCron\Cron;
use Magento\Framework\App\ResourceConnection;
class CronJob
{
protected $resourceConnection;
public function __construct(
ResourceConnection $resourceConnection
)
{
$this->resourceConnection = $resourceConnection;
}
public function execute()
{
//操作日志更改记录
$writer = new \Zend\Log\Writer\Stream(BP . '/var/log/meta_title.log');
$logger = new \Zend\Log\Logger();
$logger->addWriter($writer);
$connection = $this->resourceConnection->getConnection();
//1.查询meta_title 的属性id
$attributeId = '1';
$table = $connection->getTableName('eav_attribute');
$query = "Select * from " . $table . " WHERE `attribute_code` = 'meta_title' and `frontend_label` = 'Meta Title'";
$logger->info("查询meta title 的 id:".$query);
$result1 = $connection->fetchAll($query);
if (!empty($result1) && count($result1) == 1) {
$attributeId = $result1[0]['attribute_id'];
}
//2.获取所有网站 store_website 循环遍历网站 website_id
$table = $connection->getTableName('store_website');
$query = "Select * FROM " . $table . " where `name` != 'Admin'";
$logger->info("查询所有网站:".$query);
$websiteList = $connection->fetchAll($query);
$logger->info("所有网站list:".\GuzzleHttp\json_encode($websiteList));
if (!empty($websiteList) && count($websiteList) > 0) {
$webId = $websiteName = $query = '';
foreach ($websiteList as $v1 => $j1) {
$websiteName = $j1['name'];
$webId = $j1['website_id'];
//3.根据website_id 循环遍历产品 product_id
$table = $connection->getTableName('catalog_product_website');
$query = "Select * FROM " . $table . " where website_id = " . $webId;
$productList = $connection->fetchAll($query);
$logger->info("根据网站id查询所有产品:".$query);
$logger->info("网站id:".$webId."的全部产品".\GuzzleHttp\json_encode($productList));
if (!empty($productList) && count($productList) > 0) {
foreach ($productList as $v2 => $j2) {
$productId = $j2['product_id'];
$logger->info("product id:" . $productId);
//4.根据website_id 在store表查询出店铺id
$table = $connection->getTableName('store');
$query = "Select * FROM " . $table . " where `website_id` =" . $webId;
$storeList = $connection->fetchAll($query);
$logger->info("根据website_id查询店铺列表" . $query);
$logger->info("网站id: ".$webId."店铺列表".\GuzzleHttp\json_encode($storeList));
if (!empty($storeList) && count($storeList) > 0) {
foreach ($storeList as $v3 => $j3) {
$storeId = $j3['store_id'];
//5.获取 默认店铺admin store_id = 0 的value的值
$table = $connection->getTableName('catalog_product_entity_varchar');
$query = "Select * FROM " . $table . " where store_id = 0 and entity_id = " . $productId
. " and attribute_id = " . $attributeId;
$metaTitleValue0 = $connection->fetchAll($query);
$logger->info("获取默认值sql:".$query);
$logger->info("获取admin点meta title默认值:".\GuzzleHttp\json_encode($metaTitleValue0));
//6.根据店铺id,产品id ,属性id,查询 属性为meta_tile的值,
$query = "Select * FROM " . $table . " where store_id = " . $storeId . " and entity_id = " . $productId
. " and attribute_id = " . $attributeId;
$metaTitleValue1 = $connection->fetchAll($query);
$logger->info("获取当前网站店铺产品的meta title sql:".$query);
$logger->info("获取当前网站店铺产品的meta title 的值:".\GuzzleHttp\json_encode($metaTitleValue1));
//7.组合新值,获取store id = 0 后台默认值。
$mt = explode('-', $metaTitleValue0[0]['value']);
$newMetaTitleValue = $mt[0] . "-" . $websiteName;
//8.meta title 不存在 就添加, 存在,就更新
if (empty($metaTitleValue1) && count($metaTitleValue1) == 0) {
$data = [
'attribute_id' => $attributeId,
'store_id' => $storeId,
'entity_id' => $productId,
'value' => $newMetaTitleValue
];
$logger->info("添加的meta title 的值".\GuzzleHttp\json_encode($data));
$connection->insert($table,$data);
}else{
$id = $metaTitleValue1[0]['value_id'];
$newMetaTitleValue = str_replace("'", ' ', $newMetaTitleValue);
$sql = "UPDATE ".$table." SET `value` = '". $newMetaTitleValue."' WHERE value_id = ".$id;
$logger->info("更新meta title 的值".$sql);
$connection->query($sql);
}
}
}
}
}
}
}
}
}