PHP and MySQL Store Architecture

What We Are Building in Part 1

This chapter builds the local foundation beneath the storefront. Before creating product cards, color selectors, carts, or API synchronization, the site needs a clear place to store and retrieve its product information.

By the end of this chapter, the project will have:

Contents

• The architecture goal
• Separate content, checkout, and fulfillment
• The two-page storefront
• Recommended folder structure
• Plan the database before creating tables
• Create the products table
• Create the variants table
• Store collections and lines
• Create the reviews table
• Add database indexes
• Create the PDO connection
• Keep credentials outside public_html
• Load the catalog
• Load one product safely
• Load product variants
• Build collection navigation
• Escape database output
• Handle missing products
• Allow the schema to evolve
• Part 1 checklist
• Frequently asked questions

The Architecture Goal

The custom store needs to answer a product-page request quickly without asking Shopify or Printify for the same information every time a visitor loads a page.

Shopify and Printify remain connected through stored IDs and synchronization tools, but ordinary page views use the local database.

Separate Content, Checkout, and Fulfillment

The Two-Page Storefront

The customer-facing store can begin with only two important page templates.

Recommended Folder Structure

Plan the Database Before Creating Tables

Start by separating information that belongs to the product from information that belongs to a specific color and size combination.

Storing variants separately prevents the product table from becoming a collection of columns such as black_small_available, black_medium_available, and white_large_available.

Create the Products Table

The product table stores information shared by all variants of one design.

CREATE TABLE shirts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    item_number INT UNSIGNED NOT NULL,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) DEFAULT NULL,

    line VARCHAR(150) DEFAULT NULL,
    category VARCHAR(100) DEFAULT 'T-Shirts',

    short_description VARCHAR(255) DEFAULT NULL,
    description TEXT DEFAULT NULL,

    price DECIMAL(10,2) NOT NULL DEFAULT 0.00,

    active TINYINT(1) NOT NULL DEFAULT 1,

    likes INT UNSIGNED NOT NULL DEFAULT 0,
    buy_click_count INT UNSIGNED NOT NULL DEFAULT 0,

    seo_title VARCHAR(255) DEFAULT NULL,
    meta_description VARCHAR(320) DEFAULT NULL,
    og_title VARCHAR(255) DEFAULT NULL,
    og_description VARCHAR(320) DEFAULT NULL,

    printify_product_id VARCHAR(100) DEFAULT NULL,
    shopify_product_id VARCHAR(100) DEFAULT NULL,

    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL
        DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY uq_shirts_item_number (item_number),
    UNIQUE KEY uq_shirts_slug (slug),
    KEY idx_shirts_active (active),
    KEY idx_shirts_line (line),
    KEY idx_shirts_category (category),
    KEY idx_shirts_created_at (created_at)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

Why keep both id and item_number?

The numeric primary key is efficient for table relationships. The item number is a stable business identifier that can be used in:

• Product URLs
• Image-folder names
• Admin searches
• Print files
• Reports and exports

Create the Variants Table

A product can have several colors, sizes, prices, images, and platform IDs. Each sellable combination becomes one variant row.

