DEV Community: Classifieds24 Network

Category Trees in MySQL: Adjacency List vs Closure Table for a Classifieds Site

Classifieds24 Network — Tue, 27 Oct 2026 12:30:11 +0000

Classifieds sites live and die by their categories: "For Sale > Electronics > Phones" needs to load fast, show counts, and support breadcrumbs. I've used three approaches over the years. Here's where each one fits.

Adjacency list: simple, but recursive

CREATE TABLE categories (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  parent_id INT UNSIGNED NULL,
  name VARCHAR(100) NOT NULL,
  slug VARCHAR(100) NOT NULL,
  KEY (parent_id)
);

Easy to insert and move. On MySQL 8 you can fetch a subtree with a recursive CTE:

WITH RECURSIVE sub AS (
  SELECT id FROM categories WHERE id = ?
  UNION ALL
  SELECT c.id FROM categories c JOIN sub ON c.parent_id = sub.id
)
SELECT id FROM sub;

On MySQL 5.7 or older you're stuck with loops in PHP or a fixed number of self-joins.

Closure table: fast reads, a bit more bookkeeping

Store every ancestor/descendant pair:

CREATE TABLE category_paths (
  ancestor INT UNSIGNED NOT NULL,
  descendant INT UNSIGNED NOT NULL,
  depth TINYINT UNSIGNED NOT NULL,
  PRIMARY KEY (ancestor, descendant),
  KEY (descendant)
);

All listings under "Electronics", including subcategories, become one indexed join:

SELECT l.*
FROM listings l
JOIN category_paths p ON p.descendant = l.category_id
WHERE p.ancestor = ? AND l.status = 'active'
ORDER BY l.created_at DESC
LIMIT 30;

Breadcrumbs are just as easy: select ancestors of the current category ordered by depth descending.

Inserting a new category means copying the parent's ancestor rows:

INSERT INTO category_paths (ancestor, descendant, depth)
SELECT ancestor, :new_id, depth + 1 FROM category_paths WHERE descendant = :parent_id
UNION ALL SELECT :new_id, :new_id, 0;

Nested sets: skip it for classifieds

Nested sets are fast to read but painful to write, and admins reorganize categories more often than you'd think. I wouldn't use them here.

Counts without counting

Don't COUNT(*) on every page view. Keep a listing_count column per category, updated by a nightly job or on listing status changes, and roll it up through the closure table.

My pick

Adjacency list for the source of truth, a closure table for reads, both maintained in the same transaction. Category pages stay at one indexed query, and moving a branch is still manageable.

These patterns power the category pages across our network: Classifieds24.net, Classifieds24.info, Classifieds24.org, GlobalClassifieds.info, GlobalClassifieds.org, HirePros.pro, Realtor.pm and LinkMarket.app.

Running a Network of Classifieds Sites From One PHP Codebase

Classifieds24 Network — Tue, 27 Oct 2026 12:29:09 +0000

I run a handful of classifieds and listing sites: local classifieds, a global version, a services directory, a property site and a marketplace for websites. They look different and target different audiences, but they share one PHP codebase and one database cluster. Here's how that's structured and what I'd do the same way again.

Resolve the site from the host name

Every request starts by working out which site it belongs to. A small sites table holds the per-site config:

CREATE TABLE sites (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  host VARCHAR(190) NOT NULL UNIQUE,
  name VARCHAR(100) NOT NULL,
  theme VARCHAR(50) NOT NULL,
  default_country CHAR(2) NULL,
  root_category_id INT UNSIGNED NULL
);

<?php
$host = strtolower(preg_replace('/^www\./', '', $_SERVER['HTTP_HOST'] ?? ''));
$stmt = $pdo->prepare('SELECT * FROM sites WHERE host = ?');
$stmt->execute([$host]);
$site = $stmt->fetch(PDO::FETCH_ASSOC);
if (!$site) {
    http_response_code(404);
    exit('Unknown site');
}
define('SITE_ID', (int) $site['id']);

Cache that lookup (APCu works well) so it isn't a query on every hit.

Scope data with a site column, not separate databases

Listings carry a site_id, plus an optional many-to-many table for listings that should also appear on sister sites. One schema means one migration path, and cross-posting a listing is just an extra row:

