yapsing
asked on
MySQL and JSP: combining and saperating queries
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></t d>
</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"><%=pdescript ion2%></di v></td>
<td width="15%"><div align="right"><%=sharePric e.format(p amount2)%> </div></td >
</tr>
<tr>
<td> </td>
<td> </td>
<td> </td>
<td>Total</td>
<td><div align="right"><%=sharePric e.format(p total2)%>< /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?
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
</tr>
<tr>
<td width="20%"><div align="center">Date</div><
<td width="15%"><div align="center">TT No</div></td>
<td width="15%"><div align="center">From</div><
<td width="35%"><div align="center">Desc</div><
<td width="15%"><div align="center">Amount</div
</tr>
<%
Statement stmt4 = C.createStatement();
ResultSet RS4 = stmt4.executeQuery("select
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%><
<td width="15%"><div align="right"><%=pttno2%><
<td width="15%"><div align="right"><%=pfrom2%><
<td width="35%"><div align="right"><%=pdescript
<td width="15%"><div align="right"><%=sharePric
</tr>
<tr>
<td> </td>
<td> </td>
<td> </td>
<td>Total</td>
<td><div align="right"><%=sharePric
</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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
another problem:
i only need to show the total once.
<td width="15%"><div align="right"><%=sharePric e.format(p amount2)%> </div></td>
</tr>
<%
}//i can't put this here
%>
<tr>
<td> </td>
...
<td><%=sharePrice.format(p total2)%>< /td>
i only need to show the total once.
<td width="15%"><div align="right"><%=sharePric
</tr>
<%
}//i can't put this here
%>
<tr>
<td> </td>
...
<td><%=sharePrice.format(p
ASKER
okie, i think i got it
ResultSet RS4 = stmt4.executeQuery("select
Still no need to group by.