CREATE TABLE shirt_variants (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    shirt_id BIGINT UNSIGNED NOT NULL,

    color VARCHAR(100) NOT NULL,
    size VARCHAR(30) NOT NULL,

    price DECIMAL(10,2) NOT NULL DEFAULT 0.00,

    enabled TINYINT(1) NOT NULL DEFAULT 1,
    available TINYINT(1) NOT NULL DEFAULT 1,

    shopify_variant_id VARCHAR(100) DEFAULT NULL,
    printify_variant_id VARCHAR(100) DEFAULT NULL,

    image_url VARCHAR(500) DEFAULT NULL,

    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL
        DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_shirt_variants_shirt
        FOREIGN KEY (shirt_id)
        REFERENCES shirts(id)
        ON DELETE CASCADE,

    UNIQUE KEY uq_shirt_color_size (
        shirt_id,
        color,
        size
    ),

    KEY idx_variants_shirt_id (shirt_id),
    KEY idx_variants_available (available),
    KEY idx_variants_color (color),
    KEY idx_variants_size (size),
    KEY idx_variants_shopify_id (shopify_variant_id),
    KEY idx_variants_printify_id (printify_variant_id)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

Store Collections and Product Lines

The simplest structure stores one collection name directly in the product record:

line VARCHAR(150) DEFAULT NULL

That works well when each product belongs to one primary line, such as:

• The American Slice
• Catastrophe Cats
• Conspiracy Core
• The Cryptids
• Folklore and Superstition

When to use separate collection tables

A many-to-many structure is better when one product must belong to several collections.

CREATE TABLE collections (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    slug VARCHAR(180) NOT NULL,
    description TEXT DEFAULT NULL,
    active TINYINT(1) NOT NULL DEFAULT 1,

    UNIQUE KEY uq_collections_name (name),
    UNIQUE KEY uq_collections_slug (slug)
);

CREATE TABLE shirt_collections (
    shirt_id BIGINT UNSIGNED NOT NULL,
    collection_id BIGINT UNSIGNED NOT NULL,

    PRIMARY KEY (shirt_id, collection_id),

    CONSTRAINT fk_shirt_collections_shirt
        FOREIGN KEY (shirt_id)
        REFERENCES shirts(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_shirt_collections_collection
        FOREIGN KEY (collection_id)
        REFERENCES collections(id)
        ON DELETE CASCADE
);

Create the Reviews Table

CREATE TABLE reviews (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    shirt_id BIGINT UNSIGNED NOT NULL,

    reviewer_name VARCHAR(120) NOT NULL,
    rating TINYINT UNSIGNED NOT NULL,
    review_text TEXT NOT NULL,

    approved TINYINT(1) NOT NULL DEFAULT 0,

    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_reviews_shirt
        FOREIGN KEY (shirt_id)
        REFERENCES shirts(id)
        ON DELETE CASCADE,

    KEY idx_reviews_shirt_approved (
        shirt_id,
        approved
    )
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

Review submission should validate the rating, reviewer name, text length, CSRF token, and rate limits before inserting anything.

Add Database Indexes Deliberately

Indexes help MySQL locate rows without scanning the entire table. They are most useful on columns used frequently in:

• WHERE filters
• JOIN conditions
• ORDER BY clauses
• Unique lookups

Create a Secure PDO Connection

The database connection belongs in a reusable private file.

<?php
/**
 * /private/connection.php
 */

declare(strict_types=1);

$databaseHost = 'localhost';
$databaseName = 'shirt_store';
$databaseUser = 'shirt_store_user';
$databasePass = 'REPLACE_WITH_A_STRONG_PASSWORD';

$dsn = sprintf(
    'mysql:host=%s;dbname=%s;charset=utf8mb4',
    $databaseHost,
    $databaseName
);

$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
    $pdo = new PDO(
        $dsn,
        $databaseUser,
        $databasePass,
        $options
    );
} catch (PDOException $exception) {
    error_log(
        'Database connection failed: ' .
        $exception->getMessage()
    );

    http_response_code(500);

    exit('The store is temporarily unavailable.');
}

Important PDO settings

Keep Credentials Outside Public HTML

The guide pages load the connection file with:

require_once __DIR__ . '/../../private/connection.php';

That path moves upward from the public guide directory and loads a file stored outside the web root.

Load the Product Catalog

The catalog page should request only active products and only the columns it needs.

<?php

$page     = max(1, (int) ($_GET['page'] ?? 1));
$perPage  = 24;
$offset   = ($page - 1) * $perPage;

$stmt = $pdo->prepare("
    SELECT
        id,
        item_number,
        title,
        slug,
        line,
        category,
        short_description,
        price,
        likes,
        created_at
    FROM shirts
    WHERE active = 1
    ORDER BY item_number DESC
    LIMIT :limit
    OFFSET :offset
");

$stmt->bindValue(
    ':limit',
    $perPage,
    PDO::PARAM_INT
);

$stmt->bindValue(
    ':offset',
    $offset,
    PDO::PARAM_INT
);

$stmt->execute();

$shirts = $stmt->fetchAll();

Pagination prevents the page from loading the entire catalog at once.

Load One Product Safely

The public product page receives an item number:

/shirt.php?item=1001

Validate it before querying the database.

<?php

$itemNumber = filter_input(
    INPUT_GET,
    'item',
    FILTER_VALIDATE_INT,
    [
        'options' => [
            'min_range' => 1,
        ],
    ]
);

if ($itemNumber === false || $itemNumber === null) {
    http_response_code(404);
    exit('Product not found.');
}

$stmt = $pdo->prepare("
    SELECT
        *
    FROM shirts
    WHERE item_number = :item_number
      AND active = 1
    LIMIT 1
");

$stmt->execute([
    ':item_number' => $itemNumber,
]);

$shirt = $stmt->fetch();

if (!$shirt) {
    http_response_code(404);
    exit('Product not found.');
}

Load Available Product Variants

<?php

$variantStmt = $pdo->prepare("
    SELECT
        id,
        color,
        size,
        price,
        shopify_variant_id,
        printify_variant_id,
        image_url
    FROM shirt_variants
    WHERE shirt_id = :shirt_id
      AND enabled = 1
      AND available = 1
    ORDER BY
        color ASC,
        FIELD(
            size,
            'XS',
            'S',
            'M',
            'L',
            'XL',
            '2XL',
            '3XL'
        )
");

$variantStmt->execute([
    ':shirt_id' => $shirt['id'],
]);

$variants = $variantStmt->fetchAll();

Later, the variants can be passed to JavaScript:

<script>
const variants = <?= json_encode(
    $variants,
    JSON_UNESCAPED_SLASHES |
    JSON_UNESCAPED_UNICODE
) ?>;
</script>

Build Collection Navigation from the Database

The collection navigation can update automatically as active products are added.

<?php

$allLines = $pdo->query("
    SELECT
        TRIM(line) AS line,
        COUNT(*) AS cnt
    FROM shirts
    WHERE active = 1
      AND line IS NOT NULL
      AND TRIM(line) != ''
    GROUP BY TRIM(line)
    ORDER BY MAX(item_number) DESC
")->fetchAll(PDO::FETCH_ASSOC);

Then render the links:

<?php foreach ($allLines as $line): ?>
    <a href="/index.php?line=<?= urlencode($line['line']) ?>">
        <?= htmlspecialchars(
            $line['line'],
            ENT_QUOTES,
            'UTF-8'
        ) ?>

        (<?= (int) $line['cnt'] ?>)
    </a>
<?php endforeach; ?>

Escape Database Output

Prepared statements protect database queries. They do not make database values safe to print inside HTML.

<h1>
    <?= htmlspecialchars(
        $shirt['title'],
        ENT_QUOTES,
        'UTF-8'
    ) ?>
</h1>

Handle Missing Products Cleanly

A missing or inactive product should return a real HTTP 404 status, not an empty product template with a successful status.

if (!$shirt) {
    http_response_code(404);

    $pageTitle = 'Product Not Found | The Conspiracy Shirt Company';

    include __DIR__ . '/includes/404-product.php';

    exit;
}

A styled product-not-found page can still include:

• A link back to the catalog
• Current collections
• Popular products
• A site search

Allow the Database Schema to Evolve

The first schema will not contain every field the store eventually needs. New requirements appear as the catalog and workflows grow.

Part 1 Architecture Checklist

• The public and private directories are separated.
• Database credentials are outside the public web root.
• The connection uses UTF-8 with utf8mb4.
• PDO throws exceptions on database errors.
• Native prepared statements are enabled.
• Products and variants use separate tables.
• Every variant connects to a parent product.
• Item numbers are unique.
• Product slugs are unique when used.
• Shopify and Printify IDs have dedicated fields.
• Active products can be filtered efficiently.
• Collection fields are indexed.
• Variant availability is indexed.
• Catalog queries use pagination.
• Product URLs validate the item number.
• Missing products return a 404 status.
• SQL values use prepared parameters.
• HTML output uses context-appropriate escaping.
• The catalog page and product template are reusable.
• The schema can be extended without rebuilding the storefront.

PHP and MySQL Store Architecture FAQ

Related Guides and Tools