CREATE TABLE listing_sites (
  listing_id INT UNSIGNED NOT NULL,
  site_id INT UNSIGNED NOT NULL,
  PRIMARY KEY (listing_id, site_id),
  KEY (site_id)
);

Every listing query joins through listing_sites, so a site only ever sees what it's allowed to show.

Themes as thin template sets

Each site has its own templates folder that overrides a shared base. The lookup falls back to base/ when a site doesn't override a file, so most sites only customize the header, footer and home page.

Per-site SEO, shared logic

Titles, meta descriptions and canonical URLs come from the site config. When a listing appears on more than one site, the canonical points to the site where it was originally posted, which avoids competing with yourself in search.

What I'd watch out for

Never trust the Host header blindly. Only accept hosts that exist in sites.
Keep sessions per host. Separate cookie domains stop a login on one site leaking to another unintentionally.
Log the site ID with every error. "Search is broken" means nothing until you know which site.

One codebase has kept maintenance sane: a bug fixed once is fixed everywhere.

I build and run these sites, and listings can appear across the whole network: Classifieds24.net, Classifieds24.info, Classifieds24.org, GlobalClassifieds.info, GlobalClassifieds.org, HirePros.pro, Realtor.pm and LinkMarket.app.

A Developer Portfolio That Gets Replies: A Practical Checklist

Classifieds24 Network — Tue, 27 Oct 2026 09:14:01 +0000

Most developer portfolios fail the same way: a grid of project cards, each with a screenshot, a tech-stack badge row and a "View Code" link. Recruiters and hiring managers skim them in under a minute and move on. Below is a checklist I'd use to turn a portfolio from a gallery into evidence.

1. Lead with the problem, not the stack

"React + Node + MongoDB" tells a reader nothing about your judgment. Compare:

Inventory sync for a small print shop. Their catalog lived in three spreadsheets. I built a PHP/MySQL import that reconciles SKUs nightly and flags conflicts, cutting manual fixes from hours to minutes.

One paragraph, and the reader knows the domain, the constraint and the outcome. Put the stack underneath.

2. Show three projects, not twelve

Pick the three that best show range: one with real users or data, one that shows depth (performance, security, tricky state), and one that shows you can finish and polish. Everything else goes in a "more work" list or stays on GitHub.

3. Write a short case study for each

A good case study fits on one screen:

Context: who it was for and what was broken
Constraints: budget, legacy code, deadlines, hosting limits
Decisions: two or three choices you made and why
Result: a number if you have one, a sentence if you don't
What you'd change: this is the part reviewers remember

4. Make it fast and mobile-first

Your portfolio is itself a project. Check it on a phone. Run Lighthouse. A reviewer opening a 6 MB hero video on hotel Wi-Fi will not wait.

npx lighthouse https://your-portfolio.example --only-categories=performance,accessibility --view

Aim for real wins: compressed images (webp/avif), no layout shift, readable font sizes, tap targets that don't overlap.

5. Fix the HTML basics

One <h1> per page (your name or the project title), then <h2>/<h3> in order
A unique <title> and meta description for each case study
Alt text that describes screenshots
Open Graph tags so links look right when shared

These are small, but they signal that you care about the details a team cares about.

6. Link to live demos that actually work

Dead demos are worse than no demos. If a project needs a backend you no longer pay for, record a 60-second screen capture instead and say so.

7. Make contact effortless

An email address or a short form, visible on every page. If you're open to specific roles or freelance work, say which.

8. Get it in front of people

A portfolio nobody sees doesn't help. Share case studies as posts, join developer communities and link your portfolio from your profiles.

If you want to see how other developers lay out case-study-style portfolios, the DevSocial portfolio showcase has examples, and DevSocial is a developer community where you can share yours and get feedback.

Quick checklist

[ ] Three projects, each with a one-screen case study
[ ] Problem and result before stack
[ ] Lighthouse performance and accessibility checked
[ ] Single h1, ordered headings, unique titles
[ ] Every demo link works (or has a video)
[ ] Contact info on every page

What's the one thing on your portfolio you'd cut after reading this? I'd love to hear in the comments.