Query Magento 1.9 Database for All Images and Their Locations

databasemagento-1.9product-imagessql

We recently completed a migration from M1.9 to M2.1.9 however, there seems to be the odd missing picture. What I'm hoping to do is run a query to identify ALL images that are listed in Magento 1 DB with the expected file location and file name, and then compare that to a list of images that were successfully copied to our M2 installation, and identify those missing images.

Does anybody know the SQL queries I can run to identify all images in M1.9 database?

Thanks!

Best Answer

I don't think it's necessary to check catalog_product_entity_varchar first. Image "names" are stored in catalog_product_entity_media_gallery table, but the DB value already contains subfolder prefixes:

So for your product_image.png the entry is /p/r/product_image.png.

Raw SQL for filename ad location could look like:

SELECT
  SUBSTRING(value, 6) as 'file',
  CONCAT('your_magento_url/media/catalog/product', value) as 'path'
FROM catalog_product_entity_media_gallery;

... or for php script ...

$path = Mage::getBaseUrl(Mage_Core_Model_Store::URL_TYPE_MEDIA) . 'catalog' . DS . 'product';

$resource = Mage::getSingleton('core/resource');
$read = $resource->getConnection('core_read');
$query = $read->select()
    ->from($resource->getTableName('catalog_product_entity_media_gallery'), array('file', 'path'))
    ->reset(Zend_Db_Select::COLUMNS)
    ->columns('SUBSTRING(value, 6) as file')
    ->columns('CONCAT("' . $path . '", value) as path');

$images = $read->fetchPairs($query);

This would give you an array like

array(
   'product_image.png' => 'full/path/to/product_image.png',
   ...
);
Related Topic