2

I got a probably really simple question but I couldn´t find a solution for now. I found heaps of questions etc. but for now nothing which would be what I need.

I am trying to use a query on magento but I´m wondering how I run the SQL command.

I want to run something like

select substr(sku,1) as sku 
.....and so on....

and then I want the result to be written in a .txt file

UPDATE

I want to run the following command

select substr(sku,1) as sku 
  from catalog_product_entity
  where sku like 'S%'
  order by sku asc;

and then write that in a .csv or .txt file. Where does the code go and where will the file be? I need a full explanation if possible.

1 Answer 1

3

SUBSTRING returns a specified number of characters from a particular position of a given string.

In magento,you can use the function getSubstringSql($stringExpression, $pos).It is PDO library function. By which you can create sub string of a field.

$stringExpression may be a field name or SQL

   public function getSubstringSql($stringExpression, $pos, $len = null)
    {
        if (is_null($len)) {
            return new Zend_Db_Expr(sprintf('SUBSTRING(%s, %s)', $stringExpression, $pos));
        }
        return new Zend_Db_Expr(sprintf('SUBSTRING(%s, %s, %s)', $stringExpression, $pos, $len));
    }

Example:

$Collection = Mage::getModel('catalog/product')->getCollection();
$Collection
    ->getSelect()
    ->getSubstringSql('name',10);

You can try this

$Collection = Mage::getModel('catalog/product')->getCollection()->addAttributeToSelect('sku');
$Collection
    ->getSelect()
    ->getSubstringSql('sku',1)

Update: According to your chat you need use like in condition

$Collection = Mage::getModel('catalog/product')->getCollection()->addAttributeToSelect('sku')->addAttributeToFilter('sku', array(
   'like' => 'A%',

));

According your update

I have add new columns to using zend expression

$productCollection=Mage::getModel('catalog/product')->getCollection()->addAttributeToSelect('sku')->addAttributeToFilter('sku', array(
   'like' => 'A%'));

 $productCollection->getSelect();   
$write = Mage::getSingleton("core/resource")->getConnection("core_write");
$requestPathField = new Zend_Db_Expr($write->quoteIdentifier('sku'));
 $write->getSubstringSql($requestPathField,1);

echo $productCollection->getSelect()->columns(array('mysku' =>  $write->getSubstringSql($requestPathField,1)));
foreach($productCollection as $product):
echo $product->getMysku().'<br/>';
endforeach;

See at http://www.magentocommerce.com/wiki/1_-_installation_and_configuration/using_collections_in_magento

7
  • Thanks for that quick response, but can I not run a normal SQL command somehow on my Magento database?
    – seb
    Feb 23, 2015 at 13:20
  • 1
    Yes.you can do this and it is ok whenever want to only fetch sku fields.Please explain what is your requirement
    – Amit Bera
    Feb 23, 2015 at 13:23
  • I want to get a list from all the products where the SKU starts with a specific letter So I thought I can do Substr(sku,1) as Sku from xxxx where sku like 'A%'; But where do I run this so that I will get a file at the end containing all the products?
    – seb
    Feb 23, 2015 at 14:22
  • Amit I updated my description can you help me again.
    – seb
    Feb 24, 2015 at 15:14
  • 1
    please check my update ....
    – Amit Bera
    Feb 25, 2015 at 18:09

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.