View Full Version : reading back from dynamically generated uri

11-23-2010, 02:21 AM
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);
case 1 :
// this is the only one with that label, so return the ID and be done
case 0 :
// no labels match, so return a 404 or missing item or something and be done
default :
// if there are more than 1 matching labels, figure out which one - here is where i get lost on this approach...

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...


11-23-2010, 02:29 AM
the problem arises when trying to go backwards...

Place the parent id in the URI query string, or recurse back using the item id rather than title/description. Id's are unique, (or should be). User entered descriptions are not, hence why id's exist. The id and the id alone should be the deciding factor in your DB query.

11-23-2010, 02:32 AM
thanks for the quick reply - i'd love to use the id, but can't - i'm being fed 'pretty urls' that i need to then decipher and determine the appropriate id from it... event/past-events/gallery versus about/out-team/gallery

11-23-2010, 02:45 AM
You have no control over the URI request structure? Unless you can plonk the id in there for reference, you're going to be fighting a losing battle. Simple as. Descriptions/labels will match at some point. There is only one unique you can use. The id. If you cannot incorporate that, what you have at the moment is about the best you'll achieve.

11-23-2010, 02:50 AM
nope, no control - i'm being given (for example) 'events/past-event/gallery' or 'about/our-team/gallery' and have to figure out which row is being referenced...

11-23-2010, 03:46 AM
nope, no control - i'm being given (for example) 'events/past-event/gallery' or 'about/our-team/gallery' and have to figure out which row is being referenced...

You may just have enough info there to work with, (I know where I'd tell the geniuses in charge to shove that one personally though :D). Best thing to do is to pop a post in the SQL forum along with the table structures and such and see if one of the SQL guru's can help. It's more a SQL than a PHP question.

11-23-2010, 04:09 AM
cool - thanks