Page 1 of 1

Outputting multidimensional array

Posted: Mon Jun 20, 2005 7:50 am
by madeqx
I have an array which is generated by:

Code: Select all

SELECT SUM(p.profit) AS profit, e.name, p.time
FROM profit p, emp e
WHERE e.eid=p.eid
AND DATE_FORMAT(p.time, '%M-%Y')='June-2005'
GROUP BY e.name, p.time
ORDER BY e.name
which is put in an array by:

Code: Select all

<?
while($row = mysql_fetch_array($result)) 
	{ 
		$array[$row['name']][$row['time']] = $row['profit']; 
	} 
?>
The array then looks like this:

Code: Select all

Array
(
    &#1111;Alan] =&gt; Array
        (
            &#1111;17-June-2005] =&gt; 5060
            &#1111;18-June-2005] =&gt; 1009
            &#1111;20-June-2005] =&gt; 344
        )

    &#1111;James] =&gt; Array
        (
            &#1111;18-June-2005] =&gt; 1797
            &#1111;19-June-2005] =&gt; 1779.33
        )

    &#1111;John] =&gt; Array
        (
            &#1111;18-June-2005] =&gt; 1266
            &#1111;19-June-2005] =&gt; 907.26
            &#1111;20-June-2005] =&gt; 122
        )

)
This shows the total profit for each employee for each day.
Now, my problem is that I have big issues outputting this propperly to HTML. Ideally, I would like for the employee names to be headers and then on subsequent rows show dates and profits for each employee. Like so:

Code: Select all

&lt;table  border=&quote;0&quote; cellspacing=&quote;0&quote; cellpadding=&quote;1&quote;&gt;
  &lt;tr&gt;
    &lt;td&gt;Date&lt;/td&gt;
	&lt;td&gt;Alan&lt;/td&gt;
    &lt;td&gt;James&lt;/td&gt;
    &lt;td&gt;John&lt;/td&gt;
    &lt;td&gt;SUM&lt;/td&gt;
  &lt;/tr&gt;
  &lt;tr&gt;
    &lt;td&gt;17-June-2005&lt;/td&gt;
    &lt;td&gt;5060&lt;/td&gt;
    &lt;td&gt;0&lt;/td&gt;
    &lt;td&gt;0&lt;/td&gt;
    &lt;td&gt;xxx&lt;/td&gt;
  &lt;/tr&gt;
  &lt;tr&gt;
  	&lt;td&gt;18-June-2005&lt;/td&gt;
    &lt;td&gt;1009&lt;/td&gt;
    &lt;td&gt;1797&lt;/td&gt;
    &lt;td&gt;1266&lt;/td&gt;
    &lt;td&gt;yyy&lt;/td&gt;
  &lt;/tr&gt;
  &lt;tr&gt;
    &lt;td&gt;19-June-2005&lt;/td&gt;
    &lt;td&gt;0&lt;/td&gt;
    &lt;td&gt;1799&lt;/td&gt;
    &lt;td&gt;907&lt;/td&gt;
    &lt;td&gt;xxx&lt;/td&gt;
  &lt;/tr&gt;
  &lt;tr&gt;
    &lt;td&gt;20-June-2005&lt;/td&gt;
    &lt;td&gt;344&lt;/td&gt;
    &lt;td&gt;0&lt;/td&gt;
    &lt;td&gt;122&lt;/td&gt;
    &lt;td&gt;yyy&lt;/td&gt;
  &lt;/tr&gt;
&lt;/table&gt;
As you can see, some employees do not have any profits on certain dates which partly contributes to messing up my output.

If anyone could help me with this I would be absolutely super duper happy and greatful

Posted: Mon Jun 20, 2005 7:55 am
by Syranide
you would just have to set up special cases for when "pay out" is not done and so on. or am I missing the point?

Posted: Mon Jun 20, 2005 7:58 am
by madeqx
Syranide wrote:you would just have to set up special cases for when "pay out" is not done and so on. or am I missing the point?
The thing is that I have very little knowledge of how to output this in HTML. I tried using a mixture of foreach() loops but was a complete mess :) What I need is a description of how I can output the contents of my array into a HTML table as shown above.
Thanks

Posted: Mon Jun 20, 2005 8:13 am
by Syranide
using arbitrary names and layouts.

Code: Select all

echo "<table>";
foreach( $array as $key => $values ) {
    echo "<tr><td>$key</td><td>{$values['name']}</td><td>{$values['age']}</td></tr>";
}
echo "</table>";

Posted: Mon Jun 20, 2005 8:44 am
by madeqx
Thanks for your reply!

The thing is (as you mentioned above, I think) that I only want to output each date once, i.e. something like this: sample table

Any ideas on how I might do this?

Thanks

Posted: Mon Jun 20, 2005 10:13 am
by pickle
This'll be made much easier if you change the way you're making your array:

Code: Select all

while($row = mysql_fetch_array($result))
{
 $array($row['time'][$row['name']] = $row['profit'];
 $users_array[] = $row['name'];
}
You're array will then look like:

Code: Select all

Array
(
  &#1111;17-June-2005] =&gt; Array
               (
                 &#1111;Alan]=&gt;5060
               )
     ... and so on
)
You can output that array then, in a loop. Since, as you said, not everyone has profit in a day, loop through the $users_array to make sure you've got everyone. I'm feeling generous so here's code that should work:

Code: Select all

//output the heading row
echo "<table><tr><td>Date</td>";
foreach($users_array as $username)
{
  echo "<td>$username</td>";
}
echo "</tr>";

//output the data.  Loop through each day
foreach($array as $date=>$profitable_users)
{
 echo "<tr>"
 //loop through each users info for that day.
 //output a blank if no info (which may cause problems in Firefox)
 foreach($users_array as $curr_user)
 {
   echo "<td>".$profitable_users[$curr_user]."</td>";
 }
 //output the sum for the day
 echo "<td>".array_sum($profitable_users)."</td>";
 echo "</tr>";
}
echo "</table>";