Running Total in a PHP table

[Holoverse]

Hi,

 

Im very new to php so this may seem like an ultra noob question but here it goes.

 

Ok Im pulling a date, and a percentage from a table with this code.

SELECT date, percent FROM unitprice WHERE unitprice > number

 

Then fetching the array, and I want to display it neatly in an HTML table.

BUT theres a catch.....

In a third column of this same table, I would like to insert a previously declared variable into the first cell of the 3rd column, and  then have this variable  multiply by the percent to its left, down the third column, like a running balance.

 

So basically I want the HTML table to display this....(rough values, obviously these would be decimals)

 

Date, Percentage, Balance

Feb 2      1.00        404

Feb 3      1.00        408

Feb 4      2.00        412

Feb 5      1.00        416

 

Where date and percentage came from a table called unitprice and the balance columns first cell came from a variable $original and then is multiplied by the percent for each row, running balance style.  Like a compound calculator kindof.

 

Is this too complicated to do

 

Thanks

 

 

[teamatomic]

Something like this will work, may need a bit of tweaking but I think the idea is right.

foreach($array as $row)

{

$row2=$row[2]*$row[1];

echo "$row[0] -- $row[1] -- $row2<br>";

}

 

 

HTH

Teamatomic

[Holoverse]

I appreciate your reply, and it makes sense to me, but I also need the code for getting the first array into a table in the first place.

 

Like I mentioned, Im an absolute noob, that is trying to self learn.

So I need to know how to get my first columns, to echo into a table, and then have that third variable also echo into that table with the multiply applied.

 

So while to a regular experienced coder, your answer would be pretty self explanatory.

I just need a little more guidance as to how to ècho the table`so to speak.

And how to use this statement you provided in that code.

As right now, I have no clue where to place it really.

 

As far as my current `experience` on the matter goes right now.

All I know is that I need a while statement, to get my array to echo repeatedly into a table until its finished.

But how I would nest that 3rd column variable into this while statement, and then multiply its value is totally beyond me.

And that answer you provided doesnt provide enough context for me to understand where to put it really.

 

Its not your fault either.

Its totally mine.  I apologize.

 

But hopefully you can expand a bit further 

 

 

[Goafer]

$sql = "SELECT date, percent FROM table";   // define query
$query = mysql_query($sql) or trigger_error("Error Code");  // perform query

$table = "<table>";  // begin table

do { // fill in table data
$table .= "<tr>";
$table .=  "<td>".$row['date']."</td><td>".$row['percent']."</td>";  // fills in the row until each field is entered (date/percent)
        $table .= "<td>".$total = $total*$row['percent']."</td>";
$table .=  "</tr>";
} while ($row = mysql_fetch_array($query);  // finish filling in table when $row is finished

$table .= "</table>";  // close table

return $table;

 

This should just about do the trick. A simple while loop to run through each row of the table and for each row, have the details entered into the table.  if $total is defined, then each row it will be multiplied by the new percent.

 

This code was stripped from something I have and edited for your specs, so might need a tiny tweek but i'd say thats about right.

[Holoverse]

Thanks, that almost worked.

Well the table echoed fine and the two columns populated just fine, but the multiply column just spat out all zeros.

and I can see why.

 

Because Im declaring the variable first $total = 400

Then when we run that DO statement, the first row it outputs is empty because it hasnt fetched the array yet. So it outputs one row, with nothing in column 1 and two, and then multiplies that 400 by 0.  To spit out zero.

Then from now own the $total = 0 and will always equal zero all the way down the list.

 

So the rest of Column 1 and 2 start populating, but now that column 3 has become zero from that first row, it remains zero.

 

So I have to figure out how to get that first variable into the table without multiplying it for that first line.

 

Do you get what Im saying 

 

[Holoverse]

Figured it out....

This worked.

 

I ende up echoing the first line.

 

Then running the DO statement to input the rest.

Worked like a charm

Though, theres probably a less cluttered way to do it.

Oh well. 

 

$sql = "SELECT * FROM unitpricedataset";  // define query

$query = mysql_query($sql) or trigger_error("Error Code");  // perform query

$original = 400;

 

$data2=mysql_fetch_array($query);

 

$table = "<table><tr><td>".$data2['DATE']."</td><td>$".$data2['UNITPRICE']."</td><td>" .$data2['PERCENTAGE']/'100'."</td><td>".$original =$original*$data2['UNITPRICE']."</td></tr>";  // begin table

 

do{

$table .=  "<td>".$data2['DATE']."</td><td>$".$data2['UNITPRICE']."</td><td>" .$data2['PERCENTAGE']/'100'."</td>"; 

        $table .= "<td>".$original = $original+($original*($data2['PERCENTAGE']/'100'))."</td>";

$table .=  "</tr>";

} while ($data2 = mysql_fetch_array($query)); 

 

$table .= "</table>";  // close table

echo $table;

[Goafer]

Found the problem:

 

while - loops through a block of code while a specified condition is true

do...while - loops through a block of code once, and then repeats the loop as long as a specified condition is true

 

so basically, the previous way, wasn't performing the $row query until after the table had been created and the first row had been populated

 

This new way will run the query first, and then populate accordingly:

 

$table = "<table>";  // begin table

while ($row = mysql_fetch_array($query)  {
$table .= "<tr>";
$table .=  "<td>".$row['date']."</td><td>".$row['percent']."</td>"; (date/percent)
        $table .= "<td>".$total = $total*$row['percent']."</td>";
$table .=  "</tr>";
} 

$table .= "</table>";

 

This should remove the "cluttering" and make things a bit smoother and when you look back at it, it will be easier to read

[Holoverse]

Thanks man, that worked.