Solved

MySQL and JSP: combining and saperating queries

Posted on 2003-11-05
5
222 Views
Last Modified: 2010-04-01
i have 2 tables same structure but different data.
i have html table generated dynamically using data from the above tables.

the syntax:
<table width="100%" border="1">
        <tr bgcolor="#3399FF">
          <td colspan="5"><div align="center"><font color="#FFFFFF">
<strong>Out</strong></font></div></td>
        </tr>
        <tr>
          <td width="20%"><div align="center">Date</div></td>
          <td width="15%"><div align="center">TT No</div></td>
          <td width="15%"><div align="center">From</div></td>
          <td width="35%"><div align="center">Desc</div></td>
          <td width="15%"><div align="center">Amount</div></td>
        </tr>
<%
      Statement stmt4  = C.createStatement();
      ResultSet RS4    = stmt4.executeQuery("select *,sum(amount) as total from papertrans where type='out' group by type");
      
      while(RS4.next()){
            pdate2 = RS4.getString("date");
            pttno2 = RS4.getString("ttno");
            pfrom2 = RS4.getString("loc");
            pdescription2 = RS4.getString("description");
            pamount2 = RS4.getFloat("amount");
            float ptotal2 = RS4.getFloat("total");
%>
        <tr>
          <td width="20%"><div align="right"><%=pdate2%></div></td>
          <td width="15%"><div align="right"><%=pttno2%></div></td>
          <td width="15%"><div align="right"><%=pfrom2%></div></td>
          <td width="35%"><div align="right"><%=pdescription2%></div></td>
          <td width="15%"><div align="right"><%=sharePrice.format(pamount2)%></div></td>
        </tr>
        <tr>
          <td>&nbsp;</td>
          <td>&nbsp;</td>
          <td>&nbsp;</td>
          <td>Total</td>
          <td><div align="right"><%=sharePrice.format(ptotal2)%></div></td>
        </tr>
     </table>

question:
it only managed to show the 1st info but it showed the amount(ptotal2) correctly.
how am i suppose to display the other info without saperating the query into 2?
0
Comment
Question by:yapsing
  • 2
  • 2
5 Comments
 
LVL 3

Accepted Solution

by:
savalou earned 25 total points
ID: 9691710
Don't calculate the total in your query and don't group by type.  So you get all your records out.  Calculate your total in your JSP.

          float ptotal2 = 0;
     ResultSet RS4    = stmt4.executeQuery("select *,sum(amount) as total from papertrans where type='out'");
   
     while(RS4.next()){
...
         ptotal2 += pamount2;

0
 
LVL 19

Assisted Solution

by:Kuldeepchaturvedi
Kuldeepchaturvedi earned 25 total points
ID: 9691939
change is like..
<%
    Statement stmt4  = C.createStatement();
    ResultSet RS4    = stmt4.executeQuery("select * from papertrans where type='out' group by type");
   
     while(RS4.next()){
          pdate2 = RS4.getString("date");
         pttno2 = RS4.getString("ttno");
         pfrom2 = RS4.getString("loc");
         pdescription2 = RS4.getString("description");
         pamount2 = RS4.getFloat("amount");
         float ptotal2 += pamount2;
%>
that should do it









0
 
LVL 3

Expert Comment

by:savalou
ID: 9692321
yeah, I'd say it should be
     ResultSet RS4    = stmt4.executeQuery("select * from papertrans where type='out'");
Still no need to group by.
0
 
LVL 1

Author Comment

by:yapsing
ID: 9692394
another problem:
i only need to show the total once.

 <td width="15%"><div align="right"><%=sharePrice.format(pamount2)%> </div></td>
   </tr>
<%
   }//i can't put this here
%>
    <tr>
    <td>&nbsp;</td>
...
    <td><%=sharePrice.format(ptotal2)%></td>
0
 
LVL 1

Author Comment

by:yapsing
ID: 9692557
okie, i think i got it
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

We have come a long way with backup and data protection — from backing up to floppies, external drives, CDs, Blu-ray, flash drives, SSD drives, and now to the cloud.
In this article, I will show you HOW TO: Perform a Physical to Virtual (P2V) Conversion the easy way from a computer backup (image).
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now