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?

  • I think you'll find your answer here: magento.stackexchange.com/questions/52053/…
    – Myron
    Commented Apr 25, 2017 at 14:28
  • @Myron Thanks, but that is not exactly what I am looking for. I need to update all products inside that category and give that the position 10000.
    – JGeer
    Commented Apr 25, 2017 at 18:32
  • What Magento version you are using?
    – Sukeshini
    Commented Apr 26, 2017 at 5:20
  • @Sukeshini Version
    – JGeer
    Commented Apr 26, 2017 at 6:45

2 Answers 2


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.

  • Thanks, but in that case I need to edit it for all categories by hand. Is there also a way to create a php script, that update all categories?
    – JGeer
    Commented Apr 26, 2017 at 6:45
  • @HenkZ You could try to run this instead: UPDATE catalog_category_product SET position= 10000, don't forget to run index afterwards.
    – Myron
    Commented Apr 26, 2017 at 7:10

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 :


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

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

$categories = Mage::getModel('catalog/category')

foreach ($categories as $category) {
    $catId = $category->getId();
    $products = Mage::getModel('catalog/category')->load($catId)
        ->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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.