Posts by 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?

Thanks to everyone for the warm welcome. In my 1st day I've already had a problem (a major problem, for me at least) solved - Thanks to johnny_d!

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.

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.

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 .= …

Hi everyone. I'm new, obviously, to Daniweb, and want to introduce myself. Self-taught/teaching lover of databases and the web. Working on several db apps for the healthcare industry, and have been pulling wee-hour slogs buried in queries and variables, trying to learn how to turn my ideas into something useful. I've worked around techies for many years and have an abiding admiration for code-slingers and their ilk. Hopefully over time I'll be able to add some value to Daniweb. Thanks in advance to everyone for your help!

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 …