SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Evangelist jazztie's Avatar
    Join Date
    Mar 2001
    Location
    the Netherlands
    Posts
    519
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've recently posted a thread (http://www.sitepointforums.com/showt...threadid=22215) about a kind of site map... But I have another problem... which is about the mentioned thread, but more complicated.

    As you can read in the above mentioned thread I have a database with the following tables:
    - table cats (id, titel) 'titel is dutch for title'
    - table subcats (id, cat, titel) cat = foreign key to table cats.id

    but also:
    - table pages (id, subcat, txt, rubriek)
    A page is the text of the subcategory, and lists the 'rubrieken' which appear on the site.
    rubriek is dutch for section.
    subcat = foreign key to table subcats.id
    rubriek = foreign key to table rubrieken.id. A page can have 0, 1 or more 'rubrieken'.

    - table rubrieken (id, titel)
    - table links (id, rubriek, txt, email, titel)
    rubriek = foreign key to table rubrieken.id. A link (basically an article) belongs to a 'rubriek'.
    ------
    I know this database schema really s*cks. I know there are much better ways to accomplish the things they intend to with this database.... Unfortunately, I cannot change the database at this time... (ARGH!!!!)

    So, what it simplified looks like;
    cats -> subcats (<-cat) -> pages (<-subcat, rubriek->) -> rubrieken -> links (<-rubriek)
    ----

    What do I want to do???
    I want a sitemap which elaborates on the one mentioned in the above topic.
    This one shows a map like:
    ---
    Category 1 'titel of category'
    ... 1.1 'titel of subcategory'
    ... 1.2 'titel of subcategory'

    Category 2 'titel of category'
    ... 2.1 'titel of subcategory'
    ... 2.2 'titel of subcategory'
    ---
    It uses the following code:
    PHP Code:
    $result mysql_query("select * from cats order by id");
    while(
    $row mysql_fetch_array($result)) {
        
    extract($row);
        
    $cats[$id] = array("titel" => $titel,
                           
    "subcats" => array()
                           );
        }

    //Get all the subcats and put them into the array at the corresponding main cat    

    $result2 mysql_query("select * from subcats order by titel");
    while(
    $row mysql_fetch_array($result2)) {
        
    extract($row);
        
    $cats[$cat]["subcats"][$id] = $titel;
        }    
        
    foreach(
    $cats as $key => $val) {
        print 
    "Categorie $key <b>".$cats[$key]["titel"]."</b><br>";
        
    $i 1;
        foreach(
    $cats[$key]["subcats"] as $jey => $jal) {
            print 
    "&nbsp;&nbsp;&nbsp; $key.$i &nbsp;&nbsp; $jal<br>";
            
    $i++;
            }
        unset(
    $i);    
        print 
    "<br><br>";
        }
    // Thanks to FreddydoesPHP, BlackCat and FreakysID !!! 
    What I want to accomplish is by clicking on one of the subcats (linking itself isn't the problem) you are shown the 'rubriek' (or plural) and the different links belonging to the subcategory. I don't mind if this is shown in a new page or on the page itself...

    i.e.
    ----
    Category 2 'titel of category'
    ... 2.1 'titel of subcategory'
    ...... 'titel of rubriek 1'
    ......... 'titel of link 1'
    ......... 'titel of link 2'
    ......... 'titel of link 3'
    ...... 'titel of rubriek 2'
    ......... 'titel of link 4'
    ......... 'titel of link 5'
    ......... 'titel of link 6'
    -----
    Because of the weird database structure I have no idea how to tackle this problem... Please help me!!!
    Last edited by jazztie; May 3, 2001 at 02:15.

  2. #2
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is this how the relationships are?

    Cat : SubCat is 1 : 0,n
    SubCat : Page is 1 : 1
    Page : Rubrieken is 1 : 0,n
    Rubrieken : Links is 1 : 0,n

    ie;
    - a Cat can have 0 or may SubCats; each SubCat belongs to one and only one Cat.
    - a SubCat can have one and only one Page; each Page belongs to one and only one SubCat.
    - a Page can have none or many Rubriekens; each Rubrieken can have one and only one Page( and transitively is related to only one SubCat).
    - A Rubrieken can have none or many links; each link belongs to one and only one Rubrieken?

    That is - a hierarchical tree? I get lost with at the Rubrieken level - whether a Rubreiken can be related to many or just one SubCat.

  3. #3
    SitePoint Evangelist jazztie's Avatar
    Join Date
    Mar 2001
    Location
    the Netherlands
    Posts
    519
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Almost correct FreakysID....

    it's like this:

    Cat : SubCat --- is --- 1 : 0,n
    SubCat : Pages --- is --- 1 : 1
    Pages : Rubrieken --- is --- 1,n : 0,n
    Rubrieken : Links --- is --- 1,n : 1,n


    - a Cat can have 0 or many SubCats; each SubCat belongs to one and only one Cat.
    - a SubCat can have one and only one Page; each Page belongs to one and only one SubCat.
    - a Page can have none or many Rubrieken; each Rubrieken can have one or many Pages. Rubrieken can belong to more than one Subcat.
    - A Rubrieken can have one or many links; each link belongs to one or more Rubrieken.

    Like I said, the structure sucks... the person who made this database should be shot.
    My final year project is about making a Content Management System in an Intranet... I can't change the database of the Internet... unfortunately.

  4. #4
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OMG - this truely is a disaster of a data model then. This implies that if a subcat is related to many rubreiks then that subcat will have many related rows in pages. Is that right? (because table pages holds the foreign key to rubreik). I just want to check that.)

  5. #5
    SitePoint Evangelist jazztie's Avatar
    Join Date
    Mar 2001
    Location
    the Netherlands
    Posts
    519
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, I think you're right...

    But I believe the table 'Pages' holds null, one or more rubrieken.id's.
    So, one record can have plural rubrieken id's...

    That's the strange thing about this database...
    table pages should be in table subcats... table subcats would have 'txt' and 'rubrieken' as extra items. Like I said, the guy who designed this should be shot.

    So, a page record could look like this:

    '23', '2', 'text of the page.. blablabla', '3, 4, 19'.
    (id), (subcat.id), (text), (rubrieken.id's).

  6. #6
    SitePoint Evangelist jazztie's Avatar
    Join Date
    Mar 2001
    Location
    the Netherlands
    Posts
    519
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I could of course use a link from the list which is provided by the above mentioned code...

    And use the subcats.id in a similar kind of code to make a list of Subcats, Rubrieken and Links.
    Looking something like this (in a new page)
    ----
    Subcategory 2 'titel of subcategory'
    ... 'titel of rubriek' (id extracted from table pages)
    ...... 'titel of link 3'
    ...... 'titel of link 45'
    ...... 'titel of link 298'

    ... 'titel of rubriek' (id extracted from table pages)
    ...... 'titel of link 11'
    ...... 'titel of link 24'
    ...... 'titel of link 48'
    ----

    Does anyone have any tips on how to accomplish this?

  7. #7
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That looks like a good idea. You could also make it so that when you click on a subcategory, the link calls the same page again, but passes the value of the subcat and the page regenerates itself, but for that subcat it expands out the rubrieks and titels as you show in your post.

    So for the chosen subcat you need to find the matching pages record in the database and select the rubriek field:

    $sql = "SELECT rubriek FROM pages WHERE subcat = $subcat";

    Then explode rubriek to get the rubriek foreign keys into an array
    $result = mysql_query($sql);
    $row = mysql_fetch_array($result);
    $rubrieks = explode(",", $row["rubriek"]);

    For each rubriek in $rubrieks querty the database and get the details of that rubrieken from rubrieken and the list of associated links from link and print out the details.

    If you run into any troubles - post your code.


Bookmarks

Posting Permissions

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