Help with multi-dimensional associative array

[arenaninja]

Hey everyone. I'm in the final stage of my thingeee (yay). The last thing I need is to prepare a report, but I'm slightly lost on how to do it. Essentially, I have a query that returns some financial records and need to aggregate the data manually (in PHP).  I'm familiar with the foreach($assoc_array as $array) function, and I'd like to use it to manipulate the output as follows:

• For the first key (instType), this will mark the start of a new table element
  
• For a second key (paymentID), this will mark the start of a new table row element
  
• For a third key, (instrumentID), this will mark a new column (<td>)
  
• Then, I need to sort by two other keys - deptID, userID, accountID
  

How do I accomplish this? Pseudo-code is acceptable (my array is called $data and contains all the keys I listed above)

[silkfire]

Just use nested foreach loops then use an apporpriate sort function for the innermost loop.

[Jessica]

Start trying it and if you get stuck, post code.

[Psycho]

@jesirose: Welcome back! Haven't seen you in forever.

 

@op

I would suggest NOT creating a new Table for each set of "instType". You can certainly do that, but it would complicate the output because you need to close each table. I could take the time to explain why it would be more difficult, but that'd take too long. I'll show you an example without it and if you still feel it's necessary you can try and modify as needed. Anyway, what you basically need to do is use a couple of "flag" variables to detect when there is a change in one of the primary values. Although, typically, you would have the same value types in a row. I've never seen the need to build logic where the row is dynamically determined. Are you going to have different sets of instrumentIDs for each row or are you always going to have the same ones? If the former, the output will be really chaotic, if the latter then you don't want the logic you've asked for.

 

Anyway, based upon what you asked (which may not really be what you need) some sample code could look like this (not tested, sot here might be a couple errors)

$result = mysql_query($query);

$typeFlag = false;
$paymentFlag = false;

//Open table
echo "<table>\n";
//Process records
while($row = mysql_fetch_assoc($result))
{
    //Create new header row if new type
    if($typeFlag != $row['instType'])
    {
        //Close prev data row if open
        if($paymentFlag != false)
        {
            echo "</tr>\n";
            $paymentFlag = false;
        }
        //Display type header
        echo "<tr><th>{$row['typeName']}</th><tr>\n";
        $typeFlag = $row['instType'];
    }

    //Create data row if new payment
    if($paymentFlag != $row['paymentID'])
    {
        //Close prev data row if open
        if($paymentFlag != false)
        {
            echo "</tr>\n";
        }
        echo "<tr>\n";
        $paymentFlag = $row['paymentID']
    }

    echo "<td>{$row['instrumentName']}</td>\n";
}

//Close table
echo "</table>\n";

[Jessica]

@jesirose: Welcome back! Haven't seen you in forever.

 

Thanks I got divorced, got remarried, and now have a 20 week old son. So...I've been a *little* busy. Had some downtime this week and felt like visiting :-P

[arenaninja]

@jesirose: Welcome back! Haven't seen you in forever.

 

@op

I would suggest NOT creating a new Table for each set of "instType". You can certainly do that, but it would complicate the output because you need to close each table. I could take the time to explain why it would be more difficult, but that'd take too long. I'll show you an example without it and if you still feel it's necessary you can try and modify as needed. Anyway, what you basically need to do is use a couple of "flag" variables to detect when there is a change in one of the primary values. Although, typically, you would have the same value types in a row. I've never seen the need to build logic where the row is dynamically determined. Are you going to have different sets of instrumentIDs for each row or are you always going to have the same ones? If the former, the output will be really chaotic, if the latter then you don't want the logic you've asked for.

 

Anyway, based upon what you asked (which may not really be what you need) some sample code could look like this (not tested, sot here might be a couple errors)

$result = mysql_query($query);

$typeFlag = false;
$paymentFlag = false;

//Open table
echo "<table>\n";
//Process records
while($row = mysql_fetch_assoc($result))
{
    //Create new header row if new type
    if($typeFlag != $row['instType'])
    {
        //Close prev data row if open
        if($paymentFlag != false)
        {
            echo "</tr>\n";
            $paymentFlag = false;
        }
        //Display type header
        echo "<tr><th>{$row['typeName']}</th><tr>\n";
        $typeFlag = $row['instType'];
    }

    //Create data row if new payment
    if($paymentFlag != $row['paymentID'])
    {
        //Close prev data row if open
        if($paymentFlag != false)
        {
            echo "</tr>\n";
        }
        echo "<tr>\n";
        $paymentFlag = $row['paymentID']
    }

    echo "<td>{$row['instrumentName']}</td>\n";
}

//Close table
echo "</table>\n";

This seems like a very useful approach, thank you. Something that I hadn't thought about thoroughly last night was the fact that I should probably make a pivot table report, so that I could either pivot using MySQL or do it this way in PHP. For now I believe this approach is probably best. Thanks again.