Using SQL Data Across Applications and on the Web

1
Using SQL Data Across Applications and on the Web

• How do you export and import data into or out of
  a database?
• Tying web pages to a database
• How do you make a query work on the web?
• How do you make a web page using forms?
• What does an .asp file extension mean?
• What does a .cgi file extension mean?

Course Guide p. 103
2
About moving. First, a few definitions

• The CSV (Comma delimited) file format
• batting.csv
• Text files (ascii)
• pitching.txt
• HTML files
• Batting.html

3
Moving data into MySQL-Front

• Import data from authors website
• http//www.forta.com/books/0672321289/
• Then use WinZip, and you have text scripts

4
First, run the create tables scriptsJust paste
them into a query

• CREATE TABLE Customers
• (
• cust_id CHAR(10) NOT NULL,
• cust_name CHAR(50) NOT NULL,
• cust_addressCHAR(50) ,
• cust_city CHAR(50) ,
• cust_state CHAR(5) ,
• cust_zip CHAR(10) ,
• cust_country CHAR(50) ,
• cust_contact CHAR(50) ,
• cust_email CHAR(255)
• )

5
Then, select one table,and run its populate
scriptpaste it into a query

• INSERT INTO Customers
• (cust_id, cust_name, cust_address, cust_city,
  cust_state, cust_zip, cust_country, cust_contact,
  cust_email)
• VALUES
• ('1000000001', 'Village Toys', '200 Maple Lane',
  'Detroit', 'MI', '44444', 'USA', 'John Smith',
  'sales_at_villagetoys.com')
• etc.

6
Transfer complete
7
Moving data from MySQL-Front to Access

• We found that .csv files were the easiest way.
• When you export as shown to the right (using
  Export Tables), you get code that looks like this
  file -- not easily imported into Access.

8
Easier Save as .csv Data

• This action saves the data in a file (csv means
  comma-separated-values)

9
What does it look like now?

• Each record is on its own line
• Commas separate each field in each record

10
Importing the data into Access (or any other
database)

• Most databases allow you to import raw files as
  long as there is something to delineate the
  columns.
• With Access, create a new database but do not
  enter any data.

11
Choose type of data that you will import
12
Access automatically creates the columns
13
You tell the database whatdata types,
constraints, names you want
14
Now you are all set
15
So what is an interactive page?

• People got restless and wanted more than static
  web pages
• They wanted information that changes depending on
  the time (game scores) or the query (who wrote
  Atlas Shrugged?)

This slide not in Course Guide
16
Thus dynamic web pages!
Course Guide p. 106
17
Pull-down menu on IMDB - Internet Movie Database
http//www.imdb.com/
This slide not in Course Guide
18
HTML for the form on the Internet Movie Database

• name"select" All
  Titles My
  Movies Names
  Companies Keywordsn Characters Quotesion Bios Plots

This slide not in Course Guide
19
The link

• http//www.cs.uiuc.edu/class/fa05/cs105/websql/
• Your readings section D in the HTML book deals
  with interactive pages

This slide not in Course Guide
20
Kinds of scripts you might find

• Microsoft's Active Server Pages (ASP)
• Java
• PHP (Hypertext Preprocessor)
• CGI (Common Gateway Interface)
• We started with .cgi and moved to .php when CS
  put in a new server

This slide not in Course Guide
21
Simple variation we began with using CGI
Course Guide p. 106
22
The current PHP version looks like this
This slide not in Course Guide
23
How to tell the Browser to create a form with
CGI

• Let's learn more about the sport. Here is a
  drop-down box with some queries.
• .cs.uiuc.edu/cs105/websql.cgi"
• Note this calls a .cgi script to execute in the
  CS105 public_html folder. Or it could be a .php
  script like

Course Guide p. 107
24
Or you can do it like this with PHP

• My Online Database
  
• The above is a relative link. The file named
  websql.php is in the same folder as index.html

This slide not in Course Guide
25
What does the html look like for each choice?

• Season2005"How many games won in 2005?

Note Whether you use .cgi or .php, the query is
the same.
26
.cgi

• http//www.kn.pacbell.com/wired/cgi/

Course Guide p. 107
27
Extra Material

• PHP can do anything any other CGI program can do,
  such as collect form data, generate dynamic page
  content, or send and receive cookies.

28
More material will be given in class

• You will create a form on a web page for MP3
• All you have to do is put the form information in
  your web page
• We will do the PHP scripting for you