Let's make things harder...

[jazztie]

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:

$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!!!

[freakysid]

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.

[jazztie]

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.

[freakysid]

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

[jazztie]

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

[jazztie]

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?

[freakysid]

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.