Multi Dimensional array issue

[eluser]RichM[/eluser]
I've been having a couple issues with finalizing some code. It's very close but not quite there and I'm stumped. I haven't worked with multi-dimensional arrays much so go easy on me.

I have a result from a database query that outputs multiple rows of equipment by joining 2 tables together. As you can see below it is sorted by eid ASC, pid ASC, and then hdate ASC. Each piece of equipment may have an hdate with associated hours on that date. For instance, eid of 54 has several dates with hours logged for pid 1 and then another date/hours with pid 4.
Result:

Code:

eid | cid | make   | model  | name | eqid | pid  | hdate      | hours | uid
54  | 1   | Titan  | 23433  | NULL | T28  | NULL |            |       | 1
55  | 1   | Clark  | CGP25  | NULL | E8   | 1    | 2012-11-07 | 5     | 2
55  | 1   | Clark  | CGP25  | NULL | E8   | 1    | 2012-11-08 | 2     | 2
55  | 1   | Clark  | CGP25  | NULL | E8   | 1    | 2012-11-09 | 4     | 2
55  | 1   | Clark  | CGP25  | NULL | E8   | 4    | 2012-11-10 | 5     | 2
56  | 2   | Toyota | Tundra | NULL | T40  | NULL | 2012-11-07 | 5     | 2

To output properly to my view (and eventually a spreadsheet) I would like it to output the array with a row for each piece of equipment for each project with a subarray of the dates and associated hours. Something like below where I'll loop through each array and if it has dates would loop through those and place the hours in the proper day of the month.

Required Output:

Code:

Array
(
  [1] => Array
  (
    [eid] => 55
    [pid] => 1
    [...] => ...
    [dates] => Array
      (
        [1] => Array
          (
            [hdate] => 2012-11-07
            [hours] => 5
          )
        [2] => Array
          (
            [hdate] => 2012-11-08
            [hours] => 2
          )
      )
  )
  [2] => Array
  (
    [eid] => 55
    [pid] => 4
    [...] => ...
    [dates] => Array
      {
        [1] => Array
          (
            'output dates if present'
          )
      )
  )
)

Would be nice if the dates were indexed by the day of the month so it would match up with the actual day of the month when rendering - but can fix that while looping through.


The code below outputs an unindexed array of exactly what the result above is. Been reading posts here and stackoverflow, but haven't had much luck getting it to output properly.

Code:

Code:

$equip=array();
        $i=0;
        foreach($query->result() as $row)
        {          
          if(!isset($equip[$i]))
            {
              $equip[$i]=array();
            }        
              $equip[$i]['eid'] = $row->eid;
              $equip[$i]['cid'] = $row->cid;
              $equip[$i]['uid'] = $row->uid;
              $equip[$i]['make'] = $row->make;
              $equip[$i]['model'] = $row->model;
              $equip[$i]['name'] = $row->name;
              $equip[$i]['eqid'] = $row->eqid;
              $equip[$i]['pid'] = $row->pid;
              $equip[$i]['hdate'] = $row->hdate;
              $equip[$i]['hours'] = $row->hours;
              $i++;
            
        }
        return $equip;

Any help is much appreciated.

[eluser]jmadsen[/eluser]
this mihgt be of some help:

http://www.codebyjeff.com/blog/2012/08/n...rscore-php

uses the Underscore.php library to group on keys

[eluser]fasfad[/eluser]
I've made an example, not tested. (should work) ;-)

Code:

$equipment = array();
  foreach ($query->result() as $result) {
   if(!isset($equipment[$result->eid])) {//if array does not contain the eqid. add information to it;
    $equipment[$result->eid]["make"]  = $result->make;
    $equipment[$result->eid]["model"]  =  $result->model;
    $equipment[$result->eid]["eqid"]  =  $result->eqid;
    $equipment[$result->eid]["projects"]  = array(); //we are going to add all hours logged based on date in seperate projects
   }
  
   if($result->hours == '' || $result->hours == 0)//no hours to log skip it
    continue;
   if($result->pid == NULL)
    $result->pid = 0; //change NULL pid to 0 so we can use it as project key
   if(isset($equipment[$result->eid]["projects"][$result->pid][$result->hdate])) { //date is already set so we ADD the hours to this day
    $equipment[$result->eid]["projects"][$result->pid][$result->hdate] += $result->hours;
   } else {//date hastn been set so just put hours in this date
    $equipment[$result->eid]["projects"][$result->pid][$result->hdate] = $result->hours;
   }
  
  }

