Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New Coder
    Join Date
    Aug 2010
    Posts
    24
    Thanks
    5
    Thanked 0 Times in 0 Posts

    reading back from dynamically generated uri

    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:

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

    Code:
    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:

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

    Code:
    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

  • #2
    Senior Coder
    Join Date
    Jul 2009
    Location
    South Yorkshire, England
    Posts
    2,318
    Thanks
    6
    Thanked 304 Times in 303 Posts
    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.

  • #3
    New Coder
    Join Date
    Aug 2010
    Posts
    24
    Thanks
    5
    Thanked 0 Times in 0 Posts
    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

  • #4
    Senior Coder
    Join Date
    Jul 2009
    Location
    South Yorkshire, England
    Posts
    2,318
    Thanks
    6
    Thanked 304 Times in 303 Posts
    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.
    Last edited by MattF; 11-23-2010 at 01:47 AM.

  • #5
    New Coder
    Join Date
    Aug 2010
    Posts
    24
    Thanks
    5
    Thanked 0 Times in 0 Posts
    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...

  • #6
    Senior Coder
    Join Date
    Jul 2009
    Location
    South Yorkshire, England
    Posts
    2,318
    Thanks
    6
    Thanked 304 Times in 303 Posts
    Quote Originally Posted by big-momo View Post
    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 ). 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.

  • #7
    New Coder
    Join Date
    Aug 2010
    Posts
    24
    Thanks
    5
    Thanked 0 Times in 0 Posts
    cool - thanks


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •