Quickly associate images to your Magento products without using dataflow
Dataflow is great and does allow the association of multiple images natively, but upon wanting to add around 2,500 product images to a catalogue in Magento 1.5.0.1 - we noticed that they have broke the core support in dataflow for this,great!
So instead, we just knocked up a quick script which would associate images to products; the name name has to match the product SKU (with the exception of the file extension). It first copies the images from your media/import directory to the proper Magento structure,
eg. For an image named sonassi.jpg
./media/catalog/product/s/o/sonassi.jpg
If the directory doesn't exist, it will create it on the fly. Then once the images are in place, it will check to see what images are to be added - then check each product to see if any images are already set, and skip those images if already set.
There is a few different types of images for a product:
small_image
base_image
thumbnail
gallery_image
These have different attribute ids (can be found in eav_entity_attribute), for us the IDs were 74,75,76 and 77 respectively. Then, its just a case of running the script.
To create the directory structure, copy the images and populate the database took under 1 second for 2,500 images; so whilst not the proper Magento way - its saved you a few hours of Dataflow! As always, the below script assumes that the script exists in a subdirectory of the root Magento installation.
Enjoy!
<?php
// ###########################################################################
//
// Change these variables to suit your store!
//
$_mediaBase = realpath('../media/catalog/product') . '/';
$_imagesSrc = realpath('../media/import/product_images') . '/';
$imgAttrIds = array(
74,
75,
76
);
$imageGalId = 77;
require_once '../app/Mage.php';
//
// ###########################################################################
umask(0);
error_reporting(E_ALL);
ini_set('display_errors', '1');
Mage::setIsDeveloperMode(true);
Mage::app()->setCurrentStore(Mage_Core_Model_App::ADMIN_STORE_ID);
$conn = Mage::getSingleton('core/resource')->getConnection('core_read');
$connW = Mage::getSingleton('core/resource')->getConnection('core_write');
$images = scandir($_imagesSrc);
/*
* Relocate the images into Magento friendly format
*/
$imgArr = array();
foreach($images as $image) {
if (in_array($image, array(
'.',
'..'
))) continue;
$firstDir = $_mediaBase . $image[0];
$secondDir = strtolower($firstDir . '/' . $image[1]);
$path = strtolower($secondDir . '/' . $image);
if (!file_exists($path)) {
echo "Making $secondDir and copying to $pathn";
if (!file_exists($secondDir)) mkdir($secondDir, 0775, true);
copy($_imagesSrc . $image, $path);
}
$imgArr[] = "'" . str_ireplace(".jpg", "", $image) . "'";
}
$sql = "SELECT *
FROM catalog_product_entity
WHERE sku IN (" . implode(",", $imgArr) . ")";
$_products = $conn->fetchAll($sql);
$entitySkuMap = array();
foreach($_products as $product) $entitySkuMap[$product['sku']] = $product['entity_id'];
/*
* Check the existing images
*/
$sql = "SELECT *
FROM catalog_product_entity_media_gallery
WHERE entity_id IN (" . implode(",", $entitySkuMap) . ")";
$_galleryImgs = $conn->fetchAll($sql);
$sql = "SELECT *
FROM catalog_product_entity_varchar
WHERE entity_id IN (" . implode(",", $entitySkuMap) . ")
AND attribute_id IN (" . implode(",", $imgAttrIds) . ")";
$_imageAssoc = $conn->fetchAll($sql);
$existingImgs = array();
foreach($_imageAssoc as $img) {
$existingImgs[$img['entity_id']][$img['attribute_id']] = $img;
}
/*
* Then associate to the product itself.
*/
$insertData = array();
$skusToInsert = array();
foreach($_products as $productArrId => $product) {
$missingImgs = $imgAttrIds;
$imageName = strtolower('/' . $product['sku'][0] . '/' . $product['sku'][1] . '/' . $product['sku'] . '.jpg');
// Check if it has an image associated already ...
if (array_key_exists($product['entity_id'], $existingImgs)) {
// Check which images exists and remove those already set
foreach($imgAttrIds as $id => $val) {
if (array_key_exists($val, $existingImgs[$product['entity_id']])) {
if ($existingImgs[$product['entity_id']][$val]['value'] == "no_selection") {
$sql = "DELETE FROM catalog_product_entity_varchar WHERE value_id = '" . $existingImgs[$product['entity_id']][$val]['value_id'] . "'";
$connW->query($sql);
}
else {
unset($missingImgs[$id]);
}
}
}
}
if (count($missingImgs)) {
if (file_exists($_mediaBase . $imageName)) {
foreach($missingImgs as $img) {
$insertData[] = "(4, " . $img . ", 0, " . $product['entity_id'] . ", '" . $imageName . "')";
}
$skusToInsert[] = "(" . $imageGalId . ", " . $product['entity_id'] . ", '" . $imageName . "')";
}
}
}
/*
* Now add the images to the DB
*/
if (count($skusToInsert)) {
$sql = "INSERT INTO catalog_product_entity_media_gallery (attribute_id, entity_id, value) VALUES " . implode(",", $skusToInsert) . ";";
$connW->query($sql);
echo "Updated " . count($skusToInsert) . " imagesn";
}
if (count($insertData)) {
$sql = "INSERT INTO catalog_product_entity_varchar (entity_type_id, attribute_id, store_id, entity_id, value) VALUES " . implode(",", $insertData) . ";";
$connW->query($sql);
echo "Updated " . count($insertData) . " images";
}