Posted by Iv�n S�nchez Ortega on 12/27/07 21:12

Iván Sánchez Ortega wrote:

> IF(cgttransactions.boughtsold=0,cgttransactions.selllinkid,
> cgttransactions.transactionid) AS selllinkid,
> cgttransactions_1.datetime,$price AS price,
> cgttransactions.price AS sellprice,


So much better now:


$qtransactions = "SELECT
t.aimlisted,
c.name,
c.code,
t1.amount,
t1.amount AS sellamount,
t1.boughtsold,
IF(t.boughtsold=0,t.selllinkid,t.transactionid) AS selllinkid,
t1.datetime,
$price AS price,
t.price AS sellprice,
t.datetime AS selldatetime,
t1.stamp + t1.comm AS charges,
charges + (t1.amount * $price) AS total,
IF(t.bbprice IS NOT NULL,'B&B',NULL) AS bb,
(t1.amount*t.price) - t.comm AS selltotal,
selltotal - total AS profnloss,
$yrsheld AS yrsheld


FROM cgtcompany c INNER JOIN
(cgttransactions t INNER JOIN cgttransactions AS t1
ON t.transactionid = t1.selllinkid)
ON c.companyid = t.companyid

WHERE $filter AND t.myvarid=$selcdacnt

ORDER BY
t.datetime,
DATEDIFF(t.datetime,t1.datetime) ";


That's the first time I've ever seen a inner join of a table with itself.
Geez.

So, PLEASE clean up your SQL if you don't want to become crazy when
debugging. Oh and, by the way, you are double-checking $price and $filter
and $selcdacnt and $trsheld for SQL injections, right?

Other thing: what are ColA, ColB and ColC in your example???



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?

--
----------------------------------
Iván Sánchez Ortega -ivansanchez-algarroba-escomposlinux-punto-org-

Too smart to settle down, take a job and watch TV after work, spend two
weeks a year at the cottage and go online to find movie listings. Too smart
is too restless and no happiness, ever, without that it's chased by
obsessive maundering moping about what comes next.
Cory Doctorow, "Eastern Standard Tribe"

[Back to original message]