Report w/Cateories/subcategories

[tlei]

Hi all - this is my 1st post!

I am working on a php/mysql app in which I need to generate dynamic html reports that arrange data from the db in a 3-level hierarchy. I'm able to write the big-honkin'-query that pulls all the data I need (from multiple tables, w/keys to create relationships), but I'm having trouble presenting it. I can spew the data into an html table but all the categories, subcategories, etc get repeated for every record, like this:

Category1 | SubCat1
Category1 | SubCat2 | SubSub1 | data1
Category1 | SubCat2 | SubSub1 | data2
Category1 | SubCat2 | SubSub2
Category1 | SubCat3
... etc

I want my html report to be structured more like this:

Category1
    SubCat1
    SubCat2
        SubSub1
            -data1
            -data2
       SubSub2
    SubCat3

I'm a relative newbie to php and to programming generally, tho am comfortable with SQL. I've played around with trying to nest 'while' statements, etc., but keep hitting walls. I won't bore you with all the real data/table structures I'm using, but here's an example:

*********** [highest level]
tbl_order
   id [pk,int]
   order [varchar]

*********** [mid level]
tbl_genus
   id [pk,int]
   genus [varchar]
   order_id [int]

*********** [lowestlevel]
tbl_species
   id [pk,int]
   species [varchar]
   genus_id [int]

The report wd look something like:

order1
    genusA
      speciesX
      speciesY
      speciesZ
    genusB
      speciesP
      speciesQ
      speciesR

And the query to generate the data would look something like this:

