asked on

php mysql creating totals from multiple tables.

I have some code that lists out items from a mysql database and totals the price from qty or % with a rate, which works great but say there is 3 items listed all looking fine i would like it to total the price and keep the total price in memory so once it has done all items from lots of different .inc files i would have a total cost of the items.

how it looks at moment

Paint
------
BLAST/Prime 10.000 sq m @ 130.00 13000.00

ERECTION
--------------
3 MEN 4 DAYS 120.000 hours @ 23.00 2760.00
CRANE 2 DAYS 20.000 hours @ 35.00 700.00
ACCESS P/FORMS 8.000 hours @ 50.00 400.00

so it looks right calculations wise but i would like next to each category the total but also keep the total im memory so i can add them all together at the bottom to show the complete total. like below

Paint
------
BLAST/Prime 10.000 sq m @ 130.00 13000.00
13000.00

ERECTION
--------------
3 MEN 4 DAYS 120.000 hours @ 23.00 2760.00
CRANE 2 DAYS 20.000 hours @ 35.00 700.00
ACCESS P/FORMS 8.000 hours @ 50.00 400.00
3860.00

hope that makes sense. see code below for each include they all come along the same line just different tables.

<?
$query = "SELECT ID1, erecthrs, erectrate, erectdesc FROM panddest8 WHERE ID1='$panddid'";
$result = mysql_query($query) or die("Error: " . mysql_error());
$count = mysql_num_rows($result);
?>
<? if ($count) {
while($row = mysql_fetch_array($result))
?>

<?php
$query = "SELECT ID1, erecthrs, erectrate, erectdesc FROM panddest8 WHERE ID1='$panddid'";
$result = mysql_query($query) or die("Error: " . mysql_error());
$count = mysql_num_rows($result);
//while($row = mysql_fetch_array($result)){

?>
<tr>
<td><? if ($count) {
while($row = mysql_fetch_array($result))
print"Erection";
} else {
echo "";
}
?></td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr>
<td>---------------</td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<?
$query = "SELECT ID1, erecthrs, erectrate, erectdesc FROM panddest8 WHERE ID1='$panddid'";
$result = mysql_query($query) or die("Error: " . mysql_error());
$count = mysql_num_rows($result);
while($row = mysql_fetch_array($result)){
?>
<tr>
<td><? echo $row['erectdesc'];

      ?></td>
      <td> </td>
      <td> </td>
<td><? echo $row['erecthrs'];

      ?></td>
<td> </td>
      <td>hours</td>
      <td> </td>
      <td>@ <? echo $row['erectrate'];

      ?></td>
      <td> </td>
      <td><?
      $calc2 = ($row['erecthrs'] * $row['erectrate']);
      print"$calc2";
      ?>
      </td>
      <td> </td>
      <td> </td>
      <td> </td>
</tr>
<?
}
?>

<tr>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
      <td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<?
}
?>

Aleksandar Bradarić

Not quite sure which table goes where, but maybe this example can help you:
---
mysql> SELECT * FROM yourTable;
+------+------+
| id | data |
+------+------+
| 1 | 100 |
| 2 | 200 |
| 3 | 400 |
+------+------+
3 rows in set (0.00 sec)

mysql> SET @sum := 0; SELECT id, data, IF(@sum IS NULL, @sum := data, @sum := @sum + data) as sum FROM yourTable;
Query OK, 0 rows affected (0.00 sec)

+------+------+------+
| id | data | sum |
+------+------+------+
| 1 | 100 | 100 |
| 2 | 200 | 300 |
| 3 | 400 | 700 |
+------+------+------+
3 rows in set (0.00 sec)
---

martin69

ASKER

thanks for the example. but i need to it be in php to sum it. as it list them out and does the total but i need a total of all lines of date listed so from your example it would output 1100 then keep that in memory so i can then add the others to it afterwards to make a mass total.

Aleksandar Bradarić

Why 1100? 100+200+400 = 700... And it is kept in memory - run it twice:
---
mysql> SET @sum := 0; SELECT id, data, IF(@sum IS NULL, @sum := data, @sum := @sum + data) as sum FROM yourTable;
Query OK, 0 rows affected (0.00 sec)

+------+------+------+
| id | data | sum |
+------+------+------+
| 1 | 100 | 100 |
| 2 | 200 | 300 |
| 3 | 400 | 700 |
+------+------+------+
3 rows in set (0.00 sec)

mysql> SELECT id, data, IF(@sum IS NULL, @sum := data, @sum := @sum + data) as sum FROM yourTable;
+------+------+------+
| id | data | sum |
+------+------+------+
| 1 | 100 | 800 |
| 2 | 200 | 1000 |
| 3 | 400 | 1400 |
+------+------+------+
3 rows in set (0.00 sec)
---

Let me know if I got it wrong...

martin69

ASKER

its sort of clicking. so if you had two tables called panddest2 and panddest3 with

date like this in each table

