grouping in a table

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • mantrid

    #1

    grouping in a table

    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,item 1,somethingelse 1
    something1,item 1,somethingelse 2
    something1,item 2,somethingelse 3
    something1,item 2,somethingelse 4
    something2,item 1,somethingelse 5
    something2,item 1,somethingelse 6
    something2,item 2,somethingelse 7
    something2,item 2,somethingelse 8

    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

    #2
    Re: grouping in a table


    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,item 1,somethingelse 1
    something1,item 1,somethingelse 2
    something1,item 2,somethingelse 3
    something1,item 2,somethingelse 4
    something2,item 1,somethingelse 5
    something2,item 1,somethingelse 6
    something2,item 2,somethingelse 7
    something2,item 2,somethingelse 8
    >
    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

    Comment

    • Andy Hassall

      #3
      Re: grouping in a table

      On Mon, 02 Oct 2006 20:07:47 GMT, "mantrid" <ian.dandav@vir gin.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,ite m1,somethingels e1
      >something1,ite m1,somethingels e2
      >something1,ite m2,somethingels e3
      >something1,ite m2,somethingels e4
      >something2,ite m1,somethingels e5
      >something2,ite m1,somethingels e6
      >something2,ite m2,somethingels e7
      >something2,ite m2,somethingels e8
      >
      >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 :: andy@andyh.co.u k :: http://www.andyh.co.uk
      http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

      Comment

      • Colin Fine

        #4
        Re: grouping in a table

        Andy Hassall wrote:
        On Mon, 02 Oct 2006 20:07:47 GMT, "mantrid" <ian.dandav@vir gin.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,ite m1,somethingels e1
        >something1,ite m1,somethingels e2
        >something1,ite m2,somethingels e3
        >something1,ite m2,somethingels e4
        >something2,ite m1,somethingels e5
        >something2,ite m1,somethingels e6
        >something2,ite m2,somethingels e7
        >something2,ite m2,somethingels e8
        >>
        >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_exist s. 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_exi sts($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

        Comment

        • strawberry

          #5
          Re: grouping in a table

          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.

          Comment

          Working...