Your result would be:

Code:

//You should get something like this:
  Array
  (
   [54] => Array
   (
    [make]   => "Titan"
    [model]  => 23433
    [eqid]   => "T28"
  
   )
   [55] => Array
   (
    [make]   => "Clark"
    [model]  => "CGP25"
    [eqid]   => "E8"
    [projects]  => Array
    (
     [1]  => Array
     (
      ["2012-11-07"]  => 5 //If you happen to have another hour-log on this date, it will ad to the current hourlog
      ["2012-11-08"]  => 2
      ["2012-11-09"]  => 4
     )
     [4]  => Array
     (
      ["2012-11-10"]  => 5
     )
    )
  
   )
   [56] => Array
   (
    [make]   => "Toyota"
    [model]  => "Tundra"
    [eqid]   => "T40"
    [projects]  => Array
    (
     [0]  => Array //this pid NULL has been changed to 0
     (
      ["2012-11-07"]  => 5
     )
    )
  
   )
  
  )

[eluser]RichM[/eluser]
That's perfect as far as output goes. Very clear thought process on what I was wanting - I would have never thought of formatting by projects.

The only issue I'm having now is something with the output.

When I print_r or var_dump it doesn't look to be indexing each equipment array. The projects array is indexed properly with pid. I just added the eid to be included in the array so it's usable like the rest of the items.

You've given me a way better understanding of multi dimensional arrays than what I've read anywhere else. And maybe that's because I can relate it more directly to my issue. Many thanks.

[eluser]fasfad[/eluser]
No Problem, it's all about making a picture in your head of the ouput you want and nest the array according to that picture.

Just something I thought about, I thought you may also want to sort the array according to the equipment usage per project.

So you might want to create another dimension, so change the code to get something like thisno redundant data)

Code:

Array
(
    [equipment] = Array //hold all equipment data in this array, you can check with isset() if it's already set before
    (
        [eq_id] =>Array({equipment_data}),
        [eq_id] =>Array({equipment_data}),
        [eq_id] =>Array({equipment_data}),
    )
    [projects] = Array //hold all your logs in this array grouped by projects
    (
        [pid] = Array
        (
            [eqid] = Array("2012-01-07"=>5,"2012-01-08"=>3,"2012-01-09"=>1),//here you log every hour per equipment in project, when you loop this data you can print equipment data by getting it from the equipment array: just using $myarray["equipment"][$current_eq_id] , or something
            [eqid] = Array("2012-01-07"=>5,"2012-01-08"=>3,"2012-01-09"=>1),
            [eqid] = Array("2012-01-07"=>5,"2012-01-08"=>3,"2012-01-09"=>1),
            [eqid] = Array("2012-01-07"=>5,"2012-01-08"=>3,"2012-01-09"=>1) //etc
        ),
        [pid] = Array
        (
            [eqid] = Array("2012-01-07"=>5,"2012-01-08"=>3,"2012-01-09"=>1),
            [eqid] = Array("2012-01-07"=>5,"2012-01-08"=>3,"2012-01-09"=>1),
            [eqid] = Array("2012-01-07"=>5,"2012-01-08"=>3,"2012-01-09"=>1),
            [eqid] = Array("2012-01-07"=>5,"2012-01-08"=>3,"2012-01-09"=>1) //etc
        )
    )
)

EDIT:
I changed some things to get the above output:

Code:

//some testdata
$obj1->eid = 54;
$obj1->make= "Titan";
$obj1->pid= NULL;
$obj1->hdate= '';
$obj1->hours= '';

$obj2->eid = 55;
$obj2->make= "Clark";
$obj2->pid= 1;
$obj2->hdate= '2012-11-07';
$obj2->hours= 5;

$obj3->eid = 55;
$obj3->make= "Clark";
$obj3->pid= 1;
$obj3->hdate= '2012-11-08';
$obj3->hours= 2;

$obj4->eid = 55;
$obj4->make= "Clark";
$obj4->pid= 1;
$obj4->hdate= '2012-11-09';
$obj4->hours= 4;

$obj5->eid = 55;
$obj5->make= "Clark";
$obj5->pid= 4;
$obj5->hdate= '2012-11-10';
$obj5->hours= 5;

