•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the PHP section within the Web Development category of DaniWeb, a massive community of 391,125 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 3,266 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our PHP advertiser: Lunarpages PHP Web Hosting
Views: 969 | Replies: 9 | Solved
 |
•
•
Join Date: May 2007
Posts: 7
Reputation: 
Rep Power: 0
Solved Threads: 0
Newbie Poster
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:
I want my html report to be structured more like this:
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:
The report wd look something like:
And the query to generate the data would look something like this:
I'm guessing there's a fairly simple way to do this but I'm stumped. Can anyone help?
Thanks!
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
*********** [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
$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!
•
•
Join Date: May 2007
Location: Bucharest, RO
Posts: 67
Reputation:
Rep Power: 2
Solved Threads: 4
Junior Poster in Training
Try this:
It worked on my comp.
php Syntax (Toggle Plain Text)
•
•
Join Date: May 2007
Posts: 7
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
•
•
•
•
Originally Posted by johny_d 
Try this:
It worked on my comp.php Syntax (Toggle Plain Text)
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
$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; •
•
Join Date: May 2007
Location: Bucharest, RO
Posts: 67
Reputation:
Rep Power: 2
Solved Threads: 4
Junior Poster in Training
here's your code:
php Syntax (Toggle Plain Text)
•
•
Join Date: May 2007
Posts: 7
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
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.
•
•
Join Date: May 2007
Location: Bucharest, RO
Posts: 67
Reputation:
Rep Power: 2
Solved Threads: 4
Junior Poster in Training
•
•
Join Date: May 2007
Posts: 7
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
Can you point me to a site/article etc that explains the way you manipulated the arrays, associative arrays etc, eg
and
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.
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)
Thanks again.
•
•
Join Date: May 2007
Location: Bucharest, RO
Posts: 67
Reputation:
Rep Power: 2
Solved Threads: 4
Junior Poster in Training
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']
- process_id = $d['pid']
- step_name = $d['step']
- step_id = $d['sid']
- substep_name = $d['substep']
- substep_id = $d['bid']
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!
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
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']
- process_id = $d['pid']
- step_name = $d['step']
- step_id = $d['sid']
- substep_name = $d['substep']
- substep_id = $d['bid']
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!
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
Last edited by johny_d : May 25th, 2007 at 8:29 pm.
•
•
Join Date: May 2007
Posts: 7
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
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: What does it mean when 2 values ($d['pid'] and $d['sid'])are defined side-by-side on the 'key' side of the equation?
$step[$d['pid']][$d['sid']] = $d['step'];
•
•
Join Date: May 2007
Location: Bucharest, RO
Posts: 67
Reputation:
Rep Power: 2
Solved Threads: 4
Junior Poster in Training
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['pid']] = array ([$d['sid']] => $d['step']);
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 !
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['pid']] = array ([$d['sid']] => $d['step']);
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 !
Last edited by johny_d : May 27th, 2007 at 6:06 am.
|
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
•
•
•
•
•
•
•
•
DaniWeb PHP Marketplace
Linear Mode
- Previous Thread: append content of 1 table to another
- Next Thread: Modify Record script doesn't add/change info
