|
|
Posted by mantrid on 12/28/07 16:33
> Now, about the matter at hand:
>
> mantrid wrote:
> > This is easy enough. However, I need to display the data ordered by date
> > but cumulated in the order of column C
>
> So we come back to array manipulation 101.
>
>
> First, get *all* the data into an array. *If* the data coming out from
that
> SQL monster-query is too large, you might as well think about optimizing
> the DB schema for this particular problem.
>
> So:
>
> <?php
> $data = array();
> // Notice there is no need for the =& operator.
> while($row = mysql_fetch_array($transactions))
> {
> // We'll suppose 'ColC' as the name of the column holding data to
be shown
> // in the third column.
> $data[ $row['ColC'] ] = $row;
> }
> ?>
>
> OK, we got all the data in a pretty and nice ordered map. Now let's sort
> that map by the key:
>
> <?php
> ksort($data);
> ?>
>
> See? Now the array itself is ordered by the values of ColC. Let's
> accumulate...
>
> <?php
> $accum = 0;
> foreach($data as $key=>&$item)
> {
> // Notice there is no need for the silly
> // "$cumtot = $cumtot" statement.
> // And, while we're holding an accumulator, we'll create a new
> // item in the array. I just love C-like assignations:
> if ($item['profloss'] > 0)
> $item['accum'] = $accum += $item['profloss'];
> else
> $item['accum'] = $accum;
> }
> ?>
>
>
> Yeah, we got our cumulative numbers. Let's re-order by date:
>
>
> <?php
> foreach($data as $item)
> {
> $data2[ $item['datetime'] ] = $item;
> }
> unset($data);
> ksort($data2);
> ?>
>
> You could do the same, using less memory, via usort() and a small
> lambda-function that seeks inside the array for the dates (and the date
> diffs). We'll let lambda-functions skip into another thread this time...
>
> BTW, when you're programming, try to get timestamps instead of DATETIMEs.
> handling DATETIMEs can be nasty if you don't pay enough attention, but
> handling integer numbers (that can be fed to date() later) is usually
> easier.
>
> We now got the array ordered by date, with cumulative totals. Just display
> the data:
>
> <?php
> foreach($data as $item)
> {
> echo "<tr>
> <td>{$item['datetime']}</td>
> <td>{$item['ColB']}</td>
> <td>{$item['ColC']}</td>
> <td>{$item['accum']}</td>
> </tr>";
> }
> ?>
>
>
> M'kay?
>
> --
OK
Ive applied your solution and my code is below. For reasons I cant see
$item['accum'] has no values. everything else is displayed properly and
ordered correctly. I suspect its something to do with adding the cumulative
totals to the array. I havnt used arrays much so im probably overlooking
something obvious.
*******************************
$data = array();
$transactions= mysql_query($qtransactions) or die(mysql_error());
while($row = mysql_fetch_array($transactions)) {
extract($row);
$data[ $row['heldorder'] ] = $row;
}
ksort($data);
$accum = 0;
foreach($data as $key=>$item)
{
if ($item['profnloss'] > 0)
$item['accum'] = $accum += $item['profnloss'];
else
$item['accum'] = $accum;
}
foreach($data as $item){
$data2[ $item['selldatetime'] ] = $item;
}
unset($data);
ksort($data2);
echo"<table>";
foreach($data2 as $item)
{
echo "<tr>
<td>Held Order-{$item['heldorder']}</td>
<td>Sell Date-{$item['selldatetime']}</td>
<td>Profit/Loss-{$item['profnloss']}</td>
<td>Cumulative Total-{$item['accum']}</td>
</tr>";
}
echo"</table>";
*********************************
[Back to original message]
|