• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 314
  • Last Modified:

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>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td>---------------</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</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>&nbsp;</td>
      <td>&nbsp;</td>
    <td><? echo $row['erecthrs'];
      
      ?></td>
    <td>&nbsp;</td>
      <td>hours</td>
      <td>&nbsp;</td>
      <td>@ <? echo $row['erectrate'];
      
      ?></td>
      <td>&nbsp;</td>
      <td><?
      $calc2 = ($row['erecthrs'] * $row['erectrate']);
      print"$calc2";
      ?>
      </td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
  <?
  }
  ?>
 
  <tr>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
      <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
  <?
  }
  ?>
 
0
martin69
Asked:
martin69
  • 9
  • 8
1 Solution
 
Aleksandar BradarićSoftware DeveloperCommented:
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)
---
0
 
martin69Author Commented:
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.
0
 
Aleksandar BradarićSoftware DeveloperCommented:
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...
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
martin69Author Commented:
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.
0
 
Aleksandar BradarićSoftware DeveloperCommented:
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? :)
0
 
Aleksandar BradarićSoftware DeveloperCommented:
Here's the same example with additional tbl_totals (total for each table):
---
mysql> SET @total := 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @tbl_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, @tbl_total := @tbl_total + paintarea * paintrate as tbl_total
 FROM panddest2;
+------+-------+-----------+-----------+------+-------+-----------+
| id   | id1   | paintarea | paintrate | sum  | total | tbl_total |
+------+-------+-----------+-----------+------+-------+-----------+
|    1 | 76795 |        10 |       130 | 1300 |  1300 |      1300 |
+------+-------+-----------+-----------+------+-------+-----------+
1 row in set (0.00 sec)

mysql> SET @tbl_total := 0;
Query OK, 0 rows affected (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, @tbl_total := @tbl_total + erecthrs * erectrate as tbl_total FROM  panddest8;
+------+-------+----------+-----------+------+-------+-----------+
| id   | id1   | erecthrs | erectrate | sum  | total | tbl_total |
+------+-------+----------+-----------+------+-------+-----------+
|    1 | 76795 |      120 |        23 | 2760 |  4060 |      2760 |
|    2 | 76795 |       20 |        35 |  700 |  4760 |      3460 |
+------+-------+----------+-----------+------+-------+-----------+
2 rows in set (0.00 sec)
---
0
 
martin69Author Commented:
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
0
 
Aleksandar BradarićSoftware DeveloperCommented:
> 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.
0
 
martin69Author Commented:
thanks very much.
0
 
martin69Author Commented:
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>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td>---------------</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</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>&nbsp;</td>
      <td>&nbsp;</td>
    <td><? echo $row['erecthrs'];
      
      ?></td>
    <td>&nbsp;</td>
      <td>hours</td>
      <td>&nbsp;</td>
      <td>@ <? echo $row['erectrate'];
      
      ?></td>
      <td>&nbsp;</td>
      <td>
      <?
      
      echo $row['total'];
      
      ?>
      </td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
  <?
  }
  ?>
 
  <tr>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
      <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>
      <?
      // this is where i would like it to print the grand total      
      echo $row['total'];
      
            ?>
      </td>
    <td>&nbsp;</td>
  </tr>
  <?
  }
  ?>
0
 
Aleksandar BradarićSoftware DeveloperCommented:
The SET commands are missing.
0
 
martin69Author Commented:
i know im a pain sorry. where do i need to do the SET @total bit.
0
 
Aleksandar BradarićSoftware DeveloperCommented:
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?
0
 
martin69Author Commented:
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>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td>---------------</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</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>&nbsp;</td>
      <td>&nbsp;</td>
    <td><? echo $row['erecthrs'];
      
      ?></td>
    <td>&nbsp;</td>
      <td>hours</td>
      <td>&nbsp;</td>
      <td>@ <? echo $row['erectrate'];
      
      ?></td>
      <td>&nbsp;</td>
      <td><?
      $calc2 = ($row['erecthrs'] * $row['erectrate']);
      print"$calc2";
      ?>
      <?
      
      //echo $row['total'];
      
      ?>
      </td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
  <?
  }
  ?>
 
  <tr>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
      <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</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>&nbsp;</td>
  </tr>
  <?
  }
  ?>
 
sorry for been a pain. usless i know.      
0
 
Aleksandar BradarićSoftware DeveloperCommented:
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";

---
0
 
martin69Author Commented:
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.
0
 
Aleksandar BradarićSoftware DeveloperCommented:
I'm glad I could help :)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now