complicated MySQL - showing categories

[jazztie]

I seem to have a problem:

Using the following code I get the following result:

//Grab all the main cats and store them in an array
$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    
$result = mysql_query("select * from subcats order by cat");
while($row = mysql_fetch_array($result)) {
    extract($row);
    $cats[$cat.ID]["subcats"][$id] = $titel;
    }    
    
    
foreach($cats as $key => $val) {
    print "Cat $key ".$cats[$key]["titel"]."<br>";
    $i = 1;
    foreach($cats[$key]["subcats"] as $jey => $jal) {
        print "&nbsp;&nbsp;&nbsp;Subcat $key.$i $jal<br>";
        $i++;
        }
    unset($i);    
    print "<br><br>";
    } 

----------
Category 1 University

Category 2 Education development

Category 3 Professionalising Education

Category 4 Organisation

Category 1ID
...Subcat 1ID.1 university of leiden
...Subcat 1ID.2 blablablablabla

Category 2ID
...Subcat 2ID.1 education blablabla
...Subcat 2ID.2 education blabladidid
...Subcat 2ID.3 education blakjlsdl

Category 3ID

et cetera, et cetera.
-----------
(subcats without the dots at the beginning)

The tables I have are:
cats
- id
- titel

subcats
- id
- cat
- titel

What I want to show is:

'Category 1 title'
... subcategory 1.1 title
... subcategory 1.2 title

'Category 2 title'
... subcategory 2.1 title
... subcategory 2.2 title
---
so with the example used above it should look like this:
---
University
... university of Leiden
... blablablablabla

Education development
... education blablabblab
... education ball9d9d9
... education skjlkf lkj blablabla
---

Somehow it does find the names (titel) for the categories, prints them and then it uses the id's for the categories and prints the subcats... we're almost there.

PS: this is part of a final year project I have to do for my study... (internship at the University)

[freakysid]

jazztie, I think I've pinpointed the problem.
This line:
$cats[$cat.ID]["subcats"][$id]*=*$titel;
Should be:
$cats[$Cat_ID]["subcats"][$id]*=*$titel;

I think that's the problem. Putting this post into a new thread was not a good idea, because I had to go back to your original post to find the database schema to find that bug

Looking at your database schema, BTW, I do not think its necessary to have two tables. Is not a subcategory just a category? (is a circle really just an elipse?) To me they are the same things because they have the same attributes (id and a title). The additional thing you need to model is the relationship that causes one category to be a sub-cateogory of another category. Where an entity is related to none or one other entities of the same type, this is a tree data structure which can be represented using just the one table.

Lets use your example data:

University
... university of Leiden
... blablablablabla

Education development
... education blablabblab
... education ball9d9d9
... education skjlkf lkj blablabla

Now lets represent this as a data tree using this schema
Cateogry(ID, Title, Parent)

Category
------
ID  | Title  | Parent
--------------------------------
 1 | University  | NULL
 2 | Education development | NULL
 3 | blablablablabla  | 1
 4 | education blablabblab | 2
 5 |  education ball9d9d9  | 2
 6 | education skjlkf lkj blablabla | 2

Then to get the table data into the array structure (and copy-and-pasting some of freddy's code)

$result*=*mysql_query("select***from*Categories*order*by*Parent, ID");

while($row = mysql_fetch_array($result) {
   extract($row);
   if ( is_null($Parent) ) {
      $cats[$ID] =*array("titel"*=>*$titel,
                         "subcats"*=>*array());
   } else {
   *$cats[$ID]["subcats"][$id]*=*$titel;
   }
} 

I hope that is of some help, seeing as it is for a academic project I thought that it might be an idea to look at the data-strucure you are using. Anyone feel free to tell me my way is stupid or that there is a better way of modelling/codeing this

[jazztie]

Freakysid,

thanx for the comment... sorry about posting this into a new message.

About the database-structure... I totally agree with you. Unfortunately the database I have to use for this project is not for me to change. It's the database the University uses and they don't want to change it.

I'll have a try with the changes in the code you supllied. Thanx!

[BlackCatt]

How ironic. The institutions we count on to teach the next generation of IT professionals has such a bad DB Schema.

"Well if you keep giving them the answers, how are they ever gonna learn?"

BlackCatt