reading back from dynamically generated uri

[moagrius]

what follows is a simplified version of what i'm attempting to do.

 

say i have a mysql table called 'pages', that looks roughly like this:

 

id	parent	label
1	0	home
2	0	about
3	0	events
4	2	history
5	2	philosophy
6	3	past-events
7	3	future-events
8	6	past-event-gallery

 

which uses the parent key as a self-referencing index.  items with parent=0 don't have a parent - all other items refer to rows in the same table.  so, 'about' has 2 children: 'history' and 'philosophy'.

 

this can extend an unlimited number of levels in depth.  e.g., 'past-event-gallery' has a parent of 'past-events' which has a parent of 'events'.

 

building it out is pretty straightforward - start with all rows that have a parent of 0, then recurse...

 

select id, label from pages where parent=0
// grab the id...
select id, label from pages where where parent={$id}

 

etc. which works (for example) to build out a uri for an <a> tag's href attribute.

 

the problem arises when trying to go backwards...

 

i'm trying to get back the id of the row from that example uri...

 

so if the output was 'events/past-events/past-event-gallery', i'm exploding on slashes to get the component parts, and want to walk it back to get the id of the row.

 

if the label keys were unique, it'd be simple enough...  select id from pages where label={$label}

 

but labels might be duplicated.  for example, 'past-events' might have a child called 'gallery', but it might be possible that 'about' also has a child called 'gallery', etc.  it might even occur several levels deep, so i need to walk it backwards until i've determined the correct id from the component parts of the URI.

 

my initial thought was to run from left-to-right, something like:

 

while(count($parts) > 0){
$component = array_shift($parts);
$result = mysql_query("select id from pages where label='{$component}'");
// this is where i lose it...  maybe create a temp table from the results and continue...?
}

 

or maybe from right-to-left...

 

while(count($parts) > 0){
$component = array_pop($parts);
$result = mysql_query("select id from pages where label='{$component}'");
$row_count = mysql_num_rows($result);
switch($row_count){
	case 1 :
		// this is the only one with that label, so return the ID and be done
		break;
	case 0 : 
		// no labels match, so return a 404 or missing item or something and be done
		break;
	default :
		// if there are more than 1 matching labels, figure out which one - here is where i get lost on this approach...
		break;
}
}

 

also considered a self-returning function for the second (right-to-left) idea, but didn't get far with it.

 

any ideas?  i could be (probably am) totally off on both approaches mentioned, and there might be a much easier way to do this.  i'd be happy to hear any suggestions...

 

tyia