Naturally you can query Magento database directly as described in my earlier post about querying Magento database. But a much smarter way is it, to use exiting Magento objects and constructs like that:

<?php
/**
 * Sets position (sort order) values for the given attribute for a given option value. Therefore the given store specific value is
 * used to lookup the necessary option id to identify the correct option
 * @param sting $sAttributeCode Magento attribute code for that option value position should be set
 * @param sting $sValue  option value in the given store
 * @param int $iPosition position (sort order) to set for given option value
 * @param int $iReferenceStoreId store to search for a option label
 * @throws Zend_Db_Adapter_Exception
 */
public function setAttributeOptionSorting($sAttributeCode, $sValue, $iPosition, $iReferenceStoreId)
{
    /* @var Varien_Db_Adapter_Pdo_Mysql $oWriteConnection */
    $oWriteConnection = Mage::getSingleton('core/resource')->getConnection('core_write');

    $sOptionTable = Mage::getSingleton('core/resource')->getTableName('eav/attribute_option');
    $sOptionLableTable = Mage::getSingleton('core/resource')->getTableName('eav/attribute_option_value');

    $iAttributeId = Mage::getSingleton('eav/config')->getAttribute(Mage_Catalog_Model_Product::ENTITY, $sAttributeCode)->getId();


    $oSelect = new Varien_Db_Select($oWriteConnection);
    $oSelect->from(
        ['eao' => $sOptionTable],
        ['option_id']
    )->join(
        ['eaov' => $sOptionLableTable],
        "eao.option_id = eaov.option_id AND eaov.store_id = " . $iReferenceStoreId,
        ''
    )->where(
        'eao.attribute_id = ?',
        $iAttributeId
    )->where(
        'eaov.value = ?',
        $sValue
    );

    $aResult = $oWriteConnection->fetchCol($oSelect);

    foreach ($aResult as $iOptionId) {
        $oWriteConnection->update(
            $sOptionTable,
            ['sort_order' => $iPosition],
            $oWriteConnection->quoteInto('option_id = ?', $iOptionId)
        );
    }
}

This is a simplified version of a function (better to say shell script class) that sets attribute value positions for a certain attribute code.

The bare query version of the code above would look like that:

<?php
/**
 * Sets position (sort order) values for the given attribute for a given option value. Therefore the given store specific value is
 * used to lookup the necessary option id to identify the correct option
 * @param sting $sAttributeCode Magento attribute code for that option value position should be set
 * @param sting $sValue  option value in the given store
 * @param int $iPosition position (sort order) to set for given option value
 * @param int $iReferenceStoreId store to search for a option label
 * @throws Zend_Db_Adapter_Exception
 */
public function setAttributeOptionSorting($sAttributeCode, $sValue, $iPosition, $iReferenceStoreId)
{
    /* @var Varien_Db_Adapter_Pdo_Mysql $oWriteConnection */
    $oWriteConnection = Mage::getSingleton('core/resource')->getConnection('core_write');

    $sOptionTable = Mage::getSingleton('core/resource')->getTableName('eav/attribute_option');
    $sOptionLableTable = Mage::getSingleton('core/resource')->getTableName('eav/attribute_option_value');

    $iAttributeId = Mage::getSingleton('eav/config')->getAttribute(Mage_Catalog_Model_Product::ENTITY, $sAttributeCode)->getId();
    
    $sSelect = "SELECT 
                  `eao`.`option_id` 
                FROM `$sOptionTable` AS `eao`
                INNER JOIN `$sOptionLableTable` AS `eaov` 
                  ON eao.option_id = eaov.option_id 
                  AND eaov.store_id = $iReferenceStoreId 
                WHERE (eao.attribute_id = '$iAttributeId') 
                  AND (eaov.value = '$sValue')";

    $aResult = $oWriteConnection->fetchCol($sSelect);

    foreach ($aResult as $iOptionId) {
        $sUpdateQuery = "UPDATE 
                          `$sOptionTable` 
                        SET 
                          `sort_order` = ':iPosition'
                        WHERE 
                          option_id = ':iOptionId'";
        $this->_getWriteConnection()->query($sUpdateQuery, [$iPosition, $iOptionId]);
    }
}

But that’s not state of the art. It is strongly recommended to use the build in constructs to query Magento database whenever possible (there are reasonable exceptions). The abstraction helps to prevent errors and makes the code better maintainable. Another enhancement is, that through the abstraction of database interactions, it is possible to change the database type without the need of a complete code refactoring (in best case 😉 ).

By the way. If you want to know, how I got the queries out of the the objects in the first code snippet, you can read this elderly blog post.

For those who want to see the complete position change shell script, should have a look at my blog post about Option value position (select value sort order).

More “Query Magento database” examples

Here are a few more examples on how to query Magento database the right way:

• insert multiple items to a custom table:

  <?php
  /* @var Mage_Core_Model_Resource_Db_Abstract $this*/
  $aInserData = [];
  foreach ($aAddCandidates as $iProductId => $iPosition) {
      array_push($aInserData,[
          'category_id' => (int) $oCategory->getId(),
          'product_id'  => (int) $iProductId,
          'position'    => (int) $iPosition
      ]);
  }
  $iEffectedRowCount = $this->_getWriteAdapter()->insertMultiple(
      $this->getTable('asksheldon_featuredproducts/category_product'),
      $aInserData
  );

• delete multiple items:

  <?php
  /* @var Mage_Core_Model_Resource_Db_Abstract $this*/
  $aWhere = [
      'product_id IN(?)' => array_keys($aRemoveCandidates),
      'category_id=?' => $oCategory->getId()
  ];
  $iEffectedRowCount $this->_getWriteAdapter()->delete(
      $this->getTable('asksheldon_featuredproducts/category_product'),
      $aWhere
  );