123 行
4.2 KiB
PHP
123 行
4.2 KiB
PHP
<?php
|
|
/**
|
|
* 为剩余酒类商品更新图片
|
|
*/
|
|
|
|
// 数据库配置
|
|
$host = '127.0.0.1';
|
|
$dbname = 'mss_nahaichuan_c';
|
|
$username = 'mss_nahaichuan_c';
|
|
$password = 'MyNmyAaRj9yWKPKH';
|
|
|
|
try {
|
|
$pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8", $username, $password);
|
|
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
|
|
|
|
echo "数据库连接成功\n";
|
|
|
|
// 为剩余商品分配图片的映射规则
|
|
$image_mapping = [
|
|
// 白酒类商品
|
|
'水井坊' => 'wuliangye.jpg',
|
|
'洋河蓝色经典' => 'jiannanchun.jpg',
|
|
'西凤酒' => 'luzhoulao.jpg',
|
|
|
|
// 啤酒类商品
|
|
'珠江啤酒' => 'qingdao_beer.jpg',
|
|
'喜力啤酒' => 'budweiser_beer.jpg',
|
|
'重庆啤酒' => 'harbin_beer.jpg',
|
|
'金威啤酒' => 'snow_beer.jpg',
|
|
|
|
// 红酒类商品
|
|
'张裕解百纳' => 'red_wine_2.jpg',
|
|
'通化红酒' => 'red_wine_3.jpg',
|
|
|
|
// 洋酒类商品
|
|
'人头马' => 'brandy.jpg',
|
|
'杰克丹尼' => 'whisky.jpg',
|
|
'芝华士' => 'whisky.jpg',
|
|
'轩尼诗' => 'brandy.jpg',
|
|
'马爹利' => 'brandy.jpg'
|
|
];
|
|
|
|
// 获取没有图片的酒类商品
|
|
$sql = "
|
|
SELECT g.id, g.goods_name, gc.class_name
|
|
FROM ls_retail_goods g
|
|
LEFT JOIN ls_retail_goods_class gc ON g.goods_class_id = gc.id
|
|
WHERE (g.goods_name LIKE '%酒%' OR gc.class_name LIKE '%酒%')
|
|
AND (g.goods_image IS NULL OR g.goods_image = '')
|
|
ORDER BY gc.class_name, g.goods_name
|
|
";
|
|
|
|
$stmt = $pdo->query($sql);
|
|
$products = $stmt->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
echo "找到 " . count($products) . " 个需要更新图片的商品\n\n";
|
|
|
|
$updated_count = 0;
|
|
|
|
foreach ($products as $product) {
|
|
$goods_name = $product['goods_name'];
|
|
$goods_id = $product['id'];
|
|
$class_name = $product['class_name'];
|
|
|
|
// 根据商品名称匹配图片
|
|
$image_file = null;
|
|
foreach ($image_mapping as $keyword => $image) {
|
|
if (strpos($goods_name, $keyword) !== false) {
|
|
$image_file = $image;
|
|
break;
|
|
}
|
|
}
|
|
|
|
// 如果没有精确匹配，根据分类分配默认图片
|
|
if (!$image_file) {
|
|
if (strpos($class_name, '白酒') !== false) {
|
|
$image_file = 'wuliangye.jpg';
|
|
} elseif (strpos($class_name, '啤酒') !== false) {
|
|
$image_file = 'qingdao_beer.jpg';
|
|
} elseif (strpos($class_name, '红酒') !== false || strpos($class_name, '葡萄酒') !== false) {
|
|
$image_file = 'red_wine_1.jpg';
|
|
} else {
|
|
$image_file = 'whisky.jpg'; // 洋酒默认
|
|
}
|
|
}
|
|
|
|
// 更新数据库
|
|
$update_sql = "UPDATE ls_retail_goods SET goods_image = ? WHERE id = ?";
|
|
$update_stmt = $pdo->prepare($update_sql);
|
|
$result = $update_stmt->execute([$image_file, $goods_id]);
|
|
|
|
if ($result) {
|
|
echo "✓ 更新成功: {$goods_name} ({$class_name}) -> {$image_file}\n";
|
|
$updated_count++;
|
|
} else {
|
|
echo "✗ 更新失败: {$goods_name}\n";
|
|
}
|
|
}
|
|
|
|
echo "\n=== 更新完成 ===\n";
|
|
echo "成功更新 {$updated_count} 个商品的图片\n";
|
|
|
|
// 验证最终结果
|
|
$final_sql = "
|
|
SELECT
|
|
COUNT(*) as total_alcohol_products,
|
|
COUNT(CASE WHEN goods_image IS NOT NULL AND goods_image != '' THEN 1 END) as products_with_images
|
|
FROM ls_retail_goods g
|
|
LEFT JOIN ls_retail_goods_class gc ON g.goods_class_id = gc.id
|
|
WHERE g.goods_name LIKE '%酒%' OR gc.class_name LIKE '%酒%'
|
|
";
|
|
|
|
$final_result = $pdo->query($final_sql)->fetch(PDO::FETCH_ASSOC);
|
|
|
|
echo "\n=== 最终统计 ===\n";
|
|
echo "酒类商品总数: {$final_result['total_alcohol_products']}\n";
|
|
echo "有图片商品数: {$final_result['products_with_images']}\n";
|
|
echo "图片覆盖率: " . round(($final_result['products_with_images'] / $final_result['total_alcohol_products']) * 100, 1) . "%\n";
|
|
|
|
} catch (Exception $e) {
|
|
echo "错误: " . $e->getMessage() . "\n";
|
|
}
|
|
?>
|