Creating the User Registration Table

 

Background.  In order to develop the database you are likeliest to need to connect to a pre-existing database you have on your website and create a table.  If you don't have a pre-existing database that has been developed by your hosting source you will first need to do that.

But on jasperations.net you have a MySQL database called jasperat since our username is jasperat.   Given this you will need to create a yourlastname_user_registration table within this database. 

In order to make certain we work with all of the different form controls, and not much more, I have somewhat artificially influenced the structure of the table.  In addition, MySQL has a fairly strange feature for dealing with true/false or yes/no data.  When referencing a table in MySQL, the only things that will evaluate to false are 0 and blank "" or ''.  This requires a bit of finagling in the JSP to handle this.  Ultimately, I chose to work with text type fields even for the check boxes.

You will also want to create a subdirectory on jasperations.net called user_registration_client in order to upload and execute your pages.

The following is the JSP code to do this on jasperations.  It's not likely to be very different on any other site.  You should call this file create_user_registration_table.jsp.  You are likely to need to make sure the SQL string is all on the same line even though it doesn't appear to be in this display.

 

<html>
<head>
<title>Creating a Table in MySQL Using JSP</title>
</head>
<%@ page import = "java.sql.*" %>
<body>
<%
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 = "CREATE TABLE yourlastname_user_registration
(user_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(20),
last_name VARCHAR(30),
email VARCHAR(50) NOT NULL,
password VARCHAR(16) NOT NULL,
php_interest VARCHAR(12),
jsp_interest VARCHAR(12),
mysql_interest VARCHAR(14),
credit_card VARCHAR(12),
education VARCHAR(12))";

Statement stmt = con.createStatement( );

stmt.executeQuery( qs );

out.println ("The query string is <BR>" + qs + "<BR>");

}
// 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 ( );

}
%>
</body>
</html>

 

The formatting on the table create string should help you to see the fields.  The following table summarizes these and gives a brief description.

 

Control Type Name Corresponding Field Description
    user_id Auto incrementing integer which will be the primary key.
text box txt_first_name first_name Control and field corresponding to the first name of the user.  This will make use of a variable length character field.
text box txt_last_name last_name Control and field corresponding to the last name of the user.  This will make use of a variable length character field.
text box txt_email email Control and field corresponding to the email address of the user.  This will make use of a variable length character field.
password text box txt_password password Control and field corresponding to the email address of the user.  This will make use of a variable length character field.
checkbox chk_php php_interest The user will check this checkbox if they profess an interest in PHP.  This is characterized in a varchar field.
checkbox chk_jsp jsp_interest The user will check this checkbox if they profess an interest in JSP.  This is characterized in a varchar field.
checkbox chk_mysql mysql_interest The user will check this checkbox if they profess an interest in MySQL.  This is characterized in a varchar field.
select/option sel_credit_card credit_card The user will select which of the credit cards they will use. The options are
  • Discover
  • MasterCard
  • Visa

This is characterized by a variable length character which will store which type of credit card they've selected.

radio button rb_education education The user will check one of the radio buttons to represent their level of education. The radio buttons are
  • NoCollege
  • College
  • Masters
  • PhD

This is characterized by a variable length character which will store which type of credit card they've selected.