panddest2
+-----+--------+-------------+-----------+
|id | id1 | paintarea | paintrate |
+-----+--------+-------------+-----------+
|1 |76795 | 10 | 130 |
+-----+-------+--------------+-----------+
then

+-----+--------+-------------+-----------+
|id | id1 | erecthrs | erectrate |
+-----+--------+-------------+-----------+
|1 |76795 | 120 | 23 |
+-----+-------+--------------+-----------+
|2 |76795 | 20 | 35 |
+-----+-------+--------------+-----------+

i would need to take the paintarea and times by paint rate gives a total if there was more than one entry i would need to do that then the total of all.

then the erecthrs times by erectrate then total both lines or more if needed then keep those two totals in memory so i would have 1300 and 3460 in memory i can add together.

as i think your example is correct i just cant see it.

Aleksandar Bradarić

Here's a test with your data:
---
mysql> SET @total := 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT id, id1, paintarea, paintrate, paintarea * paintrate as sum, IF(@total IS NULL, @total := paintarea * paintrate, @total := @total + paintarea * paintrate) as total FROM panddest2;
+------+-------+-----------+-----------+------+-------+
| id | id1 | paintarea | paintrate | sum | total |
+------+-------+-----------+-----------+------+-------+
| 1 | 76795 | 10 | 130 | 1300 | 1300 |
+------+-------+-----------+-----------+------+-------+
1 row in set (0.00 sec)

mysql> SELECT id, id1, erecthrs, erectrate, erecthrs * erectrate as sum, IF(@total IS NULL, @total := erecthrs * erectrate, @total := @total + erecthrs * erectrate) as total FROM panddest8;
+------+-------+----------+-----------+------+-------+
| id | id1 | erecthrs | erectrate | sum | total |
+------+-------+----------+-----------+------+-------+
| 1 | 76795 | 120 | 23 | 2760 | 4060 |
| 2 | 76795 | 20 | 35 | 700 | 4760 |
+------+-------+----------+-----------+------+-------+
2 rows in set (0.00 sec)
---

The total is: 10 * 130 + 120 * 23 + 20 * 35 = 1300 + 2760 + 700 = 4760. Correct? :)

ASKER CERTIFIED SOLUTION

Aleksandar Bradarić

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

martin69

ASKER

wow thats it. its clicking one last silly question before i award you the point well deserved. just so i get it in my head. the total is a running total every sql statement i through in with the @total will be added to it and so on and the @tbl_total is per item?

thanks for this

Aleksandar Bradarić

> the total is a running total every sql statement i through in with the @total will be added to it and so on and the @tbl_total is per item?

Yes. Until you reset it with `SET @total := 0`. Or until you close the current connection to MySQL - variables are cleared after you log out.

martin69

ASKER

thanks very much.

martin69

ASKER

been pplaying with it for ages. and i cant get the tbl_totals to display anything and the total seems to work as the tbl_total. and trying to get the grand total im total lost now. i know i excepted the concept as the solution. hope you can help.

<?
$query = "SELECT ID1, erecthrs, erectrate, erectdesc FROM panddest8 WHERE ID1='$panddid'";
$result = mysql_query($query) or die("Error: " . mysql_error());
$count = mysql_num_rows($result);
?>
<? if ($count) {
while($row = mysql_fetch_array($result))
?>

<?php
$query = "SELECT ID1, erecthrs, erectrate, erectdesc FROM panddest8 WHERE ID1='$panddid'";
$result = mysql_query($query) or die("Error: " . mysql_error());
$count = mysql_num_rows($result);
//while($row = mysql_fetch_array($result)){

?>
<tr>
<td><? if ($count) {
while($row = mysql_fetch_array($result))
print"Erection";
} else {
echo "";
}
?></td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr>
<td>---------------</td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<?
$query = "SELECT ID1, erecthrs, erectrate, erectdesc, erecthrs * erectrate as sum, IF(@total IS NULL, @total := erecthrs * erectrate, @total := @total + erecthrs * erectrate) as total, @tbl_total := @tbl_total + erecthrs * erectrate as tbl_total FROM panddest8 WHERE ID1='$panddid'";
//$query = "SELECT ID1, erecthrs, erectrate, erectdesc FROM panddest8 WHERE ID1='$panddid'";
$result = mysql_query($query) or die("Error: " . mysql_error());
$count = mysql_num_rows($result);
while($row = mysql_fetch_array($result)){
?>
<tr>
<td><? echo $row['erectdesc'];

      ?></td>
      <td> </td>
      <td> </td>
<td><? echo $row['erecthrs'];

      ?></td>
<td> </td>
      <td>hours</td>
      <td> </td>
      <td>@ <? echo $row['erectrate'];

      ?></td>
      <td> </td>
      <td>
      <?

      echo $row['total'];

      ?>
      </td>
      <td> </td>
      <td> </td>
      <td> </td>
</tr>
<?
}
?>

<tr>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
      <td> </td>
<td> </td>
<td> </td>
<td> </td>
<td>
      <?
      // this is where i would like it to print the grand total
      echo $row['total'];

            ?>
      </td>
<td> </td>
</tr>
<?
}
?>

