creating a cumulative total column, but not in sequence

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,c gttransactions.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+(cgttransacti ons_1.amount*$pr
ice) AS total, ".
"IF(cgttransactions.bbprice IS NOT NULL,\"B&B\",NULL) AS bb, ".
"(cgttransactions_1.amount*cgttransactions.price)-cgttransac tions.comm AS
selltotal, ".
"((cgttransactions_1.amount*cgttransactions.price)-cgttransa ctions.comm)-(cg
ttransactions_1.stamp+cgttransactions_1.comm+(cgttransaction s_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,c gttransactions_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

Re: creating a cumulative total column, but not in sequence

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

The above should read "in the order of column B"

Re: creating a cumulative total column, but not in sequence

mantrid wrote:

> *****************************
> $qtransactions = "SELECT cgttransactions.aimlisted, cgtcompany.name,
> cgtcompany.code, cgttransactions_1.amount,cgttransactions.amount AS
> sellamount,cgttransactions_1.boughtsold,

First, let's make your SQL query a bit more legible. Remember that in PHP
you DON'T have to end the string on a line break. And any half-decent SQL
engine will accept query strings with line breaks and/or newlines.


$qtransactions = "SELECT
cgttransactions.aimlisted,
cgtcompany.name,
cgtcompany.code,
cgttransactions_1.amount,
cgttransactions.amount AS sellamount,
cgttransactions_1.boughtsold,
IF(cgttransactions.boughtsold=0,cgttransactions.selllinkid,c gttransactions.transactionid)
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*$price) 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) -
(cgttransactions_1.stamp+cgttransactions_1.comm
+(cgttransactions_1.amount*$price)) 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 ) ";


Now, let me try to understand that...

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

Luchar por la paz es como follar por la virginidad

Re: creating a cumulative total column, but not in sequence

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:

$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:

ksort($data);
?>

See? Now the array itself is ordered by the values of ColC. Let's
accumulate...

$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:


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:

foreach($data as $item)
{
echo "
{$item['datetime']}
{$item['ColB']}
{$item['ColC']}
{$item['accum']}
";
}
?>


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"

Re: creating a cumulative total column, but not in sequence

..oO(Iván Sánchez Ortega)

>BTW, when you're programming, try to get timestamps instead of DATETIMEs.

Depends.

>handling DATETIMEs can be nasty if you don't pay enough attention,

So can Unix timestamps.

>but
>handling integer numbers (that can be fed to date() later)

DATE_FORMAT() exists.

>is usually
>easier.

Depends on what you're going to do with it. For me DATE/DATETIME is much
better since it's not restricted in its range and can be used with all
of MySQL's date and time functions without having to convert it first.
Internally all my dates are stored with a native date type. If I should
really need a Unix timestamp in PHP, I return it with UNIX_TIMESTAMP()
from the DB.

YMMV.

Micha

Re: creating a cumulative total column, but not in sequence

Excellent thanks for that

To answer your questions

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

I did this as the only way I could think of to get my data displayed as I
wanted. This will be difficult to explain, but may be worth it as you may
offer a better solution. To explain its probably best to give some examples
of data and how I display it.

I wish the data to be displayed as follows

pdate pamount pprice sdate samount sprice
14/7/05 20 0.11 3/5/07 20 0.20
13/7/05 30 0.14 3/5/07 30 0.20

This data is stored in the table as follows

transactionid type linkid date amount price
1 p 1 14/7/05 20 0.11
2 p 1 13/7/05 30 0.14
3 s 1 3/5/07 50 0.20

So its basically all to do with displaying p transactions with its
associated s transaction in the same row.
Initially my table structurte was such that I had a separate field for all s
values and p values so I didnt have to do this but I had to change it for
other reasons, that I wont go into now.



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

Just colum names i made up in the example to explain the layout. they are
not in the actual code.
colA is cgttransactions.datetime colB is yrsheld (which is basically
DATEDIFF(cgttransactions.datetime,cgttransactions_1.datetime ) rounded to
nearest year) and colC is profnloss.

My ordering at the moment is

ORDER BY
DATEDIFF(cgttransactions.datetime,cgttransactions_1.datetime ) ";

should be

ORDER BY
cgttransactions.datetime;

and the cumulation should be by
DATEDIFF(cgttransactions.datetime,cgttransactions_1.datetime )



Ian

Re: creating a cumulative total column, but not in sequence

Iván Sánchez Ortega wrote:
>
> That's the first time I've ever seen a inner join of a table with itself.
> Geez.
>

