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(10) unsigned NOT NULL AUTO_INCREMENT,
  `MAUserId` varchar(22) DEFAULT NULL,
  `MAName` varchar(45) DEFAULT NULL,
  `MASMS` varchar(15) DEFAULT NULL,
  `MASEStatesVisited` text,
  `MASEStateFavorite` varchar(25) DEFAULT NULL,
  `MAOpinion` text,
  `MAColor` varchar(15) DEFAULT NULL,
  `MAPass` varchar(50) DEFAULT NULL,
  `MALeastFavoriteWeather` varchar(25) DEFAULT NULL,
  `MAOtherStatesVisited` text,
  `MADT` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `MAIPAddress` varchar(20) DEFAULT NULL,
  `MAUserAgent` varchar(120) DEFAULT NULL,
  PRIMARY KEY (`MAId`)
) ENGINE=MyISAM AUTO_INCREMENT=1224 DEFAULT CHARSET=latin1;


The backtics are optional except where column names have spaces in them, which none should ever have!

You're welcome to paste the script into a similar file in your home directory. Or, cp /home/tinstructor/JulyApps.sql to your home directory. Then edit it to suit the data on your form.

Use a command line like the following 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 ~]$  

With '-p' not followed by a password in the connection string, MySQL prompts for your password before executing the script.

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