G Saunders Home Page EBUS202 Home Page

Project #2

Project #2 is to write data collected on Project #1's form to a record in a table in your database. It involves using an SQL script to make the table and modifying the script from Project #1 so that it updates the database when a valid form is submitted.

To avoid problems where some cracker or vandal somewhere might spam your pages, the project asks you to control access to the form while you're not using or demonstrating it.

SQL Scripting

Here is the directory at /home/tinstructor where the script is in a text file with a .sql extension so syntax highlighting will work:


[tinstructor@info465 ~]$ ls -las
total 68
4 drwx---r-x   6 tinstructor tinstructor 4096 Jul 21 16:04 .
4 drwxr-xr-x. 36 root        root        4096 Jul  4 16:23 ..
4 -rw-------   1 tinstructor tinstructor 2827 Jul 19 16:49 .bash_history
4 -rw-------   1 tinstructor tinstructor   18 Nov 20  2015 .bash_logout
4 -rw-------   1 tinstructor tinstructor  193 Nov 20  2015 .bash_profile
4 -rw-------   1 tinstructor tinstructor  231 Nov 20  2015 .bashrc
4 drwx------   3 tinstructor tinstructor 4096 Jun 26 18:40 .cache
4 drwx------   3 tinstructor tinstructor 4096 Jun 26 18:40 .config
4 drwx------   3 tinstructor tinstructor 4096 Jun 26 18:40 .local
0 -rw-rw-r--   1 tinstructor tinstructor    0 Jul 19 16:31 MemberAppLocked
4 -rw-rw-r--   1 tinstructor tinstructor  600 Jul 20 14:27 MemberApp.sql
4 -rw-------   1 tinstructor tinstructor  917 Jul 19 17:19 .mysql_history
4 -rw-r--r--   1 tinstructor tinstructor  567 Apr  1 18:54 OptionsStates
4 -rw-r--r--   1 tinstructor tinstructor  492 Apr  2 13:26 OptionsStatesNotSE
4 -rw-r--r--   1 tinstructor tinstructor   74 Mar 28 18:04 OptionsStatesSE
4 -rw-r--r--   1 tinstructor tinstructor  211 Jul 19 16:37 SiteSettings.php
4 -rw-------   1 tinstructor tinstructor  830 Jun 28 21:41 .viminfo
4 drwxr-xr-x   3 tinstructor tinstructor 4096 Jul 20 16:20 web


[tinstructor@info465 ~]$ cat MemberApp.sql
CREATE TABLE MembershipApps (
  MAId int unsigned NOT NULL AUTO_INCREMENT,
  MAName varchar(45) DEFAULT NULL,
  MASMS varchar(15) DEFAULT NULL,
  MASEStatesVisited text DEFAULT NULL,
  MASEStateFavorite varchar(25) DEFAULT NULL,
  MAOpinion text DEFAULT NULL,
  MAColor varchar(15)  DEFAULT NULL,
  MAPass varchar(50) DEFAULT NULL,
  MALeastFavoriteWeather varchar(25) default NULL,
  MAStatesVisited text default NULL,
  MADT timestamp,
  MAIPAddress varchar(20) default NULL,
  MAUserAgent varchar(120) default NULL,
  PRIMARY KEY (MAId)
) ENGINE=MyISAM AUTO_INCREMENT=1128 DEFAULT CHARSET=latin1;

You're welcome to paste the script into a similar file in your home directory. Then edit it to suit the data on your form.

Use a command line like this to run the script to create the table, substituting your credentials. Notice that the password isn't entered directly after the -p to keep it from showing up on logs. MySQL will prompt for your password when only the -p is in the connection string. The '<' sign is a 'redirect' that pushes the contents of the script into MySQL when the script is run. The EOD - End of Data at the script's end causes MySQL to return control to the command line. Here is what it looks like:


[tinstructor@info465 ~]$
[tinstructor@info465 ~]$ mysql -utinstructor -p  tinstructor < MemberApp.sql
Enter password:

[tinstructor@info465 ~]$
[tinstructor@info465 ~]$  

MySQL runs quietly if there are no errors, but will report syntax errors in the script if there are any. When it runs with no errors, tuck back into MySQL and find your newly created table:

[tinstructor@info465 ~]$ mysql -utinstructor -p  tinstructor 
Enter password:

MariaDB [tinstructor]> show tables;
+-----------------------+
| Tables_in_tinstructor |
+-----------------------+
| MembershipApps        |
+-----------------------+
1 row in set (0.00 sec)

MariaDB [tinstructor]> describe MembershipApps;
+------------------------+------------------+------+-----+-------------------+-----------------------------+
| Field                  | Type             | Null | Key | Default           | Extra                       |
+------------------------+------------------+------+-----+-------------------+-----------------------------+
| MAId                   | int(10) unsigned | NO   | PRI | NULL              | auto_increment              |
| MAName                 | varchar(45)      | YES  |     | NULL              |                             |
| MASMS                  | varchar(15)      | YES  |     | NULL              |                             |
| MASEStatesVisited      | text             | YES  |     | NULL              |                             |
| MASEStateFavorite      | varchar(25)      | YES  |     | NULL              |                             |
| MAOpinion              | text             | YES  |     | NULL              |                             |
| MAColor                | varchar(15)      | YES  |     | NULL              |                             |
| MAPass                 | varchar(50)      | YES  |     | NULL              |                             |
| MALeastFavoriteWeather | varchar(25)      | YES  |     | NULL              |                             |
| MAOtherStatesVisited   | text             | YES  |     | NULL              |                             |
| MADT                   | timestamp        | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| MAIPAddress            | varchar(20)      | YES  |     | NULL              |                             |
| MAUserAgent            | varchar(120)     | YES  |     | NULL              |                             |
+------------------------+------------------+------+-----+-------------------+-----------------------------+
13 rows in set (0.00 sec)

MariaDB [tinstructor]>


G Saunders,
Dept of Information Systems
VCU School of Business

G Saunders Wings

Content © 1999 - Today
By G Saunders
Images are Available on the Web