adjacency list > load by path

load.php

<?php
/** @var string $path The path, for example 'services/webdevelopment/wordpress' **/

// all page rows are expected to have a slug value.
$path_parts = preg_split('@/+@', trim($path, '/'));

$joins = array();
$where = array();
$select = array(sprintf('a%d.*', count($path_parts)));

$tmp_slug_cols = array("'/'");

$params = array();

foreach ($path_parts as $i => $slug) {
    $level = $i + 1;
    $where[] = "a$level.active AND a$level.slug = ?";
    $params[] = $slug;

    $tmp_slug_cols[] = "a$level.slug";

    if (!$i) {
        $joins[] = "FROM page a$level";
        $where[] = "IFNULL(a$level, 0) = 0";
    } else {
        $joins[] = "INNER JOIN page a$level ON (a$level.parent_id = a$i.id)";
    }
}

$select[] = sprintf('CONCAT_WS(%s) as path', implode(', ', $tmp_slug_cols));

$sql = strtr(
    "SELECT %select% %joins% %where%\nLIMIT 1",
    array(
        '%select%' => "\n\t" . implode(",\n\t", $select),
        '%joins%' => "\n" . implode("\n\t", $joins),
        '%where%' => "\nWHERE 1=1\n\tAND " . implode("\n\tAND ", $where)
    )
);

if ($page = $db->fetchAssoc($sql, $params)) {
    return $page;
}