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.

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

  • Thanks for that quick response, but can I not run a normal SQL command somehow on my Magento database? – Klettseb Feb 23 '15 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 '15 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? – Klettseb Feb 23 '15 at 14:22
  • Amit I updated my description can you help me again. – Klettseb Feb 24 '15 at 15:14
  • 1
    please check my update .... – Amit Bera Feb 25 '15 at 18:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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