convert SQL results set to N-dimensional array [SOLVED]

[eluser]obiron2[/eluser]
I am returning a results set which contains the following data:

Table number, seat number, First Name, Surname

Ordered by table ASC, Seat ASC so I get

Code:

1,1,Fred,Bloggs
1,2,Joe, Smith
2,1,Bob,Jones
2,2,Mick,Reed
3,1,Mark,Thomas
3,2,Sam,Brown

I want to convert this to a 2 dimensional array with the table number being the outer dimension and the seat and name data being the inner dimension

Code:

$data['Tabledetails'] = array
                         (array
                           (Table=>1,
                            Tabledata => array
                             (Seat=>1,Fname=>'Fred',Sname=>'Bloggs'),
                             (Seat=>2,Fname=>'Joe',Sname=>'Smith')
                            ),
                            (Table=>2,
                             Tabledata => array
                               (Seat=>1,Fname=>'Bob', Sname=>'Jones'),
                               (Seat=>2,Fname=>'Mick', Sname=>'Reed'),
                            ),
                            (Table=>3,
                             Tabledata => array
                               (Seat=>1,Fname=>'Mark', Sname=>'Thomas'),
                               (Seat=>2,Fname=>'Sam', Sname=>'Brown'),
                            )
                           )

Does anyone have a generic function that does this, either into an array or an object. The data is so much easier to work with in the view if it correctly formatted.

Obiron

[eluser]obiron2[/eluser]
OK,

here is the function I have written. It takes a flat set of results, already sorted by the primary and secondary key (e.g. by table and then by seat within table) and converts this to an array as follows:

Outer Array unkeyed list of Key array records.
Array Record = array with 2 Keyed elements. 1st element is the Key specified, 2nd Element is an sub-array of data associated with the Key in record number order
Sub-Array = an unkeyed array of sub-sub-arrays
sub-sub-array = a keyed array of data relating to a record in the original data set.

So in the example quoted where I have table# and seat# in the flat data structure, I specify table# as the key I need and I end up with an array of 4 elements (four tables). Each element has an array of two elements [Table] and [TableData]. Each [TableData] is an array of seats. Each seat array has [seat],[FirstName][LastName]as keys.

$object is the results set
$indexKey is the $object->row->field I want to group on

Code:

function converttoarray($object,$indexKey)
    {
     $innerArray = array();     // key value pair array of child records
     $dataArray = array();      // numeric array of innerArrays
     $indexKeyArray = array();  // key value pair array indesKey=> indexValue
     $returnArray = array();    //numeric array of indexKeyArray
     $indexKeyData = $indexKey . "Data";
     $indexValue = null;
     $validateIndexValue=null;

     foreach ($object as $objectsplit)
      {
        $innerArray = array();  // reset the inner array
        foreach ($objectsplit as $key=>$value)
        {
          if($key != $indexKey)                   // if this isn't the key
          {
            $innerArray[$key]=$value;             // this builds the array for non index key
          }
          else                                    // do this if the key is the index key
          {
            $validateIndexValue = $value;
          }
        }
        if ($validateIndexValue != $indexValue)      // if the index value has changed
        {
           $indexKeyArray[$indexKey] = $indexValue;
           $indexKeyArray[$indexKeyData] =$dataArray;
           $indexValue = $validateIndexValue;        // update the indexValue
           $dataArray = array();                     // reset the data array
           if ($indexKeyArray[$indexKey] != null)
           {
             $returnArray[] =$indexKeyArray;           // add the indexKeyArray to the return Array
           }
           $indexKeyArray = array();                 // reset the indexKeyArray
        }
          $dataArray[] = $innerArray;
      }
      // do it all again for the last objectsplit (yes, I know I should be calling another function!)
      $indexKeyArray[$indexKey] = $indexValue;
      $indexKeyArray[$indexKeyData] =$dataArray;
      $indexValue = $validateIndexValue;             // update the indexValue
      $dataArray = array();                          // reset the data array
      if ($indexKeyArray[$indexKey] != null)
      {
        $returnArray[] =$indexKeyArray;              // add the indexKeyArray to the return Array
      }
      return $returnArray;
    }

[eluser]obiron2[/eluser]
to access the data in the view:

Code:

foreach ($keys as $key) //$keys is the name of the viewdata array element
{
  print $key[keyname] //keyname is the name of the property you grouped on
  foreach ($key[keynameData] as $data) // the array is always called the keyname+'Data'
  {
   print $data[datakeyname]  // the datakeyname is the name of the property from the
                             // original data  
  }
}

I hope this can be of use to someone. As you can see, accessing the data is much easier in the view than trying to iterate the flat file and work out where to break on change of values.