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?

p. 109 in Course Guide
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 SQLyog

• 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 MySQLyog 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) BUT you have to tell it
  to use commas instead of tabs! See next slide

Changed from Course Guide
9
Making sure it uses commas
10
What does it look like now?

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

11
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.

12
Choose type of data that you will import
13
Access automatically creates the columns
14
You tell the database whatdata types,
constraints, names you want
15
Now you are all set
16
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?)

17
Thus dynamic web pages!
18
Pull-down menu on IMDB - Internet Movie Database
http//www.imdb.com/
19
HTML for the form on the Internet Movie Database

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

20
The link

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

21
Kinds of scripts you might find

• Microsoft's Active Server Pages (ASP)
• Java
• PHP (Hypertext Preprocessor)
• CGI (Common Gateway Interface)
• We used .php

22
The example PHP version looks like this
23
How to tell the Browser to create a form with
PHP

• My Online Database
• The above is a relative link. The file named
  websql.php is in the same folder as index.html
• Note this calls a .ph script to execute in the
  CS105 public_html folder.

24
What does the html look like for each choice?

• Season2005"How many games won in 2005?

25
.cgi and .php
Here is a view of what happens
26
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.

27
More material will be given in class

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