How to query SELECT SUM of Total from three different table php

Question

Hope you good Guys! I have a big problem on SELECT three different table and SUM there Total so that I may get Grand total of those three tables. The table as follows, I just mention some of fields:

1:payment

id idnumber school_fee  trans_fee
1  va03     10000       20000

2:payment_one

id idnumber school_fee  trans_fee
1  va01     10000       30000

3:payment_two

id idnumber school_fee  trans_fee
1  va02     40000       50000

I have already get 'Total' from each table, what I want is to SUM UP those Total I get, to have Grand total from those three tables.

Here my php codes;

1:payment:

  <?php

     //include mysql connect

   if (isset($_GET['query'])) 
{    
      $query=$_GET['query'];

      // Instructions if $_POST['value'] exist    
      }  

   // gets value sent over search form

     $min_length = 3;
    // you can set minimum length of the query if you want

    if(strlen($query) >= $min_length){ // if query length is more or equal minimum  
           length then

       $query = htmlspecialchars($query); 
        // changes characters used in html to their equivalents, for example: < to &gt;

    $query = mysql_real_escape_string($query);
    // makes sure nobody uses SQL injection

       $raw_results = mysql_query("SELECT      
    *,SUM(school_fee+trans_fee) 
                    As Total  FROM payment
            WHERE (`class` LIKE '%".$query."%')") or die(mysql_error());

  $raw_results2 = mysql_query("SELECT * FROM payment
        WHERE (`class` LIKE '%".$query."%')") or die(mysql_error());





    // * means that it selects all fields, you can also write: `id`, `title`, `text`
    // articles is the name of our table

       // '%$query%' is what we're looking for, % means anything, for example if $query  
         is  Hello
    // it will match "hello", "Hello man", "gogohello", if you want exact match use  
      `title`='$query'
       // or if you want to match just full word so "gogohello" is out use '% $query %' 
          ...OR ... '$query %' ... OR ... '% $query'


    if(mysql_num_rows($raw_results) > 0){
    if(mysql_num_rows($raw_results2) > 0){
     // if one or more rows are returned do following

        while($results = mysql_fetch_array($raw_results)){
         while($results2 = mysql_fetch_array($raw_results2)){

        // $results = mysql_fetch_array($raw_results) puts data from database into   
               array, while it's valid it does the loop


            // posts results gotten from database(title and text) you can also show id  
                    ($results['id'])
        }{



              echo " &nbsp;Total amount of money payed by&nbsp;" .$results['class']  
                  ."&nbsp;"."class is&nbsp;" . $results ['Total'] . "&nbsp;/=Tshs";



              echo"<br>";   echo"<br>"; 
                               }


                  }
            }
            }
       }

                      ?>

2:payment_one

 <?php

     //include mysql connect

   if (isset($_GET['query'])) 
{    
      $query=$_GET['query'];

      // Instructions if $_POST['value'] exist    
      }  

   // gets value sent over search form

     $min_length = 3;
    // you can set minimum length of the query if you want

    if(strlen($query) >= $min_length){ // if query length is more or equal minimum  
           length then

       $query = htmlspecialchars($query); 
        // changes characters used in html to their equivalents, for example: < to &gt;

    $query = mysql_real_escape_string($query);
    // makes sure nobody uses SQL injection

       $raw_results = mysql_query("SELECT      
    *,SUM(school_fee+trans_fee) 
                    As Total  FROM payment_one
            WHERE (`class` LIKE '%".$query."%')") or die(mysql_error());

  $raw_results2 = mysql_query("SELECT * FROM payment_one
        WHERE (`class` LIKE '%".$query."%')") or die(mysql_error());





    // * means that it selects all fields, you can also write: `id`, `title`, `text`
    // articles is the name of our table

       // '%$query%' is what we're looking for, % means anything, for example if $query  
         is  Hello
    // it will match "hello", "Hello man", "gogohello", if you want exact match use  
      `title`='$query'
       // or if you want to match just full word so "gogohello" is out use '% $query %' 
          ...OR ... '$query %' ... OR ... '% $query'


    if(mysql_num_rows($raw_results) > 0){
    if(mysql_num_rows($raw_results2) > 0){
     // if one or more rows are returned do following

        while($results = mysql_fetch_array($raw_results)){
         while($results2 = mysql_fetch_array($raw_results2)){

        // $results = mysql_fetch_array($raw_results) puts data from database into   
               array, while it's valid it does the loop


            // posts results gotten from database(title and text) you can also show id  
                    ($results['id'])
        }{



              echo " &nbsp;Total amount of money payed by&nbsp;" .$results['class']  
                  ."&nbsp;"."class is&nbsp;" . $results ['Total'] . "&nbsp;/=Tshs";



              echo"<br>";   echo"<br>"; 
                               }


                  }
            }
            }
       }

                      ?>

3:payment_two

 <?php

     //include mysql connect

   if (isset($_GET['query'])) 
{    
      $query=$_GET['query'];

      // Instructions if $_POST['value'] exist    
      }  

   // gets value sent over search form

     $min_length = 3;
    // you can set minimum length of the query if you want

    if(strlen($query) >= $min_length){ // if query length is more or equal minimum  
           length then

       $query = htmlspecialchars($query); 
        // changes characters used in html to their equivalents, for example: < to &gt;

    $query = mysql_real_escape_string($query);
    // makes sure nobody uses SQL injection

       $raw_results = mysql_query("SELECT      
    *,SUM(school_fee+trans_fee) 
                    As Total  FROM payment_two
            WHERE (`class` LIKE '%".$query."%')") or die(mysql_error());

  $raw_results2 = mysql_query("SELECT * FROM payment_two
        WHERE (`class` LIKE '%".$query."%')") or die(mysql_error());





    // * means that it selects all fields, you can also write: `id`, `title`, `text`
    // articles is the name of our table

       // '%$query%' is what we're looking for, % means anything, for example if $query  
         is  Hello
    // it will match "hello", "Hello man", "gogohello", if you want exact match use  
      `title`='$query'
       // or if you want to match just full word so "gogohello" is out use '% $query %' 
          ...OR ... '$query %' ... OR ... '% $query'


    if(mysql_num_rows($raw_results) > 0){
    if(mysql_num_rows($raw_results2) > 0){
     // if one or more rows are returned do following

        while($results = mysql_fetch_array($raw_results)){
         while($results2 = mysql_fetch_array($raw_results2)){

        // $results = mysql_fetch_array($raw_results) puts data from database into   
               array, while it's valid it does the loop


            // posts results gotten from database(title and text) you can also show id  
                    ($results['id'])
        }{



              echo " &nbsp;Total amount of money payed by&nbsp;" .$results['class']  
                  ."&nbsp;"."class is&nbsp;" . $results ['Total'] . "&nbsp;/=Tshs";



              echo"<br>";   echo"<br>"; 
                               }


                  }
            }
            }
       }

                      ?>

Any help I'will be thankfully.

[Please, stop using mysql_* functions](http://stackoverflow.com/q/12859942/1238019) in new code, they are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Instead of, have a look on [prepared statements](http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-prepared-statements.html), and use [Mysqli](http://php.net/manual/en/book.mysqli.php) or [PDO](http://php.net/manual/en/book.pdo.php). — zessx, Nov 03 '13 at 14:22
Do you realize that the three scripts you posted are exactly the same, except for the table name mentioned in the query (`payment`, `payment_one` and `payment_two`)? Also, in my opinion, having three tables like that is a terribly bad idea, unless you are using a legacy (pre existing) database -- in that case, I would use a view table to unify them, indeed. — Stefano Sanfilippo, Nov 03 '13 at 14:23
You should read up on the basics of normalization and discover that this problem is trivial if you fix your database into at least the second normal form. — Niels Keurentjes, Nov 03 '13 at 14:59

Paul Lo · Answer 1 · 2013-11-03T14:43:51.863

1

If I understand correctly, all of your three tables [payment][payment_one][payment_two] have same columns: id, idnumber, school_fee trans_fee.

You would be able to use one single table instead, and distinguish them by introducing a new column: tablenum, then it will be simple to obtain what you want. Please note both [id] and [tablenum] are primary key (composite primary key) now.

New table schema and the data would be (I'm not very sure about the purpose of your idnumber column):

[payment] 
id tablenum idnumber school_fee  trans_fee 
1  0        va03     10000       20000
1  1        va01     10000       30000
1  2        va02     40000       50000

SQL example:

SELECT 
school_fee,
trans_fee,
(school_fee + trans_fee) as 'total'
FROM payment WHERE id=1

edited Nov 03 '13 at 14:43

answered Nov 03 '13 at 14:28

Paul Lo

6,032
6
31
36

So, is this will bring the grand total, Note: this tables are separate b'se each class have different fee, – Haika Nov 03 '13 at 14:42
Yes, this unify table should serve your need, and avoid those duplicate code you have. – Paul Lo Nov 03 '13 at 14:49
the idnumber is student number, and each class has different fee from one another, payment table carry Baby class fee,payment_one is for standard one fee and payment_two is for standard two fee, so now is required to get The total SUM of all class fee. – Haika Nov 03 '13 at 14:50
1

'tablenum' column is used to identify different classes for you, value 0 is for your original [payment] table, value 1 is for your [payment_one], value 2 is for your [payment_two]. So right now with this new schema you only need one table, which make your code more simple and clean. – Paul Lo Nov 03 '13 at 17:22
tablenum colum is auto increment or? – Haika Nov 03 '13 at 17:33
No, you need to assign the value by yourself since you want to identify which classes it is for. So, when you insert the data, you "INSERT INTO payment (id, tablenum, idnumber, school_fee, trans_fee) VALUES (1, 1 ,'va01', 10000, 30000)" rather than "INSERT INTO payment_one (id, idnumber, school_fee, trans_fee) VALUES (1, 'va01', 10000, 30000)", "INSERT INTO payment (id, tablenum, idnumber, school_fee, trans_fee) VALUES (1, 2 ,'va02', 40000, 50000)" rather than "INSERT INTO payment_two (id, idnumber, school_fee, trans_fee) VALUES (1, 'va02', 40000, 50000)" – Paul Lo Nov 04 '13 at 07:58

How to query SELECT SUM of Total from three different table php

1 Answers1