How to tell which products are not catgorized via phpmyadmin
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?
product category
edited 11 mins ago
Teja Bhagavan Kollepara
3,01241949
asked Oct 9 '13 at 16:37
Christina RuleChristina Rule
59631328
add a comment |
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?
product category
edited 11 mins ago
Teja Bhagavan Kollepara
3,01241949
asked Oct 9 '13 at 16:37
Christina RuleChristina Rule
59631328
add a comment |
0
0
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
edited 11 mins ago
Teja Bhagavan Kollepara
3,01241949
asked Oct 9 '13 at 16:37
Christina RuleChristina Rule
59631328
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
product category
edited 11 mins ago
Teja Bhagavan Kollepara
3,01241949
asked Oct 9 '13 at 16:37
Christina RuleChristina Rule
59631328
edited 11 mins ago
Teja Bhagavan Kollepara
3,01241949
asked Oct 9 '13 at 16:37
Christina RuleChristina Rule
59631328
edited 11 mins ago
Teja Bhagavan Kollepara
3,01241949
edited 11 mins ago
Teja Bhagavan Kollepara
3,01241949
edited 11 mins ago
Teja Bhagavan Kollepara
3,01241949
3,01241949
asked Oct 9 '13 at 16:37
Christina RuleChristina Rule
59631328
asked Oct 9 '13 at 16:37
Christina RuleChristina Rule
59631328
asked Oct 9 '13 at 16:37
Christina RuleChristina Rule
59631328
59631328
add a comment |
add a comment |
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
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 FROMeav_attributeINNER JOINeav_entity_typeON 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
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
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
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
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 FROMeav_attributeINNER JOINeav_entity_typeON 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
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 FROMeav_attributeINNER JOINeav_entity_typeON 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
3
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
answered Oct 9 '13 at 16:46
Alexey ShchurAlexey Shchur
548313
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
answered Oct 9 '13 at 16:46
Alexey ShchurAlexey Shchur
548313
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
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 FROMeav_attributeINNER JOINeav_entity_typeON 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
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 FROMeav_attributeINNER JOINeav_entity_typeON 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
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
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
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
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
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
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
draft saved
draft discarded
Thanks for contributing an answer to Magento Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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
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
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
