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?