Displaying Users at the User Registration Website

 

Introduction.  Now that we have been able to add some users we want to be able to display them.  This will be based on a SELECT command in SQL.  You should also notice the abundance of similarities between this page and the preceding page where we used an INSERT command to write to the yourlastname_user_registration table.

For this application we require only a single page which should be called display_user_registration.jsp.

 

<html>
<head>
<title>Retrieving from a Table in MySQL and Displaying Using JSP</title>
</head>
<%@ page import = "java.sql.*" %>
<%

String php_display;
String jsp_display;
String mysql_display;

// assign the values for database access
try
{

// assign the values for database access
Class.forName ("org.gjt.mm.mysql.Driver");

String url = "jdbc:mysql://localhost:3306/jasperat";
String username = "jasperat";
String password = "f5W2-Ld5s";

Connection con = DriverManager.getConnection ( url, username, password);

String qs = "SELECT * FROM yourlastname_user_registration";
Statement stmt = con.createStatement( );

ResultSet rs = stmt.executeQuery( qs );
%>
<table border=2 width = 900 align=center>
<tr>
<td align = center><font size = 4><b>Name</b></font>
</td>
<td align = center><font size = 4><b>EMail Address</b></font>
</td>
<td align = center><font size = 4><b>Interests</b></font>
</td>
<td align = center><font size = 4><b>Credit Card</b></font>
</td>
<td align = center><font size = 4><b>Education</b></font>
</td>
</tr>


<%
while ( rs.next( ) )

{

php_display = "";
jsp_display = "";
mysql_display = "";
if (rs.getString("php_interest").equals("php_yes"))
php_display = "PHP" + "<br>";
if (rs.getString("jsp_interest").equals("jsp_yes"))
jsp_display = "JSP" + "<br>";
if (rs.getString("mysql_interest").equals("mysql_yes"))
mysql_display = "MySQL" + "<br>";
%>
<tr>
<td align = center><font size = 4><%=rs.getString("first_name")%> <%=rs.getString("last_name")%></font>
</td>
<td align = center><font size = 4><%=rs.getString("email")%></font>
</td>
<td align = center><font size = 4><%=php_display%><%=jsp_display%><%=mysql_display%></font>
</td>
<td align = center><font size = 4><%=rs.getString("credit_card")%></font>
</td>
<td align = center><font size = 4><%=rs.getString("education")%></font>
</td>
</tr>
<%

}
// close the resultSet, Statement and Connection
rs.close( );
stmt.close( );
con.close( );

}
// the catch block for the connection
catch (java.lang.Exception ex)

{

// Print description of the exception.
System.out.println( "** Error on data select. ** " );
ex.printStackTrace ( );

}
%>
</table>

<body bgcolor = "003344" text="cccccc" link="00aacc" vlink="007799">
<center>
<P><a href = "user_registration_home.html"><font size=4><b>User Registration HomePage</b></font></a></P>
</center>
</body>
</html>

 

After this page is uploaded and accessed you should see something like the following.

 

 

Notice the processing that needs to be done to get the PHP, JSP and MySQL interests to display.

Notice how I bounced between HTML and JSP to create the table.  The loops are in JSP, the actual printing of the database values are in JSP, but the table structure is in HTML.

You should also make sure to notice the patterns from the write_to_user_registration.jsp that are repeated in this JSP.