Products | The Magento Expert

Getting products with or without custom options using SQL (Magento 1)

15. Januar 2016 Magento Solutions

To get all products that do not have custom options, simply run the following SQL query: SELECT * FROM catalog_product_entity WHERE entity_id NOT IN (SELECT DISTINCT product_id FROM catalog_product_option); To get all products that do have custom options, remove the…

View solution

Getting the status of every product in the database using MySQL (Magento 1)

Mex 30. September 2014 Magento Solutions

The following MySQL query returns the status of every product in the Magento database.

SELECT
  cpe.sku,
  q.product_status
FROM catalog_product_entity cpe
  JOIN (SELECT
    entity_id AS product_id,
    value AS product_status
  FROM catalog_product_entity_int
  WHERE entity_type_id = (SELECT
    entity_type_id
  FROM eav_entity_type
  WHERE entity_type_code = 'catalog_product')
  AND attribute_id = (SELECT
    attribute_id
  FROM eav_attribute
  WHERE attribute_code = 'status'
  AND entity_type_id = (SELECT
    entity_type_id
  FROM eav_entity_type
  WHERE entity_type_code = 'catalog_product'))) q
    ON cpe.entity_id = q.product_id;

SELECT

cpe.sku,

q.product_status

FROM catalog_product_entity cpe

JOIN (SELECT

entity_id AS product_id,

value AS product_status

FROM catalog_product_entity_int

WHERE entity_type_id = (SELECT

entity_type_id

FROM eav_entity_type

WHERE entity_type_code = 'catalog_product')

AND attribute_id = (SELECT

attribute_id

FROM eav_attribute

WHERE attribute_code = 'status'

AND entity_type_id = (SELECT

entity_type_id

FROM eav_entity_type

WHERE entity_type_code = 'catalog_product'))) q

ON cpe.entity_id = q.product_id;

Status 1: active Status 2: not active

View solution

Change column count of category page in admin area (backend) (Magento 1)

Mex 5. September 2014 Magento Solutions

Open the category in the admin area (backend) and add the following xml to the layout update section.

<reference name="product_list">
    <action method="setColumnCount"><count>4</count></action>
</reference>

</reference>

View solution

Getting the URL of a product image and display the image (Magento 1)

Mex 12. Juli 2013 Magento Solutions

Use the following code to get a Magento products URL and to display the image on a tempate page:

<img src="<?php echo $_product->getImageUrl(); ?>" alt="<?php echo $_product->getName();?>" />

1	<img src="<?php echo $_product->getImageUrl(); ?>" alt="<?php echo $_product->getName();?>" />

View solution

Deleting upsell products directly in the database (Magento 1)

Mex 11. April 2013 Magento Solutions

Once saved, upsell products can not be removed again easily. Using the following MySQL script, you can delete Magento upsell products from the database.

DELETE link.* FROM catalog_product_link AS link
LEFT JOIN catalog_product_link_type AS linktype ON (linktype.link_type_id = link.link_type_id)
WHERE linktype.code = "up_sell" and product_id = 123;

DELETE link.* FROM catalog_product_link AS link

LEFT JOIN catalog_product_link_type AS linktype ON (linktype.link_type_id = link.link_type_id)

WHERE linktype.code = "up_sell" and product_id = 123;

View solution

Loading a product by id (Magento 1)

Mex 10. April 2013 Magento Solutions

Use the following code to load a product by id:

$productId = 1234;
$product = Mage::getModel('catalog/product')->load($productId);

1 2	$productId = 1234; $product = Mage::getModel('catalog/product')->load($productId);

View solution