Try comp.databases.mysql - it's quite common over there. Try searching
on "strawberry query" for some examples.

Nothing against you, Iván - you're an excellent PHP programmer, IMHO.

But it's a very big reason why I recommend people go to that group for
SQL questions to the appropriate database newsgroup - people there are
experts in SQL (but not experts on PHP :-) ).

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Re: creating a cumulative total column, but not in sequence

Jerry Stuckle wrote:

> Try comp.databases.mysql - it's quite common over there. Try searching
> on "strawberry query" for some examples.

Will do. I've read a couple of books on DB design and I cannot recall that
technique.

> Nothing against you, Iván - you're an excellent PHP programmer, IMHO.

Thanks :-)

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

Un ordenador no es un televisor ni un microondas, es una herramienta
compleja.

Re: creating a cumulative total column, but not in sequence

"Jerry Stuckle" wrote in message
news:ofOdnU-xsrQB8enanZ2dnUVZ_j-dnZ2d@comcast.com...
> Iván Sánchez Ortega wrote:
> >
> > That's the first time I've ever seen a inner join of a table with
itself.
> > Geez.
> >
>
> Try comp.databases.mysql - it's quite common over there. Try searching
> on "strawberry query" for some examples.
>
> Nothing against you, Iván - you're an excellent PHP programmer, IMHO.
>
> But it's a very big reason why I recommend people go to that group for
> SQL questions to the appropriate database newsgroup - people there are
> experts in SQL (but not experts on PHP :-) ).
>

Jerry
did a search for comp.databases.mysql through outlook express but it cant
find it. Any ideas why?
Ian

Re: creating a cumulative total column, but not in sequence

My ISP (Australias Largest) refuses to carry it as well. Unfortunately that
leaves the option of paying for a seperate newsserver account.

Kye.

"mantrid" wrote in message
news:CS6dj.17349$745.9872@newsfe1-win.ntli.net...
>
> "Jerry Stuckle" wrote in message
> news:ofOdnU-xsrQB8enanZ2dnUVZ_j-dnZ2d@comcast.com...
>> Iván Sánchez Ortega wrote:
>> >
>> > That's the first time I've ever seen a inner join of a table with
> itself.
>> > Geez.
>> >
>>
>> Try comp.databases.mysql - it's quite common over there. Try searching
>> on "strawberry query" for some examples.
>>
>> Nothing against you, Iván - you're an excellent PHP programmer, IMHO.
>>
>> But it's a very big reason why I recommend people go to that group for
>> SQL questions to the appropriate database newsgroup - people there are
>> experts in SQL (but not experts on PHP :-) ).
>>
>
> Jerry
> did a search for comp.databases.mysql through outlook express but it cant
> find it. Any ideas why?
> Ian
>
>

Re: creating a cumulative total column, but not in sequence

mantrid wrote:
> "Jerry Stuckle" wrote in message
> news:ofOdnU-xsrQB8enanZ2dnUVZ_j-dnZ2d@comcast.com...
>> Iván Sánchez Ortega wrote:
>>> That's the first time I've ever seen a inner join of a table with
> itself.
>>> Geez.
>>>
>> Try comp.databases.mysql - it's quite common over there. Try searching
>> on "strawberry query" for some examples.
>>
>> Nothing against you, Iván - you're an excellent PHP programmer, IMHO.
>>
>> But it's a very big reason why I recommend people go to that group for
>> SQL questions to the appropriate database newsgroup - people there are
>> experts in SQL (but not experts on PHP :-) ).
>>
>
> Jerry
> did a search for comp.databases.mysql through outlook express but it cant
> find it. Any ideas why?
> Ian
>
>
>

Maybe your news server doesn't carry it. Try asking them to do so.
I've found most are quite helpful.

Other alternatives are google groups (yuck!) or a paid service. For the
latter, I recommend individual.net - only 10 euros per year. No binary
groups, but that suits me fine.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Re: creating a cumulative total column, but not in sequence

