Magento SQL query: Get all simple products that are "not visible individually"

Magento SQL query: Get all simple products that are "not visible individually"

July 7, 2026 · Illustrated author avatarBy Stefan Bogdanescu
Magento SQL query: Get all simple products that are "not visible individually"

Retrieving “Not Visible Individually” Simple Products from Magento Database

As a senior developer specializing in e-commerce platforms like Magento, I’ve encountered this query many times while optimizing product visibility for clients. Let’s dive into how to retrieve all simple products that are marked as “not visible individually”.

Understanding the Magento Product Visibility Architecture

In Magento, product visibility is controlled by an integer value stored in the catalog_product_entity_int table under the attribute ID associated with visibility. The possible values and their meanings are:

  • 1: Not Visible Individually (the default for simple products)
  • 2: Catalog, Search
  • 3: In Search
  • 4: In Category, Search
  • 5: In Category

Writing the SQL Query to Fetch Relevant Simple Products

To fetch all simple products with a visibility value of “1” (not visible individually), you can use the following MySQL query:

SELECT *
FROM catalog_product_entity AS ppe
INNER JOIN catalog_product_entity_int AS pei ON ppe.entity_id = pei.entity_id
WHERE pei.attribute_id IN (42, 43)
AND pei.value = 1;

Let’s break down this query:

  • We select all columns from the catalog_product_entity table (ppe). This is where Magento stores basic product information like SKU, name, etc.
  • We join it with the catalog_product_entity_int table (pei) on the common entity_id field. The catalog_product_entity_int table holds the attribute values for each product.
  • In the WHERE clause:
    • We use attribute_id IN (42, 43) to filter products where either of these two attributes is set. Attribute IDs are specific to your Magento installation; you can find them in the eav_attribute table.
      • 42 usually represents the ‘visibility’ attribute.
      • 43 often corresponds to the ‘status’ attribute, which we include to ensure only enabled products are fetched.
    • We set pei.value = 1 to filter for products that aren’t visible individually.

Executing the Query and Analyzing Results

You can execute this query directly in your MySQL client or via a script using a library like PDO in PHP:

<?php
$host = 'localhost';
$db   = 'your_magento_db_name';
$user = 'your_username';
$pass = 'your_password';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$opt = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
    $pdo = new PDO($dsn, $user, $pass, $opt);
} catch (PDOException $e) {
    throw new PDOException($e->getMessage(), (int)$e->getCode());
}

// Your query here
$sql = "SELECT * FROM catalog_product_entity AS ppe INNER JOIN catalog_product_entity_int AS pei ON ppe.entity_id = pei.entity_id WHERE pei.attribute_id IN (42, 43) AND pei.value = 1";
$stmt = $pdo->query($sql);

while ($row = $stmt->fetch()) {
    echo "Product ID: {$row['entity_id']}, SKU: {$row['sku']}\n";
}
?>

This script fetches the product’s entity_id and sku, outputting them for each matched record.

Conclusion

Retrieving “not visible individually” simple products from your Magento database involves understanding the eav (easy attribute) architecture and writing an appropriate JOIN query. Always remember to filter by both visibility and status attributes if you want only enabled, invisible simple products.

Remember to double-check the specific attribute_id values for ‘visibility’ in your installation, as they can vary between versions or through customizations.

For more advanced Magento SQL query techniques, I recommend checking out our comprehensive guide on Magento database optimization. It provides best practices for managing and querying Magento’s complex relational data structures.

Catalog Database Performance CLI

Our network