$sql=mysql_query("SELECT order.*,genus.*,species.*
                 FROM    order, genus, species
                 WHERE   order.id=genus.order_id
                 AND     genus.id=species.genus_id
                 AND     order.id=" . $_GET['order_id']);

I'm guessing there's a fairly simple way to do this but I'm stumped. Can anyone help?

Thanks!

[johny_d]

Try this:

$query = "SELECT `order`.`order`,`order`.id as oid,genus.genus,genus.id as gid,species.species, species.id as sid FROM `order`, genus, species WHERE `order`.id =genus.order_id AND genus.id=species.genus_id";
$rows = mysql_query ($query);
     
while ($v  = mysql_fetch_assoc ($rows)) {
 $order[$v['oid']] = $v['order'];
 $genus[$v['oid']][$v['gid']] = $v['genus'];
 $species[$v['gid']][$v['sid']] = $v['species'];
 }
 
$result = "<ul>";
foreach ($order as $ko => $vo) {
 $result .= "<li>".$vo."\r\n <ul>\r\n";
 foreach ($genus[$ko] as $kg => $vg) {
  $result .= "  <li>".$vg."\r\n  <ul>\r\n";
  foreach ($species[$kg] as $ks => $vs) {
   $result .= "   <li>".$vs."</li>\r\n";
   }
  $result .= "  </ul></li>\r\n";
  }
 $result .= " </ul></li>\r\n";
 }
$result .= "</ul>\r\n";
 
echo $result;

It worked on my comp.

[tlei]

Try this:

$query = "SELECT `order`.`order`,`order`.id as oid,genus.genus,genus.id as gid,species.species, species.id as sid FROM `order`, genus, species WHERE `order`.id =genus.order_id AND genus.id=species.genus_id";
$rows = mysql_query ($query);
     
while ($v  = mysql_fetch_assoc ($rows)) {
 $order[$v['oid']] = $v['order'];
 $genus[$v['oid']][$v['gid']] = $v['genus'];
 $species[$v['gid']][$v['sid']] = $v['species'];
 }
 
$result = "<ul>";
foreach ($order as $ko => $vo) {
 $result .= "<li>".$vo."\r\n <ul>\r\n";
 foreach ($genus[$ko] as $kg => $vg) {
  $result .= "  <li>".$vg."\r\n  <ul>\r\n";
  foreach ($species[$kg] as $ks => $vs) {
   $result .= "   <li>".$vs."</li>\r\n";
   }
  $result .= "  </ul></li>\r\n";
  }
 $result .= " </ul></li>\r\n";
 }
$result .= "</ul>\r\n";
 
echo $result;

It worked on my comp.

Thanks, johnny_d. When I applied your script to my db I couldn't make it work - I tried checking all the syntax and everything, but I get nothing. The tables and fields I'm actually using are:

process [tbl]
   id [pk]
   process

step [tbl]
   id [pk]
   step
   process_id

substep [tbl]
   id [pk]
   substep
   step_id

...and here is the code I used, following your model

$query = "SELECT process.process,process.id AS pid,
    step.step.step.id AS sid ,
    substep.substep,substep.id AS bid
        FROM process,step,substep 
        WHERE process.id =step.process_id 
        AND step.id=substep.step_id";
    
    $rows=mysql_query($query) or die(mysql_error());
    
    while($d=mysql_fetch_assoc($rows) {    
        $process[$d['pid']] = $d['process'];
        $step[$d['sid']] = $d['step'];
        $substep[$d['bid']] = $d['substep'];
    
        $result = "<ul>";
        
        foreach($process as $key_p => $val_p) {
            $result .= "<li>" . $val_p . "\r\n <ul>\r\n";
            
            foreach($step as $key_s => $val_s) {    
                $result .= "   <li>" . $val_s . "\r\n <ul>\r\n";
                
                foreach($substep as $key_b => $val_b) {
                    $result .= "      <li>" . $val_b . "\r\n <ul>\r\n";
                    }
                $result .= "     </ul></li>\r\n";
                }
            $result .= "  </ul></li>\r\n";
            }
        $result .= "</ul></li>\r\n";
        }
    echo $result;

I'm sorry to be a pain, but can you see what I'm doing wrong? Thanks again.

[johny_d]

here's your code:

$query = "SELECT `process`.`process`,`process`.id as pid,step.step,step.id as sid,substep.substep, substep.id as bid FROM `process`, step, substep WHERE `process`.id =step.process_id AND step.id=substep.step_id";
$rows = mysql_query ($query);
     
while ($d  = mysql_fetch_assoc ($rows)) {
 $process[$d['pid']] = $d['process'];
 $step[$d['pid']][$d['sid']] = $d['step'];
 $substep[$d['sid']][$d['bid']] = $d['substep'];
 }
 
$result = "<ul>";
foreach ($process as $kp => $vp) {
 $result .= "<li>".$vp."\r\n <ul>\r\n";
 foreach ($step[$kp] as $ks => $vs) {
  $result .= "  <li>".$vs."\r\n  <ul>\r\n";
  foreach ($substep[$ks] as $kb => $vb) {
   $result .= "   <li>".$vb."</li>\r\n";
   }
  $result .= "  </ul></li>\r\n";
  }
 $result .= " </ul></li>\r\n";
 }
$result .= "</ul>\r\n";
 
echo $result;

[tlei]

johnny_d - This worked - you ROCK! Thanks for bearing with me on this. I'll study your code to try and figure it out for myself.

[johny_d]

I'm glad to be of help ;)

[tlei]

Can you point me to a site/article etc that explains the way you manipulated the arrays, associative arrays etc, eg

while ($d  = mysql_fetch_assoc ($rows)) {
    $process[$d['pid']] = $d['process'];
    $step[$d['pid']][$d['sid']] = $d['step'];
    $substep[$d['sid']][$d['bid']] = $d['substep'];
                             }

and

foreach ($process as $kp => $vp)

I've been wading around on the web looking for clues but am still confused... I greatly appreciate all the time you put into solving my problem, and will understand if you're "over it"... but I'd really like to figure this out.
Thanks again.

[johny_d]

Well, all that's there are basic tables that you can find in php manual.
The way you structure the tables is another thing and that you don't find in books. First, you have to understand what you want to accomplish and than how to put it in maths matrixes (which I think tables expressions are).

So, let's start with what we want to get:
we have 3 levels of info, each level being the parent of the next one.

level 0: PROCESS
level 1: STEP; has PROCESS as parent
level 2: SUBSTEP; has STEP as parent

so, we want 3 arrays, each with information from one level and the connection to its parent, so we can see later who's child is each table; (for the sake of the clearness I'll write without $ and quotes and any other php grammar; all I want is to emphasize the structure)

first one: process array
process[process_id] = process_name

second one: step array
What we want next is to create an array of steps (children) for each process;
step[process_id] (eg: step[1], step[2], step[3], where 1,2,3 are the ids of the parent processes
)
so if we have 3 processes in the process array, we'll have 3 step arrays, each one with its own pairs of keys=>values, which are the steps corresponding to the respective process.
Eg:
step[1] = array(s1_key_1=>s1_value_1, s1_key_2=>s1_value_2, ....);
This translates as: the steps for process 1 are s1_value_1, s1_value_2, .... with the ids of the steps being s1_key_1, s1_key_2, ....

which can be written as
step[1][s1_key_1] = s1_value_1
step[1][s1_key_2] = s1_value_2
......

which can be abstracted in
element[parent_id][element_id] = element_name

step[process_id][step_id] = step_name;

The same principle works between steps and substeps
substep[step_id][substep_id] = substep_name;

Than all you have to do is take each row (you called the row variable $d) from the mysql query response, which has this structure:
$d = process, pid (as process ID), step, sid (as step ID), substep, bid (as substep ID)
and replace the variables in the expressions above with the values from the row:
- process_name = $d
- process_id = $d
- step_name = $d
- step_id = $d
- substep_name = $d
- substep_id = $d

At this point, we have information grouped in tables that make the connection between elements at a certain level and their parent name and id.

Now we want to expand this tree we've got. How?
We start from the top;

we take each element from the top level
foreach ($process as $kp => $vp)

we get the element (PROCESS) name

and than foreach child of this element (each step of this process)
foreach ($step[$kp] as $ks => $vs)
($kp is the id of the parent process, see above)

we get the element (STEP) name

and than foreach child of this element (each substep of this step)
foreach ($substep[$ks] as $kb => $vb)
($ks is the id of the parent step, see above)

we get the element (SUBSTEP) name

And there's your tree structure! :icon_wink:

I don't know if it makes any sense to you, but maybe it gives you a hint in uderstanding how tables work.
In my oppinion, tables are some of the hardest things to understand. If you understood math matrixes in school, you will get the php tables too, as they are the same thing - matrixes, if not, you will have a hard time with them.
Anyway, you should start with simple ones, defined by you in php and than go to mysql result tables.

Learning php and mysql is very easy; the hard thing is how you translate a mathematical problem and equation into these languages :icon_wink:

[tlei]

Thanks again, johnny_d. I think I get generally how you constructed the arrays and then used embedded foreach statements to render the data in a way that reflects the parent/child relationships. One of things I don't understand is the array syntax in which it appears that there are 2 values for the key in the array, eg:

$step[$d['pid']][$d['sid']] = $d['step'];

What does it mean when 2 values ($d and $d)are defined side-by-side on the 'key' side of the equation?

[johny_d]

That is an array of arrays.
That means that each key of the top level array point to an array instead of a string value.
You can write that expression in another way:

$step[$d] = array ([$d] => $d);

or, to simplify:

step_array[process_id] = array (step_id => step_name);

In this expression, "process_id" is a key of the "step_array" and it has an array as value;
After you will iterate through all the values you will have something like this:

step_array[1][1] = step_name_1;
step_array[1][2] = step_name_2;
step_array[1][3] = step_name_3;

step_array[2][1] = step_name_4;
step_array[2][2] = step_name_5;

step_array[3][1] = step_name_6;

which is equivalent to

step_array[1] = array (1=>step_name_1, 2=>step_name_2, 3=>step_name_3);

step_array[2] = array (1=>step_name_4, 2=>step_name_5);

step_array[3] = array (1=>step_name_6);

which in turn is equivalent

step_array = (1=>array (1=>step_name_1, 2=>step_name_2, 3=>step_name_3), 2=>array (1=>step_name_4, 2=>step_name_5), 3=>array (1=>step_name_6));

and that's what I said in the begining:
an array of arrays !