Kye wrote:
> "mantrid" wrote in message
> news:CS6dj.17349$745.9872@newsfe1-win.ntli.net...
>> "Jerry Stuckle" wrote in message
>> news:ofOdnU-xsrQB8enanZ2dnUVZ_j-dnZ2d@comcast.com...
>>> Iván Sánchez Ortega wrote:
>>>> That's the first time I've ever seen a inner join of a table with
>> itself.
>>>> Geez.
>>>>
>>> Try comp.databases.mysql - it's quite common over there. Try searching
>>> on "strawberry query" for some examples.
>>>
>>> Nothing against you, Iván - you're an excellent PHP programmer, IMHO.
>>>
>>> But it's a very big reason why I recommend people go to that group for
>>> SQL questions to the appropriate database newsgroup - people there are
>>> experts in SQL (but not experts on PHP :-) ).
>>>
>> Jerry
>> did a search for comp.databases.mysql through outlook express but it cant
>> find it. Any ideas why?
>> Ian
>>
>>
>
>
>
> My ISP (Australias Largest) refuses to carry it as well.
Unfortunately > that leaves the option of paying for a seperate
newsserver account.
>
> Kye.
>

(Top posting fixed)

See my note to Ian - there are both free and inexpensive alternatives.

P.S. Please don't top post. Thanks.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Re: creating a cumulative total column, but not in sequence

Jerry Stuckle said:
> mantrid wrote:
>> "Jerry Stuckle" wrote in message
>> news:ofOdnU-xsrQB8enanZ2dnUVZ_j-dnZ2d@comcast.com...
>>> Iv=E1n S=E1nchez Ortega wrote:
>>>> That's the first time I've ever seen a inner join of a table with
>> itself.
>>>> Geez.
>>>>
>>> Try comp.databases.mysql - it's quite common over there. Try searchi=
ng
>>> on "strawberry query" for some examples.
>>>
>>> Nothing against you, Iv=E1n - you're an excellent PHP programmer, IMH=
O.
>>>
>>> But it's a very big reason why I recommend people go to that group fo=
r
>>> SQL questions to the appropriate database newsgroup - people there ar=
e
>>> experts in SQL (but not experts on PHP :-) ).
>>>
>>
>> Jerry
>> did a search for comp.databases.mysql through outlook express but it c=
ant
>> find it. Any ideas why?
>> Ian
>>
>>
>>
>=20
> Maybe your news server doesn't carry it. Try asking them to do so. I'v=
e=20
> found most are quite helpful.
>=20
> Other alternatives are google groups (yuck!) or a paid service. For th=
e=20
> latter, I recommend individual.net - only 10 euros per year. No binary=
=20
> groups, but that suits me fine.
>=20
http://motzarella.org

Free news service, you just have to sign up.

~A!

--=20
Anthony Levensalor
anthony@mypetprogrammer.com

Only two things are infinite, the universe and human stupidity,
and I'm not sure about the former. - Albert Einstein

Re: creating a cumulative total column, but not in sequence

> 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:
>
> > $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:
>
> > ksort($data);
> ?>
>
> See? Now the array itself is ordered by the values of ColC. Let's
> accumulate...
>
> > $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:
>
>
> > 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:
>
> > foreach($data as $item)
> {
> echo "
> {$item['datetime']}
> {$item['ColB']}
> {$item['ColC']}
> {$item['accum']}
> ";
> }
> ?>
>
>
> 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"

Re: creating a cumulative total column, but not in sequence

mantrid wrote:

[...]
> I haven't used arrays much so im probably overlooking something obvious.
[...]

You overlooked something, but it is not so obvious.

I said:

foreach($data as $key=>&$item)

but you wrote:

foreach($data as $key=>$item)


See the difference? Now, your homework is to go back to the PHP manual and
read everything about references and the "foreach" language construct ;-)

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