Aleksandar Bradarić

The SET commands are missing.

martin69

ASKER

i know im a pain sorry. where do i need to do the SET @total bit.

Aleksandar Bradarić

See the post you accepted - do it in that order. The @total gets set only once. The @tbl_total gets set for each new table.

I see that the `while` line is commented out, but just in case pu the @total before it:
---
$query = "SET @total := 0";
$result = mysql_query($query) or die("Error: " . mysql_error());

$query = "SELECT ID1, erecthrs, erectrate, erectdesc FROM panddest8 WHERE ID1='$panddid'";
$result = mysql_query($query) or die("Error: " . mysql_error());
$count = mysql_num_rows($result);

//while($row = mysql_fetch_array($result)){
---

And the @tbl_total would go a bit lower:
---
$query = "SET @tbl_total := 0";
$result = mysql_query($query) or die("Error: " . mysql_error());

$query = "SELECT ID1, erecthrs, erectrate, erectdesc, erecthrs * erectrate as sum, IF(@total IS NULL, @total := erecthrs * erectrate, @total := @total + erecthrs * erectrate) as total, @tbl_total := @tbl_total + erecthrs * erectrate as tbl_total FROM panddest8 WHERE ID1='$panddid'";
---

Works?

martin69

ASKER

nearly nearly. now i get all the totals one after the other so i get:-

Erection
---------------
3 Men times 4 days      120             hours             @ 23             2760
Crane X 2 Days             20             hours             @ 35             700

Access Platform             8             hours             @ 50             400
On/Off Hire             2             hours             @ 100             200
                                                                            2760346038604060 ( i only need the last one)

this is my code so far.

<?
$query = "SELECT ID1, erecthrs, erectrate, erectdesc FROM panddest8 WHERE ID1='$panddid'";
$result = mysql_query($query) or die("Error: " . mysql_error());
$count = mysql_num_rows($result);
?>
<? if ($count) {
while($row = mysql_fetch_array($result))
?>

<?php
$query = "SELECT ID1, erecthrs, erectrate, erectdesc FROM panddest8 WHERE ID1='$panddid' GROUP BY ID1";
$result = mysql_query($query) or die("Error: " . mysql_error());
$count = mysql_num_rows($result);
//while($row = mysql_fetch_array($result)){

?>
<tr>
<td><? if ($count) {
while($row = mysql_fetch_array($result))
print"Erection";
} else {
echo "";
}
?></td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr>
<td>---------------</td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<?
$query = "SET @total := 0";
$result = mysql_query($query) or die("Error: " . mysql_error());

$query = "SELECT ID1, erecthrs, erectrate, erectdesc FROM panddest8 WHERE ID1='$panddid'";
$result = mysql_query($query) or die("Error: " . mysql_error());
$count = mysql_num_rows($result);

while($row = mysql_fetch_array($result)){
?>
<tr>
<td><? echo $row['erectdesc'];

      ?></td>
      <td> </td>
      <td> </td>
<td><? echo $row['erecthrs'];

      ?></td>
<td> </td>
      <td>hours</td>
      <td> </td>
      <td>@ <? echo $row['erectrate'];

      ?></td>
      <td> </td>
      <td><?
      $calc2 = ($row['erecthrs'] * $row['erectrate']);
      print"$calc2";
      ?>
      <?

      //echo $row['total'];

      ?>
      </td>
      <td> </td>
      <td> </td>
      <td> </td>
</tr>
<?
}
?>

<tr>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
      <td> </td>
<td> </td>
<td> </td>
<td> </td>
<td>
      <?
            $query = "SET @tbl_total := 0";
$result = mysql_query($query) or die("Error: " . mysql_error());

$query = "SELECT ID1, erecthrs, erectrate, erectdesc, erecthrs * erectrate as sum, IF(@total IS NULL, @total := erecthrs * erectrate, @total := @total + erecthrs * erectrate) as total, @tbl_total := @tbl_total + erecthrs * erectrate as tbl_total FROM panddest8 WHERE ID1='$panddid'";
      $result = mysql_query($query) or die("Error: " . mysql_error());
while(list($ID1,$erecthrs,$erectrate,$erectdesc,$sum,$total) = mysql_fetch_row($result))
print"$total";


            ?>
      </td>
<td> </td>
</tr>
<?
}
?>

sorry for been a pain. usless i know.

Aleksandar Bradarić

Move the print out of the while:
---
while(list($ID1,$erecthrs,$erectrate,$erectdesc,$sum,$total) = mysql_fetch_row($result)) {
// do nothing (or something else...) :)
}
print"$total";

---

martin69

ASKER

few got there had to put tt1 = ($total); inside the brackets otherwise it would not print anything so looks like this

while(list($ID1,$erecthrs,$erectrate,$erectdesc,$sum,$total) = mysql_fetch_row($result)) {
tt1 = ($total);
}
print"$tt1";

thanks for your help. sorry for been usless.

Aleksandar Bradarić

I'm glad I could help :)