$obj6->eid = 56;
$obj6->make= "Toyota";
$obj6->pid= 4;
$obj6->hdate= '2012-11-07';
$obj6->hours= 5;

$obj7->eid = 55;
$obj7->make= "Clark";
$obj7->pid= 4;
$obj7->hdate= '2012-11-10';
$obj7->hours= 2;


$testdata = array($obj1,$obj2,$obj3,$obj4,$obj5,$obj6,$obj7);

$output = array();
foreach($testdata as $result){
    //add equipment to eq (only if its not filled yet)
    if(!isset($output["equipment"][$result->eid])) {
        $output["equipment"][$result->eid]["make"] = $result->make;
//add other equipment data here
    }
    if($result->hdate == NULL || $result->hdate == '')
        continue;
    //add log to project for specific equipment
    if($result->pid == '' || $result->pid == NULL)
        $result->pid = 0;
    if(isset($output["projects"][$result->pid][$result->eid][$result->hdate])) {
        $output["projects"][$result->pid][$result->eid][$result->hdate] += $result->hours;
    } else {
        $output["projects"][$result->pid][$result->eid][$result->hdate] = $result->hours;
    }
}
var_dump($output);

outputs:

Code:

array(2) {
  ["equipment"]=>
  array(3) {
    [54]=>
    array(1) {
      ["make"]=>
      string(5) "Titan"
    }
    [55]=>
    array(1) {
      ["make"]=>
      string(5) "Clark"
    }
    [56]=>
    array(1) {
      ["make"]=>
      string(6) "Toyota"
    }
  }
  ["projects"]=>
  array(3) {
    [1]=>
    array(1) {
      [55]=>
      array(3) {
        ["2012-11-07"]=>
        int(5)
        ["2012-11-08"]=>
        int(2)
        ["2012-11-09"]=>
        int(4)
      }
    }
    [4]=>
    array(1) {
      [55]=>
      array(1) {
        ["2012-11-10"]=>
        int(7)
      }
    }
    [0]=>
    array(1) {
      [56]=>
      array(1) {
        ["2012-11-07"]=>
        int(5)
      }
    }
  }
}

Now i can use the above for nice output like this:

Code:

foreach($output["projects"] as $pid=>$project){

    echo "Project #$pid , equipment used:\n";
    foreach($project as $eid=>$logs){
        echo " - ".$output["equipment"][$eid]["make"]."\n";
        foreach($logs as $date=>$hours){
            echo "    -".$date.": ".$hours."hours\n";
        }
    }
}

Output will be:
Project #1 , equipment used:
- Clark
-2012-11-07: 5hours
-2012-11-08: 2hours
-2012-11-09: 4hours
Project #4 , equipment used:
- Clark
-2012-11-10: 7hours
- Toyota
-2012-11-07: 5hours

[eluser]fasfad[/eluser]
Edited my post , not sure if it notifies you, because it had some bad code

[eluser]RichM[/eluser]
Interesting. That actually aligns well with another report that I am needing to do that outputs per project. Essentially each user has access to just some of the projects so I could limit the output to just their jobs and sort equipment and usage that way.

[eluser]RichM[/eluser]
Ok, back to the original query/output. I've got it outputting properly except for 'some' of the dates.

I'm returning the equipment array as

Code:

$output as $line

and accessing the hours by rolling through the days of the month and using that number

Code:

while($hd<=$days)
                      {                      
                        echo '<td>';
                        if(isset($line["projects"]["$pid"]["$year-$month-$hd"]))
                        {
                        echo $line["projects"]["$pid"]["$year-$month-$hd"];
                        }
                        else
                        {
                        echo '0';
                        }
                        echo '</td>';
                        $hd++;                        
                      }

The problem is that it only works for 2 digit days, of course. So 2012-11-1 is not a proper index as it should be 2012-11-01. Once I hit 2012-11-10 it works properly as you would expect. Is there a way to force the $hd variable to be 2 digit? I'm sending the $days variable into it with the function days_in_month() and passing the month and year.
Or maybe I'm going all the way back to how the dates input into the database?
I'm using jqueryUI's datepicker for the date. I'm thinking I'll just modify the format of the original entry.
I'll say thanks again. The information you've given me has been spot on and has fit perfectly.

[eluser]RichM[/eluser]
Well database won't allow me to save as a single digit without going to just saving as a string. But then the query won't work properly.

Ended up using str_pad() and it's working.