|
|
Posted by Ian Hobson on 12/30/07 14:52
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 <tr> to </tr>) 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
Navigation:
[Reply to this message]
|