Posted by mantrid on 12/27/07 20:09

Hello
I use a while....loop to extract rows from a mysql database and display them
in a table on a webpage. I also use
$cumtot=$cumtot+$colC
in the loop to add up numbers from colC and display a cumulative total on
the page to.

colA colB colC $cumtot
1/2/07 3 10 10
3/9/07 1 23 33
4/10/07 4 12 45
1/11/07 2 6 51

This is easy enough. However, I need to display the data ordered by date but
cumulated in the order of column C

So the above example should look like

colA colB colC $cumtot
1/2/07 3 10 39
3/9/07 1 23 23
4/10/07 4 12 51
1/11/07 2 6 29

The only thing I can think that may work is to use two select statements,
one ordered by colA and used to display the data. The other ordered by colB
to provide data in correct order to do the cumulation and pass this data
along with an index number to an array which could then be linked somehow to
the first select statement by index no. or somehow use the array directly in
the while loop that displays the data on the page. Can anyone think of a
better method before I attempt this way?
My code is below

*****************************
$qtransactions = "SELECT cgttransactions.aimlisted, cgtcompany.name,
cgtcompany.code, cgttransactions_1.amount,cgttransactions.amount AS
sellamount,cgttransactions_1.boughtsold,
IF(cgttransactions.boughtsold=0,cgttransactions.selllinkid,cgttransactions.t
ransactionid) AS selllinkid, cgttransactions_1.datetime,$price AS price,
cgttransactions.price AS sellprice, cgttransactions.datetime AS
selldatetime, ".
"cgttransactions_1.stamp+cgttransactions_1.comm AS charges,
cgttransactions_1.stamp+cgttransactions_1.comm+(cgttransactions_1.amount*$pr
ice) AS total, ".
"IF(cgttransactions.bbprice IS NOT NULL,\"B&B\",NULL) AS bb, ".
"(cgttransactions_1.amount*cgttransactions.price)-cgttransactions.comm AS
selltotal, ".
"((cgttransactions_1.amount*cgttransactions.price)-cgttransactions.comm)-(cg
ttransactions_1.stamp+cgttransactions_1.comm+(cgttransactions_1.amount*$pric
e)) AS profnloss, ".
"$yrsheld AS yrsheld ".
"FROM cgtcompany INNER JOIN (cgttransactions INNER JOIN cgttransactions AS
cgttransactions_1 ON cgttransactions.transactionid =
cgttransactions_1.selllinkid) ON cgtcompany.companyid =
cgttransactions.companyid ".
"WHERE ".$filter." AND cgttransactions.myvarid=$selcdacnt ".
"ORDER BY
cgttransactions.datetime,DATEDIFF(cgttransactions.datetime,cgttransactions_1
..datetime) ";


while($row =& mysql_fetch_array($transactions)) {
extract($row);

if($profnloss > 0){$cumtot = $cumtot + $profnloss; }else{ $cumtot =
$cumtot;}

CODE TO CREATE TABLE DISPLAYING DATA FROM $row HERE

}
**************************

Thanks
Ian

• Next in forum: Re: creating a cumulative total column, but not in sequence
• Prev in forum: Re: emailing data from my DB...??
• Thread view: creating a cumulative total column, but not in sequence

[Reply to this message]