No, mamá, no...œš·GÛ{;È... NO CARRIER

Re: creating a cumulative total column, but not in sequence

"Iván Sánchez Ortega"
wrote in message news:fl39jc$3pl$1@hercules.cohp1...
> mantrid wrote:
>
> [...]
> > I haven't used arrays much so im probably overlooking something obvious.
> [...]
>
> You overlooked something, but it is not so obvious.
>
> I said:
>
> foreach($data as $key=>&$item)
>
> but you wrote:
>
> foreach($data as $key=>$item)
>
>
> See the difference? Now, your homework is to go back to the PHP manual and
> read everything about references and the "foreach" language construct ;-)
>
> --
> ----------------------------------
> Iván Sánchez Ortega -ivansanchez-algarroba-escomposlinux-punto-org-
>
> No, mamá, no...œš·GÛ{;È... NO CARRIER


I purposely left the & out as initially it gave an error (below) with it, so
I thought it was just a typo

Parse error: syntax error, unexpected '&', expecting T_VARIABLE or '$' in
/home/iddsoftw/public_html/cgtcalc/profitloss.php on line 368

Is it to do with the php version I use, which I believe is 4.something
Also I presume the

Re: creating a cumulative total column, but not in sequence

> >
> > See the difference? Now, your homework is to go back to the PHP manual
and
> > read everything about references and the "foreach" language construct
;-)
> >
> > --
> > ----------------------------------
> > Iván Sánchez Ortega -ivansanchez-algarroba-escomposlinux-punto-org-
> >
> > No, mamá, no...os·GÛ{;È... NO CARRIER
>
>
> I purposely left the & out as initially it gave an error (below) with it,
so
> I thought it was just a typo
>
> Parse error: syntax error, unexpected '&', expecting T_VARIABLE or '$' in
> /home/iddsoftw/public_html/cgtcalc/profitloss.php on line 368
>
> Is it to do with the php version I use, which I believe is 4.something
> Also I presume the
>
>

ok
I looked at the manual. and it does look like its due to my version of php.
Seems using & to modify array's elements is only available in version 5.
What should I do?
Ian
ps I will look for the answer in the meantime myself, so you dont think Im
lazy. But its all starting to look complicated with this referencing stuff
Ian

Re: creating a cumulative total column, but not in sequence

mantrid wrote:

> I looked at the manual. and it does look like its due to my version of
> php. Seems using & to modify array's elements is only available in version
> 5. What should I do?

First, upgrade to PHP5 ;-)

(Remember that PHP4's end-of-life is near, BTW)

Second, modify the array elements by using the array name and the relevant
key:

Instead of

foreach ($array as $key=>&$item)
{
$item['foo'] = $whatever;
}

use something like:

foreach ($array as $key=>$item)
{
$array['key']['foo'] = $whatever;
}

*And* leave a nice comment about changing that when upgrading to PHP5. Don't
forget about those little things, try to keep your code clean.

> ps I will look for the answer in the meantime myself, so you dont think Im
> lazy. But its all starting to look complicated with this referencing stuff

Referencing stuff is not that complicated (it gets very easy if you know are
trained in C pointers), but it's pretty good to handle situations like
this, and it's indispensable if you want to do semi-complex stuff with
classes and objects.

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

To be or not to be.
-- Shakespeare
To do is to be.
-- Nietzsche
To be is to do.
-- Sartre
Do be do be do.
-- Sinatra

Re: creating a cumulative total column, but not in sequence

"Iván Sánchez Ortega"
wrote in message news:fl3rop$dge$1@hercules.cohp1...
> mantrid wrote:
>
> > I looked at the manual. and it does look like its due to my version of
> > php. Seems using & to modify array's elements is only available in
version
> > 5. What should I do?
>
> First, upgrade to PHP5 ;-)
>
> (Remember that PHP4's end-of-life is near, BTW)
>
> Second, modify the array elements by using the array name and the relevant
> key:
>
> Instead of
>
> foreach ($array as $key=>&$item)
> {
> $item['foo'] = $whatever;
> }
>
> use something like:
>
> foreach ($array as $key=>$item)
> {
> $array['key']['foo'] = $whatever;
> }
>

Ok Ive been tinkering with your solution and my ignorance of arrays has
stumped me again. It looks like I should be recreating the array along the
lines of (pseudo code)
array (oldelement1,oldelement2)= newarray (newelement1,newelement2)
If element1 is the key this will remain the same and element2 is the
cumulative total will change. So I tried

$data['key']['accum'] = $key =>$accum += $item['profnloss'];
initially, but it didnt work

I ended up with

foreach($data as $key=>$item)
{
if ($item['profnloss'] > 0)
$data['key']['accum'] = $accum += $item['profnloss'];
else
$data['key']['accum'] = $accum;
}

This sort of worked a bit in that in the displayed results the first line
had no values exept the overall cumulative total. ie
Held Order- Sell Date- Profit/Loss- Cumulative Total-1005.06506667


and all the other rows had all the other data exept the cumulated total. ie
Held Order-371 Sell Date-2007-05-03 16:14:00 Profit/Loss--39.98 Cumulative
Total-
Held Order-688 Sell Date-2007-06-01 12:42:00 Profit/Loss-158.5254068123
Cumulative Total-
Held Order-377 Sell Date-2007-06-11 16:21:00 Profit/Loss-80.9849999999997
Cumulative Total-
Held Order-750 Sell Date-2007-08-02 12:07:00 Profit/Loss-548.267993187701
Cumulative Total-

Again excuse my ignorance. What have I got wrong
Ian

Re: creating a cumulative total column, but not in sequence

mantrid wrote:

> Ok Ive been tinkering with your solution and my ignorance of arrays has
> stumped me again.

Do a after and before you re-sort the array by
date. You should be able to look where the data goes wrong.

Hint: if you're seeing the results through a web browser (as HTML), pop
a "

" before, and a "

Re: creating a cumulative total column, but not in sequence

"Iván Sánchez Ortega"
wrote in message news:fl6jjl$b55$1@hercules.cohp1...
> mantrid wrote:
>
> > Ok Ive been tinkering with your solution and my ignorance of arrays has
> > stumped me again.
>
> Do a after and before you re-sort the array by
> date. You should be able to look where the data goes wrong.
>
> Hint: if you're seeing the results through a web browser (as HTML), pop
> a "

" before, and a "

Re: creating a cumulative total column, but not in sequence

mantrid wrote:

> [key] => Array
> (
> [accum] => 1005.06506667
> )

It's $data[$key], not $data['key']. You want to access to a given array
item, given its key.

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

Un ordenador no es un televisor ni un microondas, es una herramienta
compleja.

Re: creating a cumulative total column, but not in sequence

"Iván Sánchez Ortega"
wrote in message news:fl6njf$crd$1@hercules.cohp1...
> mantrid wrote:
>
> > [key] => Array
> > (
> > [accum] => 1005.06506667
> > )
>
> It's $data[$key], not $data['key']. You want to access to a given array
> item, given its key.
>
> --
> ----------------------------------
> Iván Sánchez Ortega -ivansanchez-algarroba-escomposlinux-punto-org-
>
> Un ordenador no es un televisor ni un microondas, es una herramienta
> compleja.



OK Thats done it :)
Thank for all your help Iván

