0

I'm trying to pull a list of all products that are not in categories via php-myadmin, is this possible? What is the best way to go about doing this?

1 Answer 1

3

This SQL query will give you a list of all "uncategorized" skus:

select sku 
from catalog_product_entity as e 
left join catalog_category_product as cat 
    on e.entity_id = cat.product_id where  category_id is null

EDIT: If you want to fetch more data, you need to pick it up from proper EAV tables, here is a basic sample demonstrating the principle:

set @name_attribute_id =  (SELECT attribute_id FROM  `eav_attribute` INNER JOIN `eav_entity_type` ON entity_type_code = 'catalog_product' AND  eav_entity_type.entity_type_id = `eav_attribute`.entity_type_id where attribute_code in ('name'));

select sku, created_at, updated_at, name_table.value as product_name
from catalog_product_entity as e 
left join catalog_category_product as cat 
    on e.entity_id = cat.product_id 
left join catalog_product_entity_varchar as name_table 
    on e.entity_id = name_table.entity_id and name_table.attribute_id = @name_attribute_id
where category_id is null
6
  • thanks so much! This indeed worked great! If I also wanted to pull up the products name, date created, and date of last updated what would I have to add? Oct 9, 2013 at 16:48
  • Check out my updated answer. This is real magento-sql-geek stuff :P Oct 9, 2013 at 17:01
  • you are amazing! Thank you! Excuse my "n00bness" but when I entered in your entire snippet nothing was happening. When I entered in the code starting at "select sku" (from the second code snippet you pasted) it brought up a list of everything correctly except for the product names come up "NULL". Am I doing something wrong? Oct 9, 2013 at 17:12
  • Looks like your phpMyAdmin configuration doesn't allow multiple queries per request so we can't make use of variable \@name_attribute_id. A workaround is: 1) run first query without "set \@name_attribute_id =", just select 2) figure what your "name" attribute id 3) run your second query with substituting @name_attribute_id by the real attribute_id Oct 9, 2013 at 17:31
  • Thanks so if my name attribute was 71 I would run something like: set 71 = (SELECT attribute_id FROM eav_attribute INNER JOIN eav_entity_type ON entity_type_code = 'catalog_product' AND eav_entity_type.entity_type_id = eav_attribute.entity_type_id where attribute_code in ('name')); Oct 9, 2013 at 18:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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