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>

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.