Re: creating a cumulative total column, but not in sequence

mantrid wrote:
> 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?
Hi Ian,

How about this for an approach?

1) Read the data in the order needed to compute the cumulative total.

2) As each row of the table is generated, do not emit it, but store the
whole line (from to ) in an array, with the the date value as
the index. (Use yy/mm/dd format for the key, so it will sort.)

3) Sort the array keys into a new array.

4) Foreach key in new array, print the value at that index from the
array created in step 2.

5) Close the table.

That way you don't need to read the database twice.

If your table is so large that it needs to be displayed on more than one
page, then you will have to compute the cumulative values and store them
in the database. Make sure the cumulative values are updated when any of
the colC values are changed.

Display of the sub-set required can then be accomplished with order by
and limit clauses, and the use of SQL_CALC_FOUND_ROWS will tell you if
NEXT and/PREV buttons are needed for this page.

Regards

Ian

Re: creating a cumulative total column, but not in sequence

>
> How about this for an approach?
>
> 1) Read the data in the order needed to compute the cumulative total.
>
> 2) As each row of the table is generated, do not emit it, but store the
> whole line (from to ) in an array, with the the date value as
> the index. (Use yy/mm/dd format for the key, so it will sort.)
>
> 3) Sort the array keys into a new array.
>
> 4) Foreach key in new array, print the value at that index from the
> array created in step 2.
>
> 5) Close the table.
>
> That way you don't need to read the database twice.
>
> If your table is so large that it needs to be displayed on more than one
> page, then you will have to compute the cumulative values and store them
> in the database. Make sure the cumulative values are updated when any of
> the colC values are changed.
>
> Display of the sub-set required can then be accomplished with order by
> and limit clauses, and the use of SQL_CALC_FOUND_ROWS will tell you if
> NEXT and/PREV buttons are needed for this page.
>
> Regards
>
> Ian

Thanks Ian
This is what Ivan has also suggested on the thread on comp.lang.php. Dont
know why those messages are not showing up here on alt.comp.lang.php.
Ian

Re: creating a cumulative total column, but not in sequence

"Iván Sánchez Ortega"
wrote in message news:fl6njf$crd$1@hercules.cohp1...
> mantrid wrote:
>
> > [key] => Array
> > (
> > [accum] => 1005.06506667
> > )
>
> It's $data[$key], not $data['key']. You want to access to a given array
> item, given its key.
>
> --
> ----------------------------------
> Iván Sánchez Ortega -ivansanchez-algarroba-escomposlinux-punto-org-
>
> Un ordenador no es un televisor ni un microondas, es una herramienta
> compleja.


I ve just discovered a problem.
When using

