Stock Level Import Script
This is an old revision of the document!
A popular problem with Magento is how to import stock levels from another system, e.g. an ERP system. Although Magento has the API for easily carrying out such tasks it is s-l-o-w. Hence, here is an example of how to do it quickly and easily. This exceptionally well documented, clean code is thanks to michael_g08, see thread here: http://www.magentocommerce.com/boards/viewthread/32491
- <?php
- ////////////////GLOBAL DEFINITIONS/////////////////////
- //DATABASE SETTINGS
- $dbConfig = array(
- 'host' => 'HOSTNAME',
- 'username' => 'USERNAME',
- 'password' => 'PASSWORD',
- 'dbname' => 'MAGENTO DB NAME',
- 'driver_options'=> array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES UTF8')
- );
- ///// Path to Your CSV file
- $path = '/var/www/html/MAGENTO/import/WebStockInput.csv';
- // Path to Mage.php
- require_once '/var/www/html/MAGENTO/app/Mage.php';
- // Connect to the databases
- $db_magento = Zend_Db::factory('Pdo_Mysql', $dbConfig);
- updateTempTableFromFile($db_magento, $path);
- $query = $db_magento->select()->from('temp_table');
- $contents_array = $db_magento->fetchAll($query);
- // Run for each line
- foreach ($contents_array as $line) {
- $sku = $line['sku'];
- $qty = $line['qty'];
- $exists = $db_magento->query("SELECT COUNT(sku) cnt FROM catalog_product_entity WHERE sku = '$sku' LIMIT 1");
- $find_product = (($exists->fetchObject()->cnt) > 0) ? true : false;
- if ($find_product == true) {
- $entity_id = getEntityID_bySKU($db_magento, $sku);
- updateQTY ($db_magento, $entity_id, $qty);
- }
- }
- function getEntityID_bySKU($db_magento, $sku) {
- $entity_row = $db_magento->query("SELECT entity_id FROM catalog_product_entity p_e WHERE p_e.sku = '$sku'")->fetchObject();
- $entity_id = $entity_row->entity_id;
- return $entity_id;
- }
- function updateQTY($db_magento, $entity_id, $qty) {
- $db_magento->query("UPDATE cataloginventory_stock_item s_i, cataloginventory_stock_status s_s
- SET s_i.qty = '$qty', s_i.is_in_stock = IF('$qty'>0, 1,0),
- s_s.qty = '$qty', s_s.stock_status = IF('$qty'>0, 1,0)
- WHERE s_i.product_id = '$entity_id' AND s_i.product_id = s_s.product_id ");
- }
- function updateTempTableFromFile($db_magento, $path){
- $db_magento->query("TRUNCATE TABLE temp_table");
- $db_magento->query("LOAD DATA INFILE '$path'
- INTO TABLE temp_table
- CHARACTER SET cp1251
- FIELDS TERMINATED BY ','
- LINES TERMINATED BY 'n'
- (`store`,`sku`, `qty`,`is_in_stock`)");
- }
- function getAttributeEAV_id ($db_magento, $attribute_name)
- {
- $result = $db_magento->query("SELECT attribute_id FROM eav_attribute WHERE entity_type_id = 4 AND attribute_code = '$attribute_name'")->fetchObject()->attribute_id;
- return $result;
- }
Some tips to popular issues: 1- Add FILES privilege to mysql user to run this cript 2- If you get an error with LOAD DATA INFILE command, try LOAD LOCAL DATA INFILE.
