Magento set products category sort number programmatically

Question

I am importing data from oscommerce to magento for one of my clients.

All data including products, categories, and customers has been imported successfully. The last thing left is adding a product sort number after setting their categories.

I need following steps:

Import products -> assign categories to product -> set product sort order (display order) from the oscommerce table.

I searched a lot, but I was not able to find the method to set a product's sort order in a particular category.

Any help will be deeply appreciated.

Diwaker Tripathi Diwaker Tripathi · Accepted Answer · 2013-11-11T18:48:30

Product's sort order in a category is by default determined by the position column in 'Category Products' tab in Manage Categories section.

I guess you need to bulk update the sort orders of the products per category. Assuming that you may need to write a php script, that can update the 'position' column in table 'catalog_category_product' in database using sql queries.

File can be kept at the root directory in your magento installation. Below code is just to give an idea, you need to modify/add/remove code in order to complete it as per your requirement and then hit the file from your browser.

<?php

$mageFilename = 'app/Mage.php';
require_once $mageFilename;
Mage::setIsDeveloperMode(true);
ini_set('display_errors', 1);
umask(0);
Mage::app('admin');
Mage::register('isSecureArea', 1);
Mage::app()->setCurrentStore(Mage_Core_Model_App::ADMIN_STORE_ID);

set_time_limit(0);
ini_set('memory_limit','1024M');

/***************** UTILITY FUNCTIONS ********************/
function _getConnection($type = 'core_read'){
    return Mage::getSingleton('core/resource')->getConnection($type);
}

function _getTableName($tableName){
    return Mage::getSingleton('core/resource')->getTableName($tableName);
}

function _updatePosition($position, $categoryId, $productId){
    $connection     = _getConnection('core_write');
    $sql = "UPDATE " . _getTableName('catalog_category_product') . " ccp
                SET  ccp.position = ?
            WHERE  ccp.category_id = ?
            AND ccp.product_id = ?";
    $connection->query($sql, array($position, $categoryId, $productId));
}

hope this help !

Magento set products category sort number programmatically

1 Answers