Jump to content

SELECT Count for multiple tables


shiqla

Recommended Posts

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>

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.