www.webdeveloper.com
Results 1 to 6 of 6

Thread: SELECT Count for multiple tables

  1. #1
    Join Date
    Apr 2005
    Posts
    70

    SELECT Count for multiple tables

    I need to count total from 5 different tables. Each of 1 table represent 1 type of form.The objective: to create a pie chart. The function of this pie chart is:to show total of form for current date.I already count total of form for current date from 5 different tables and got the correct amount.My problem is:HOW TO COUNT SUM OF THE TOTAL FORMS FROM 5 DIFFERENT TABLES.

    PHP Code:
    <table cellpadding="1" cellspacing="1" width="100%" align="center"> 
      <tr> 
        <td><h3>&nbsp;Total of Form : Today</h3></td> 
      </tr>
      <tr>
        <td><br></td>
      </tr>
      <tr>
        <td><table cellpadding="1" cellspacing="1" width="40%">
          <tr>
            <td><span class="teks17">Type of Form</span></td>
            <td align="center"><span class="teks17">Total</span></td>
          </tr>
          <tr>
            <td><a class="menu" href="index.php?menu=listFormA">Form A: Bunkering</a></td>
            <?php
                    
                    $sql 
    "SELECT COUNT(*) AS bunkering FROM bunkering WHERE dateForm >= DATE_SUB(NOW(), INTERVAL 1 DAY)";
                    
    $result mysql_query($sql) or die('Error, query failed');
                    
    $row mysql_fetch_array($result);
                    
    ?>
            <td align="center"><span class="teks06"><?php echo $row['bunkering']; ?></span></td>
          </tr>
          <tr>
            <td><a class="menu" href="index.php?menu=listFormB">Form B: Cargo Vessel</a></td>
            <?php
                    
                    $sql1 
    "SELECT COUNT(*) AS cargo FROM cargo WHERE dateForm >= DATE_SUB(NOW(), INTERVAL 1 DAY)";
                    
    $result1 mysql_query($sql1) or die('Error, query failed');
                    
    $row1 mysql_fetch_array($result1);
                    
    ?>
            <td align="center"><span class="teks06"><?php echo $row1['cargo']; ?></span></td>
          </tr>
        <tr>
                    <td><a class="menu" href="index.php?menu=listFormC">Form C: Offshore Vessel</a></td>
                    <?php
                    $sql2 
    "SELECT COUNT(*) AS offshore FROM offshore WHERE dateForm >= DATE_SUB(NOW(), INTERVAL 1 DAY)"
                    
    $result2 mysql_query($sql2) or die('Error, query failed');
                    
    $row2 mysql_fetch_array($result2);
                    
    ?>
                    <td align="center"><span class="teks06"><?php echo $row2['offshore']; ?></span></td>
        </tr>
        <tr>
                    <td><a class="menu" href="index.php?menu=listFormD">Form D: Tug & Barge</a></td>
                    <?php
                    $sql3 
    "SELECT COUNT(*) AS tugbarge FROM tugbarge WHERE dateForm >= DATE_SUB(NOW(), INTERVAL 1 DAY)";
                    
    $result3 mysql_query($sql3) or die('Error, query failed');
                    
    $row3 mysql_fetch_array($result3);
                    
    ?>
                    <td align="center"><span class="teks06"><?php echo $row3['tugbarge']; ?></span></td>
        </tr>
        <tr>
                    <td><a class="menu" href="index.php?menu=listFormE">Form E: Other Vessel</a></td>
                    <?php
                    $sql4 
    "SELECT COUNT(*) AS other FROM other WHERE dateForm >= DATE_SUB(NOW(), INTERVAL 1 DAY)";
                    
    $result4 mysql_query($sql4) or die('Error, query failed');
                    
    $row4 mysql_fetch_array($result4);
                    
    ?>
                    <td align="center"><span class="teks06"><?php echo $row4['other']; ?></span></td>
        </tr>
          
          <tr>
            <td><hr /><span class="teks17">Total</span></td>
            <?php
                    $sqlTotal 
    "SELECT COUNT(*) AS total FROM **(5tables)--THIS IS MY PROBLEM
                      WHERE dateForm >= DATE_SUB(NOW(), INTERVAL 1 DAY)"
    ;
                      
                    
    $resultTotal mysql_query($sqlTotal) or die('Error, query failed');
                    
    $rowTotal mysql_fetch_array($resultTotal);
                                    
                    
    ?>
            <td align="center"><hr /><span class="teks17"><?php echo $rowTotal['total']; ?>
            
            </span></td>
          </tr>
          <tr> </tr>
        </table></td>
      </tr>
      <tr>
        <td>
        <form method="post" action="index.php?menu=chart2">
            <input type="hidden" name="bunkering" value="<?php echo $row['bunkering']; ?>">
            <input type="hidden" name="cargo" value="<?php echo $row1['cargo']; ?>">
            <input type="hidden" name="offshore" value="<?php echo $row2['offshore']; ?>">
            <input type="hidden" name="tugbarge" value="<?php echo $row['tugbarge']; ?>">
            <input type="hidden" name="other" value="<?php echo $row1['other']; ?>">
            <input type="hidden" name="total" value="<?php echo $rowTotal['total']; ?>">
            <input type="submit" name="submit" value="Pie Chart">
        </form>
        </td>
      </tr>
     <tr>
            <td align="left" colspan="2"><A class="menu" HREF="javascript:javascript:history.go(-1)"><IMG SRC="../images/back.jpg" BORDER="0" ALT="back"></a>&nbsp;</td>
      </tr>
    </table>

  2. #2
    Join Date
    Apr 2010
    Posts
    227
    Just store each individual total into a session variable
    PHP Code:
    @session_start();
    if (!isset(
    $_SESSION['grandtotal'])){
            
    $_SESSION['grandtotal'] = 0;
    }


    $_SESSION['grandtotal'] += $rowTotal['total']; 
    Or is this a trick question?
    Last edited by Belrick; 07-04-2012 at 06:47 PM.

  3. #3
    Join Date
    Apr 2005
    Posts
    70

    Count Grand Total of 5 tables

    I know how to store the count (total) from the 5 tables.But i don't know how to count grand total using session variables.I only manage to store (count total) separately from the 5 tables using the session variables.

  4. #4
    Join Date
    Apr 2010
    Posts
    227
    So youre sorted now then?

  5. #5
    Join Date
    Apr 2005
    Posts
    70
    Thanks for your reply.I didn't get what u meant.I never try use session variables like ur example before..So,i don't understand how to count the grand total using that's method.

  6. #6
    Join Date
    Apr 2005
    Posts
    70

    resolved Found Solution

    I ve found the solution.Very simple..

    PHP Code:
    <?php $rowTotal['jumlah'] +=$row['bunkering'] + $row1['cargo'] + $row2['offshore'] + $row3['tugbarge'] + $row4['other']; ?>
    Last edited by inventive; 07-06-2012 at 05:28 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles