How to query magento database tables to show categories and products?

How to query magento database tables to show categories and products?

September 13, 2026 · Illustrated author avatarBy Stefan Bogdanescu
How to query magento database tables to show categories and products?

Integrating Magento Categories and Products into an Existing PHP Website

You’re on the right track by considering Magento for your e-commerce needs while keeping your existing PHP site intact. In this post, we’ll guide you through the process of querying the Magento database to display categories and products directly in your website’s homepage, even though it lies outside the Magento installation.

Understanding Magento Database Schema

Magento employs a complex relational database schema with numerous tables. The key tables relevant for our purposes are:

  • catalog_category_entity: Stores information about all product categories.
  • catalog_product_entity: Contains basic details about each product.
  • catalog_product_entity_varchar: Stores additional text data associated with products.

Querying the Magento Database

To fetch category and product data, you’ll need to establish a database connection from your PHP site to the Magento installation. Here’s a high-level outline of the process:

  1. Establish the database connection: You can use the PDO library in PHP to connect to the MySQL database where Magento is installed.

  2. Fetch category data: Write a SQL query to retrieve all categories from catalog_category_entity. The query might look something like this:

$stmt = $pdo->prepare("SELECT entity_id, name FROM catalog_category_entity");
$stmt->execute();
$categories = $stmt->fetchAll(PDO::FETCH_ASSOC);
  1. Fetch product data: To get basic product information, you can query catalog_product_entity:
$stmt = $pdo->prepare("SELECT entity_id, name, price FROM catalog_product_entity");
$stmt->execute();
$products = $stmt->fetchAll(PDO::FETCH_ASSOC);
  1. Fetch additional product data: For text attributes like description or short description, you’ll need to query catalog_product_entity_varchar:
$stmt = $pdo->prepare("SELECT entity_id, attribute_id FROM catalog_product_entity_varchar");
$stmt->execute();
$productAttributes = $stmt->fetchAll(PDO::FETCH_ASSOC);

Best Practices for Database Access

While retrieving data directly from the Magento database is viable, it’s not recommended as a long-term solution:

  • Performance: Heavy queries can significantly impact your Magento site’s performance.
  • Security: Exposing direct access to the database poses potential security risks.

A better approach would be to use Magento’s API or create custom modules for data exchange. However, these require more advanced development skills and deeper understanding of Magento architecture.

Conclusion

In this post, we’ve demonstrated how to query the Magento database directly from an external PHP site to fetch categories and products. Remember that while this method works, it’s not recommended for production-level sites due to performance and security concerns.

For a robust integration, consider exploring the following options:

  • Using Magento API: Magento provides a REST API for interacting with its data. You can use cURL or Guzzle in PHP to make API requests.
  • Creating custom modules: If you need tight coupling between your site and Magento, develop a custom Magento module that communicates with your existing PHP codebase.

At LaravelCompany, we specialize in Laravel development services. Our team can help you design an efficient e-commerce solution tailored to your business needs while ensuring optimal performance and security.

Catalog Database

Our network