I just came across a different method. With this example URL:
/Page1/SubPage1/SubSubPage1
You can access it via one query like so:
SELECT p1.id
FROM pages AS p1, pages AS p2, pages AS p3
WHERE p1.PageName = ‘SubSubPage1’ AND p1.parentID = p2.ID AND
p2.PageName = ‘SubPage1’ AND p2.parentID = p3.ID AND
p3.PageName = ‘Page1’
So here is the code I ended up using (where $url is equal to the url segment after the domain name):
// Get segments in reverse order
$parts = array_reverse( explode('/', $url) );
$from = $where = '';
for($i=1; $i <= count($parts); $i++){
if( $i!=1 ){
$from.= ', ';
$where.= ' AND ';
}
$from.= 'pages as p'.$i;
$where.= 'p'.$i.'.slug = ?';
if( $i < count($parts) ) $where.= ' AND p'.$i.'.parent_id = p'.($i+1).'.id';
else $where.= ' AND p'.$i.'.parent_id = 0';
}
// GET PAGE FROM DB
$q = $this->db->query('SELECT p1.* FROM '.$from.' WHERE '.$where, $parts);
I’m using CodeIgniter so in the query the $parts array values replace all the ‘?’ symbols (for those that were wondering).
This seems to be efficient enough to get it all in one query. I like that. Just a note but by doing it this why, you’d want to put an index on that slug field. That’s an area that could be slowed down in that query.