SELECT Count for multiple tables

[shiqla]

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.

 

<table cellpadding="1" cellspacing="1" width="100%" align="center"> 
  <tr> 
    <td><h3> 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> </td> 
  </tr> 
</table>

[mikosiko]

if you are already counting the records from the 5 tables why the last select?... is not than simple as

$total = $row['bunkering'] + $row1['cargo'] + $row2['offshore'] + $row3['tugbarge'] + $row4['others']

[shiqla]

I just found the solution and want to post it here.haha.same with yours..

 

I ve found the solution.Very simple..

<?php $rowTotal['jumlah'] +=$row['bunkering'] + $row1['cargo'] + $row2['offshore'] + $row3['tugbarge'] + $row4['other']; ?>

 

You re the one from so many forum that i post that give this correct solution..Thanks for your reply!