Hjdcti

Anatomically Correct Mesopelagic Aves

Customer Requests (Sometimes) Drive Me Bonkers!

Opposite of a diet

Why doesn't a table tennis ball float on the surface? How do we calculate buoyancy here?

Why did we only see the N-1 starfighters in one film?

Should I tutor a student who I know has cheated on their homework?

Return the Closest Prime Number

How to write papers efficiently when English isn't my first language?

Would this house-rule that treats advantage as a +1 to the roll instead (and disadvantage as -1) and allows them to stack be balanced?

Why here is plural "We went to the movies last night."

Are there languages with no euphemisms?

Go Pregnant or Go Home

When airplanes disconnect from a tanker during air to air refueling, why do they bank so sharply to the right?

Is HostGator storing my password in plaintext?

Why does C# sound extremely flat when saxophone is tuned to G?

Rotate a column

What can we do to stop prior company from asking us questions?

How do I construct this japanese bowl?

Implement the Thanos sorting algorithm

How can I get through very long and very dry, but also very useful technical documents when learning a new tool?

How long to clear the 'suck zone' of a turbofan after start is initiated?

How to make a software documentation "officially" citable?

How do I solve this limit?

How can I open an app using Terminal?

How to tell which products are not catgorized via phpmyadmin

0

1

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?

product category

share|improve this question

edited 31 mins ago

Teja Bhagavan Kollepara

3,01241949

asked Oct 9 '13 at 16:37

Christina RuleChristina Rule

59631328

add a comment | 

0

1

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?

product category

share|improve this question

edited 31 mins ago

Teja Bhagavan Kollepara

3,01241949

asked Oct 9 '13 at 16:37

Christina RuleChristina Rule

59631328

add a comment | 

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?

product category

share|improve this question

edited 31 mins ago

Teja Bhagavan Kollepara

3,01241949

asked Oct 9 '13 at 16:37

Christina RuleChristina Rule

59631328

share|improve this question

edited 31 mins ago

Teja Bhagavan Kollepara

3,01241949

asked Oct 9 '13 at 16:37

Christina RuleChristina Rule

59631328

share|improve this question

edited 31 mins ago

Teja Bhagavan Kollepara

3,01241949

asked Oct 9 '13 at 16:37

Christina RuleChristina Rule

59631328

edited 31 mins ago

Teja Bhagavan Kollepara

3,01241949

edited 31 mins ago

Teja Bhagavan Kollepara

3,01241949

asked Oct 9 '13 at 16:37

Christina RuleChristina Rule

59631328

asked Oct 9 '13 at 16:37

Christina RuleChristina Rule

59631328

1 Answer
1

active

oldest

votes

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

share|improve this answer

edited Oct 9 '13 at 16:59

answered Oct 9 '13 at 16:46

Alexey ShchurAlexey Shchur

548313

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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?
  
  – Christina Rule
  Oct 9 '13 at 16:48
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Check out my updated answer. This is real magento-sql-geek stuff :P
  
  – Alexey Shchur
  Oct 9 '13 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?
  
  – Christina Rule
  Oct 9 '13 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
  
  – Alexey Shchur
  Oct 9 '13 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'));
  
  – Christina Rule
  Oct 9 '13 at 18:00
  

  
  
  
  

 | 
show 1 more comment

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "479"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fmagento.stackexchange.com%2fquestions%2f9185%2fhow-to-tell-which-products-are-not-catgorized-via-phpmyadmin%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

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

share|improve this answer

edited Oct 9 '13 at 16:59

answered Oct 9 '13 at 16:46

Alexey ShchurAlexey Shchur

548313

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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?
  
  – Christina Rule
  Oct 9 '13 at 16:48
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Check out my updated answer. This is real magento-sql-geek stuff :P
  
  – Alexey Shchur
  Oct 9 '13 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?
  
  – Christina Rule
  Oct 9 '13 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
  
  – Alexey Shchur
  Oct 9 '13 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'));
  
  – Christina Rule
  Oct 9 '13 at 18:00
  

  
  
  
  

 | 
show 1 more comment

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

share|improve this answer

edited Oct 9 '13 at 16:59

answered Oct 9 '13 at 16:46

Alexey ShchurAlexey Shchur

548313

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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?
  
  – Christina Rule
  Oct 9 '13 at 16:48
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Check out my updated answer. This is real magento-sql-geek stuff :P
  
  – Alexey Shchur
  Oct 9 '13 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?
  
  – Christina Rule
  Oct 9 '13 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
  
  – Alexey Shchur
  Oct 9 '13 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'));
  
  – Christina Rule
  Oct 9 '13 at 18:00
  

  
  
  
  

 | 
show 1 more comment

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

share|improve this answer

edited Oct 9 '13 at 16:59

answered Oct 9 '13 at 16:46

Alexey ShchurAlexey Shchur

548313

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

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

share|improve this answer

edited Oct 9 '13 at 16:59

answered Oct 9 '13 at 16:46

Alexey ShchurAlexey Shchur

548313

answered Oct 9 '13 at 16:46

Alexey ShchurAlexey Shchur

548313

answered Oct 9 '13 at 16:46

Alexey ShchurAlexey Shchur

548313