After executing my query i got several products (SKUs) but it is not displaying all the products that are stored in the database.

Take a look at this picture:


How do i know that? Well, I put a random SKU in the MAGENTO application, then i clicked on "search" and the product appeared.

I tried to find that SKU in MYSQL but it was not there:

Take a look:


This is my query:

SELECT main.entity_id,main.sku,code_brand.value AS code_brand

FROM catalog_product_entity AS main 

LEFT JOIN catalog_product_entity_int AS code_brand
ON main.entity_id = code_brand.entity_id

WHERE code_brand.attribute_id = (
    SELECT attribute_id 
    FROM eav_attribute 
    WHERE attribute_code LIKE 'code_brand'
    AND `entity_type_id` = (
        SELECT entity_type_id
        FROM eav_entity_type
        WHERE entity_type_code = 'catalog_product'

What is happening here? Am i missing anything?

This is the MAGENTO database diagram:



How can i get all products with status = disabled or enabled?

I've been searching and i found this:

SELECT entity_id FROM `catalog_product_entity_int`
WHERE attribute_id = (
    SELECT attribute_id FROM `eav_attribute`
    WHERE `attribute_code` LIKE 'status'
) AND `catalog_product_entity_int`.value = 2

How can i modify my code in order to obtain that?

  • 1
    It looks like you have a limitation to 1000 rows in your SQL client set. Check the settings. Try select count(*) from ... insted of listing the fields that should give you the number of products your query would return without limit set. To change/delete the limit check the manual of your SQL client
    – HelgeB
    Feb 3 '19 at 8:07
  • @HelgeB Yeah, you are right!. Thank you, buddy :) Feb 3 '19 at 14:41
  • Dude, how can i know if a product is enabled or disabled? I mean, for example: i want to obtain all the products with "status = disabled". How can i do that? Take a look, i have edited the question. I tried to do that buy i failed :( Feb 3 '19 at 15:28
  • @HelgeB No, it is not working. I was not able to get ALL the products. Take a look: magento.stackexchange.com/questions/260305/… Feb 4 '19 at 2:06

Your Answer

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

Browse other questions tagged or ask your own question.