foreach($data as $item){
$data2[$item['selldate'] ] = $item;
}

ksort($data2);

to create the second array ordered by selldate, it is missing out any
records with duplicate selldates. How do I tell it to include duplicates?
Ian

Re: creating a cumulative total column, but not in sequence

mantrid wrote:

> it is missing out any records with duplicate selldates. How do I tell it
> to include duplicates?

You cannot, it's how ordered maps work.

You can, however, use a custom ordering function (see usort() ) and forget
creating a second array altogether.

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

MSN:i_eat_s_p_a_m_for_breakfast@hotmail.com
Jabber:ivansanchez@jabber.org ; ivansanchez@kdetalk.net

Re: creating a cumulative total column, but not in sequence

"Iván Sánchez Ortega"
wrote in message news:flb9cc$s85$1@hercules.cohp1...
> mantrid wrote:
>
> > it is missing out any records with duplicate selldates. How do I tell it
> > to include duplicates?
>
> You cannot, it's how ordered maps work.
>
> You can, however, use a custom ordering function (see usort() ) and forget
> creating a second array altogether.
>
> --
> ----------------------------------
> Iván Sánchez Ortega -ivansanchez-algarroba-escomposlinux-punto-org-
>
> MSN:i_eat_s_p_a_m_for_breakfast@hotmail.com
> Jabber:ivansanchez@jabber.org ; ivansanchez@kdetalk.net

Thanks again Ivan
Looked at usort and the example given. I applied it to my situation and came
up with

*******************
while($row = mysql_fetch_array($transactions)) {
extract($row);
$data[ $row['heldlength'] ] = $row;
}

ksort($data);
$accum = 0;
$heldorder = 0;
foreach($data as $key=>$item) {

if ($item['profnloss'] > 0){
$data[$key]['accum'] = $accum += $item['profnloss'];
$data[$key]['heldorder'] = $heldorder += 1;

}else{
$data[$key]['accum'] = $accum;
$data[$key]['heldorder'] = $heldorder += 1;
}
}

function usort_cmp($a, $b) {
if ($a == $b) return 0;
return ($a > $b) ? -1 : 1;
}


usort($data, "usort_cmp");

******************************
Too my suprise, it works perfectly. Ever heard the one where you give a
group of monkeys pens and paper and given enough time they will write the
complete works of shakespere. I think something similar has happened here. I
ve solved it but havent a clue how?
I am particularly puzzled by what the function

function usort_cmp($a, $b) {
if ($a == $b) return 0;
return ($a > $b) ? -1 : 1;
}

is doing exactly. Particularly where argument $b comes from, and how it and
the returned values -1,0 and 1 are affecting the ordering of the array.

If anyone wants to explain this to me in simple english I'd be grateful. If
not, no worries Im just glad it works. Been on this for 4 days now.
Ian

Re: creating a cumulative total column, but not in sequence

mantrid wrote:

> I am particularly puzzled by what the function [...]
> is doing exactly. Particularly where argument $b comes from, and how it
> and the returned values -1,0 and 1 are affecting the ordering of the
> array.
[...]
> If anyone wants to explain this to me in simple english I'd be grateful.
> If not, no worries Im just glad it works. Been on this for 4 days now.

I could explain, but I'm growing a bit worn out by this loooong thread. And
I'd give you a headache too :-P

So: grab the nearest book on algorithmics, and open it up on the chapter
about sorting algorithms. Every sort algorithm depends on a comparison
function, to know if a item on the soon-to-be-sorted-out-list is "bigger"
or "smaller" than other item.

Really, do grab a book on algorithmics.



BTW, my solution was along the lines of *not* touching the array indexes and
declaring the comparison function something like this:

function usort_cmp($a, $b)
{
return ($a['date'] > $b['date'])
}

Reason behind this: you're comparing arrays (a set of id, date, companyid,
price, etc etc). One of those arrays is bigger than the other if the date
of the first array is bigger than the date of the second array.

Got the idea?



> Ever heard the one where you give a group of monkeys pens and paper and
> given enough time they will write the complete works of shakespere.

No, you're wrong. It's "old typewriters" ;-)

http://en.wikipedia.org/wiki/Infinite_monkey_theorem

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

Proudly running Debian Linux with 2.6.22-3-amd64 kernel, KDE 3.5.8, and PHP
5.2.4-2 generating this signature.
Uptime: 03:28:40 up 39 days, 13:44, 4 users, load average: 0.53, 0.79,
0.77