grouping in a table

[mantrid]

I have records from a database that are extracted with php and displayed in
a table.
Data in some of the fields is replicated

eg
something1,item1,somethingelse1
something1,item1,somethingelse2
something1,item2,somethingelse3
something1,item2,somethingelse4
something2,item1,somethingelse5
something2,item1,somethingelse6
something2,item2,somethingelse7
something2,item2,somethingelse8

i wish to tidy this up and make it more readable by putting repeated fields
as a header row or something along those lines
eg

something1
item1
somethingelse1
somethingelse2
item2
somethingelse3
somethingelse4

something2
item1
somethingelse5
somethingelse6
item2
somethingelse7
somethingelse8

I think I can do it using about 3 sql statements two of which use grouping,
but I would like to know if there is a more elegant solution using the one
sql and somehow doing the grouping in the php.

Anyone done anything similar, or seen an example out there?

Thanks Ian

[strawberry]

mantrid wrote:

I have records from a database that are extracted with php and displayed in
a table.
Data in some of the fields is replicated

eg
something1,item1,somethingelse1
something1,item1,somethingelse2
something1,item2,somethingelse3
something1,item2,somethingelse4
something2,item1,somethingelse5
something2,item1,somethingelse6
something2,item2,somethingelse7
something2,item2,somethingelse8

i wish to tidy this up and make it more readable by putting repeated fields
as a header row or something along those lines
eg

something1
item1
somethingelse1
somethingelse2
item2
somethingelse3
somethingelse4

something2
item1
somethingelse5
somethingelse6
item2
somethingelse7
somethingelse8

I think I can do it using about 3 sql statements two of which use grouping,
but I would like to know if there is a more elegant solution using the one
sql and somehow doing the grouping in the php.

Anyone done anything similar, or seen an example out there?

Thanks Ian

why not do the grouping in sql. It might just be simpler abd quicker

[Andy Hassall]

On Mon, 02 Oct 2006 20:07:47 GMT, "mantrid" <ia********@virgin.netwrote:

>I have records from a database that are extracted with php and displayed in
a table.
Data in some of the fields is replicated

eg
something1,item1,somethingelse1
something1,item1,somethingelse2
something1,item2,somethingelse3
something1,item2,somethingelse4
something2,item1,somethingelse5
something2,item1,somethingelse6
something2,item2,somethingelse7
something2,item2,somethingelse8

i wish to tidy this up and make it more readable by putting repeated fields
as a header row or something along those lines
eg

something1
item1
somethingelse1
somethingelse2
item2
somethingelse3
somethingelse4

something2
item1
somethingelse5
somethingelse6
item2
somethingelse7
somethingelse8

I think I can do it using about 3 sql statements two of which use grouping,
but I would like to know if there is a more elegant solution using the one
sql and somehow doing the grouping in the php.

For each field that you're grouping by in your display, keep a variable for
the last seen value. When it changes, print a header row, and reset the
variable. So for example here, you'd have two variables keeping track of the
"somethingX" and "itemX" fields.

When you see a change in the first-level heading also reset the second-level
variable (to empty string or something that won't appear in the data itself),
so you get a heading row for "something2/item1".

Your SQL and data remains unchanged, it's just a matter of how you display it.

--
Andy Hassall :: an**@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

[Colin Fine]

Andy Hassall wrote:

On Mon, 02 Oct 2006 20:07:47 GMT, "mantrid" <ia********@virgin.netwrote:

>I have records from a database that are extracted with php and displayed in
a table.
Data in some of the fields is replicated

eg
something1,item1,somethingelse1
something1,item1,somethingelse2
something1,item2,somethingelse3
something1,item2,somethingelse4
something2,item1,somethingelse5
something2,item1,somethingelse6
something2,item2,somethingelse7
something2,item2,somethingelse8

i wish to tidy this up and make it more readable by putting repeated fields
as a header row or something along those lines
eg

something1
item1
somethingelse1
somethingelse2
item2
somethingelse3
somethingelse4

something2
item1
somethingelse5
somethingelse6
item2
somethingelse7
somethingelse8

I think I can do it using about 3 sql statements two of which use grouping,
but I would like to know if there is a more elegant solution using the one
sql and somehow doing the grouping in the php.

For each field that you're grouping by in your display, keep a variable for
the last seen value. When it changes, print a header row, and reset the
variable. So for example here, you'd have two variables keeping track of the
"somethingX" and "itemX" fields.

When you see a change in the first-level heading also reset the second-level
variable (to empty string or something that won't appear in the data itself),
so you get a heading row for "something2/item1".

Your SQL and data remains unchanged, it's just a matter of how you display it.

I would prefer to use associative arrays and array_key_exists. Something
like:

$root = array();

while ($record = /*retrieve a record from DB */) {
$fields = /*fn. to split record into an array of fields*/;
$current = &$root;
foreach ($fields as $field) {
if (!array_key_exists($field, $current) {
$current[$field] = array();
}
$current = &$current[$field];
}
}

This now has your entire data in a hierarchical array structure, and
it works however many fields there are in a record. (The last level
will be empty arrays, but that shouldn't be a problem).

Colin

[strawberry]

Anyone done anything similar, or seen an example out there?

Is xml out of the question? There should be lots of examples out there
of doing this kind of thing in xml.