Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 22
  1. #1
    Senior Member Steax's Avatar
    Join Date
    Dec 2006
    Location
    Bandung, Indonesia
    Posts
    1,207
    Member #
    14572
    Hi everyone. I need to create a structured system that allows for an infinite depth hierarchy inside a single database table. I'm working on a simple CMS. A page can have any other page as a parent. In the site, a page is only shown on its parent page. But for the admin system, I need to display the entire hierarchy. Now, I'm confused on how to make this work.

    I made a `pages` table with a `parent` field. That field must point to the `id` of another page, or blank to mean a global page. I need to grab pages in the correct order.

    My first idea was to simply get the entire table into PHP, and try to arrange it from there. But how? Because I don't know how deep the hierarchy can get, I figured it might have to do with arrays then... any help would be deeply appreciated.
    Note on code: If I give code, please note that it is simply sample code to demonstrate an effect. It is not meant to be used as-is; that is the programmer's job. I am not responsible to give you support or be held liable for anything that happens when using my code.

  2.  

  3. #2
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    An example of what your table could look like:

    Code:
    CREATE TABLE pages (
    	id int UNSIGNED NOT NULL AUTO_INCREMENT,
    	parent_id int UNSIGNED NULL,
    	page_type_id int UNSIGNED NULL,
    	name varchar(255) NULL,
    	title varchar(255) NULL,
    	body text NOT NULL,
    	status TINYINT UNSIGNED NOT NULL DEFAULT 1,
    	PRIMARY KEY (id),
    	UNIQUE (name),
    	FOREIGN KEY (parent_id) REFERENCES pages(id) ON UPDATE CASCADE ON DELETE CASCADE,
    	FOREIGN KEY (page_type_id) REFERENCES page_types(id) ON UPDATE CASCADE ON DELETE SET NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;
    The InnoDB engine allows you to enforce FKs and also lets you define triggers for them (i.e. cascading delete) which is good for what you're doing. It's a little slower than MyISAM but that shouldn't be a problem for a CMS.

    The page_types table could allow you to display different pages differently based upon type.

    Your PHP would then contain a recursive architecture for display: Here's what it could look like:
    PHP Code:
    function displayPages($parentId=0$level=0) {
        
    $sql "SELECT * FROM pages WHERE parent_id " . ($parentId == " IS NULL" " = $parentId");
        
    //execute your query and return an associative array of records
        
    $result mysql_query($sql) or die('Query failed: ' mysql_error());
        while (
    $line mysql_fetch_array($resultMYSQL_ASSOC)) {
            echo 
    strpad(""$level"-") . $line["title"] . "<br />";
            
    displayPages($line["id"], $level+1);
        }
    }

    displayPages() 

  4. #3
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    PS - the only way to run a single query on a recursive table and get an ordered array back is using a UDF, which MySQL doesn't support.

  5. #4
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    PPS - the other option, of course would be to query the entire table and order by parent_id, then loop through the resultset multiple times for display purposes... that would probably be quicker than what I posted above, but the code would be a little more complex.

  6. #5
    Senior Member Steax's Avatar
    Join Date
    Dec 2006
    Location
    Bandung, Indonesia
    Posts
    1,207
    Member #
    14572
    Thanks transio. But it looks like I'll have to go for a full PHP-based structuring system, instead of manipulating in MySQL. Could you help on that? I'd think it would work like such:

    PHP Code:
    function GetChild($p){
       
    $q mysql_query("SELECT * FROM `pages` WHERE `parent`='$p'");
       
    $n mysql_numrows($q);
       for(
    $i=0;$i<$n;$i++){
         
    $c mysql_result($q,$i,"name");
         echo 
    "Child of $p #$i$c";
         
    GetChild($c);
      }

    But I guess then the variables would conflict with each other. Sorry, this is the first time I've attempted something like this... It looks like I simply have to have a good structured database, with the complexity being in the PHP since this is only for the control panel (and possibly the site map). Thanks for the help!
    Note on code: If I give code, please note that it is simply sample code to demonstrate an effect. It is not meant to be used as-is; that is the programmer's job. I am not responsible to give you support or be held liable for anything that happens when using my code.

  7. #6
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    Steax, I agree that you'll need to use PHP. Did you read my whole post? Your PHP code above, it's virtually identical to what I posted, minus the $level identifier.

    Well, really, you will need more than that. Look at DOM for an archetype on recursive architecture ancestry. There's getParent($id), getChildren($id), getAncestor($id, $generation), getAllAncestors($id), getDecendents($id)

    You can use "getAllAncestors" to create a breadcrumb trail, and getChildren to generate your menu structures (expanding as you explore sections). Then you can use getAllDecendents(null) to get your sitemap.

  8. #7
    Senior Member Steax's Avatar
    Join Date
    Dec 2006
    Location
    Bandung, Indonesia
    Posts
    1,207
    Member #
    14572
    Oh, alright. I'm not very used to using array fetching, I usually go with mysql_result. Should I adjust to array fetching?

    I also didn't understand how the while loop in your first post goes. I think I'll try your code there. My entire worry was that variables called might inflict on each other. I'll give your code a go. Thanks transio.
    Note on code: If I give code, please note that it is simply sample code to demonstrate an effect. It is not meant to be used as-is; that is the programmer's job. I am not responsible to give you support or be held liable for anything that happens when using my code.

  9. #8
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    BTW, that "IS NULL" bit is MS-SQL specific. I think MySQL uses the function "IFNULL(column)". You might wanna change the code around to account for that.

  10. #9
    Senior Member filburt1's Avatar
    Join Date
    Jul 2002
    Location
    Maryland, US
    Posts
    11,774
    Member #
    3
    Liked
    21 times
    Well kind of. It's the ANSI SQL standard; PostgreSQL and Oracle both use IS NULL. It's only retarded MySQL that (apparently) doesn't support it.
    filburt1, Web Design Forums.net founder
    Site of the Month contest: submit your site or vote for the winner!

  11. #10
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    Quote Originally Posted by filburt1
    Well kind of. It's the ANSI SQL standard; PostgreSQL and Oracle both use IS NULL.
    Back when I used to use Oracle, it didn't support IS NULL either, I think. Or maybe that's Informix. They're kind of a blur to me.


Page 1 of 3 1 2 3 LastLast

Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 03:58 AM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com