Change category product sort order values

Question

I want to change one time all sort order values of the products inside all categories, by using a php script.

Currently all products inside all categories do got the value 1 for the sort order.

I want to change this to 10000, so that I can easily change the sort order of the products.

How can I achieve that?

Answer 1

Ah sorry, i might have read your question wrong.

The thing i can think of is dirty but effective.

You can run a query like this:

$writeConnection = $resource->getConnection('core_write');
$writeConnection->query("UPDATE `catalog_category_product` SET `position`= 10000 WHERE `category_id` = 23");

I have tested the query on my own magento database and it works like a charm.

do note: 23 in this case was the id of a specific category so change that to the category you would like to update with the new data.

After you have run the query run a index of product flat data else the new data won't show up.

As I said it is dirty doing it this way.

In any other case you could try and write your own module handeling all this for you by simply adding a category id and to what number the position of the products should change to.

Answer 2

I've tested below code for few products to update the positions in there categories and it worked as expected.

Place this code in a php file inside your project folder and run it. It is highly recommended to get a backup of table catalog_category_product before you run the script.

Even though there is an API method specifically to do this, I have used direct SQL query as it eliminates most of the Magento's overhead (e.g. event observers related) if we used API. Specially when there's a large set of data required to be updated, then direct SQL queries act faster.

I'm not encouraging to use direct SQL queries, but since this is a temporal php script which you can use and throw away it would be fine.

The code :

<?php

$mageFilename = 'app/Mage.php'; // Put your Mage.php path here
require_once $mageFilename;
Mage::setIsDeveloperMode(true);
ini_set('display_errors', 1);
umask(0);
Mage::app();

$resource = Mage::getSingleton('core/resource');
$table = $resource->getTableName('catalog_category_product');
$writeConnection = $resource->getConnection('core_write');
$position = 10000;

$categories = Mage::getModel('catalog/category')
    ->getCollection()
    ->addAttributeToSelect('*')
    ->addIsActiveFilter();

foreach ($categories as $category) {
    $catId = $category->getId();
    $products = Mage::getModel('catalog/category')->load($catId)
        ->getProductCollection()
        ->addAttributeToSelect('*')
        ->addFieldToFilter('status', array('eq' => Mage_Catalog_Model_Product_Status::STATUS_ENABLED))
        ->addFieldToFilter('visibility', Mage_Catalog_Model_Product_Visibility::VISIBILITY_BOTH);
    foreach ($products as $product) {
        $productId = $product->getId();
        $query = "UPDATE {$table} SET position = '{$position}' WHERE category_id = " . $catId . " AND product_id = " . $productId;
        $writeConnection->query($query);
    }
}