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 05: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 04: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

"

"

X vBulletin 4.2.2 Debug Information

  • Page Generation 0.20034 seconds
  • Memory Usage 2,924KB
  • Queries Executed 13 (?)
More Information
Template Usage (33):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (3)bbcode_php
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (6)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (1)navbar_moderation
  • (1)navbar_noticebit
  • (1)navbar_tabs
  • (2)option
  • (6)postbit
  • (6)postbit_onlinestatus
  • (6)postbit_wrapper
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (26):
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/functions_navigation.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./includes/functions_facebook.php
  • ./includes/functions_calendar.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/functions_notice.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php 

Hooks Called (70):
  • init_startup
  • friendlyurl_resolve_class
  • init_startup_session_setup_start
  • database_pre_fetch_array
  • database_post_fetch_array
  • init_startup_session_setup_complete
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • load_show_variables
  • load_forum_show_variables
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • strip_bbcode
  • friendlyurl_clean_fragment
  • friendlyurl_geturl
  • forumjump
  • cache_templates
  • cache_templates_process
  • template_register_var
  • template_render_output
  • fetch_template_start
  • fetch_template_complete
  • parse_templates
  • fetch_musername
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • memberaction_dropdown
  • tag_fetchbit_complete
  • forumrules
  • navbits
  • navbits_complete
  • build_navigation_data
  • build_navigation_array
  • check_navigation_permission
  • process_navigation_links_start
  • process_navigation_links_complete
  • set_navigation_menu_element
  • build_navigation_menudata
  • build_navigation_listdata
  • build_navigation_list
  • set_navigation_tab_main
  • set_navigation_tab_fallback
  • navigation_tab_complete
  • fb_like_button
  • showthread_complete
  • page_templates