episkeyyy episkeyyy - 4 months ago 8
MySQL Question

use multiple mysql tables and display in one html table

I'm trying to add the number of orders into the last column in an html table by joining my "user2" table and "orders" table using a sql statement (I made the userID in "user" primary key and userID in "orders" foreign key) and using setAttribute in my Servlet class in order to use EL in my jsp file. This is the code I have right now in my Servlet class,

ps = conn
.prepareStatement("select * FROM user2 WHERE lastname LIKE '%"
+ searchReq + "%'");
rs = ps.executeQuery();

while (rs.next()) {
user = new User(rs.getInt("userID"), rs.getString("username"),
rs.getString("password"), rs.getString("firstname"),
rs.getString("lastname"), rs.getString("Email"),
rs.getInt("age"));
listOfUsers.add(user);


and in my jsp:

<table border="1" class="search-table">

<tr>
<td>ID</td>
<td>Username</td>
<td>Password</td>
<td>First Name</td>
<td>Last Name</td>
<td>Email</td>
<td>Age</td>
<td>Orders</td>


</tr>

<c:forEach items="${requestScope.lou}" var="current">
<tr>
<td><c:out value="${current.id}" /></td>
<td><c:out value="${current.username}" /></td>
<td><c:out value="${current.password}" /></td>
<td><c:out value="${current.firstname}" /></td>
<td><c:out value="${current.lastname}" /></td>
<td><c:out value="${current.email}" /></td>
<td><c:out value="${current.age}" /></td>
<td><c:out value="${orders}" /></td>


</tr>
</c:forEach>
</table>


My orders table has userID, orderID, Shipping, and item as columns, and I count the number of orders by using orderID per userID.

I am just unsure how to get the number of orders to go into each row in my html table, since when I used a subquery in the "select * FROM user2...etc" statement it returns more than one row (as it should) but I'm not sure how to handle the multiple rows. Or is there a different way to handle this? Thanks for any guidance, I am a beginner just learning.

Answer

Seems like you need to learn SQL.

To get a count of orders as an extra column of your query result, use a subselect, e.g.

SELECT userID
     , username
     , password
     , firstname
     , lastname
     , Email
     , age
     , ( SELECT COUNT(*)
           FROM orders o
          WHERE o.userID = u.userID
       ) AS orders
  FROM user2 u
 WHERE lastname LIKE ?

That SQL also uses a parameter marker (?), and you specify the value of the marker by calling setString() of a PreparedStatement.

You do NOT do it using string concatenation, unless you want hackers to use SQL Injection to steal your data and/or delete you tables.

Comments