Last Time

1
Last Time
2
Last Time

• HTML is one example of an SGML based language
• SGML defined a way of describing markup languages
• XML is a simplified form of SGML
• XML is
• Text-based
• Open source
• Platform independent
• A way of describing hierarchically structured
  data

3
Last Time

• XML doesnt care about what data means, it only
  describes its structure
• XML employs custom made tags
• The structure of these tags is described in a
  schema (XML Schema/DTD)
• XML is parsed
• XLS describes the presentation of a XML document
  (HTML)
• XSLT transforms one XML document to another

4
Announcements
5
Announcements

• No class next week !!
• Grades for assignment 1 next week
• Assignment 2
• Create a database for the game of assignment 1
• Employ server side scripting and a database
• Today!!

6
Assignment 2

• Log in with email-address
• View your high-score and the best 3 scores thus
  far
• After gameplay, receive an email with your
  results.

7
Server Side Technology
8
Overview

• What is
• Dynamic HTML
• Server Side scripting
• CGI
• PHP
• Relational Database
• Querying
• SQL?

9
Dynamic Websites

• Classical hypertext navigation occurs among
  "static" documents, and, for web users, this
  experience is reproduced using static web pages.
• web navigation can also provide an interactive
  experience that is termed "dynamic". Content
  (text, images, form fields, etc.) on a web page
  can change, in response to different contexts or
  conditions.

10
Dynamic Webpages

• Using client-side scripting to change interface
  behaviors within a specific web page, in response
  to mouse or keyboard actions. In this case the
  dynamic behavior occurs within the presentation.
  (2 weeks ago)
• Using server-side scripting to change the
  supplied page source between pages, adjusting the
  sequence or reload of the web pages or web
  content supplied to the browser. (Today)
• Determined by
• data in a posted HTML form
• parameters in the URL
• the type of browser being used
• the passage of time
• or a database or server state.

11
Server Side Scripting

• a web server technology in which a user's request
  is fulfilled by running a script directly on the
  web server to generate dynamic HTML pages.
• Primary advantage the ability to highly
  customize the response based on the user's
  requirements, access rights, or queries into data
  stores.

12
Server Side Scripting

• The browser sends an HTTP request.
• The server retrieves the requested script or
  program.
• The server executes the script or program which
  typically outputs an HTML web page. The program
  usually obtains input from the query string or
  standard input which may have been obtained from
  a submitted web form.
• The server sends the HTML output to the client's
  browser.

13
Server Side Technologies

• CGI Common Gateway interface
• Executes external programs or scripts on
  webserver
• Direct Execution
• Executes (embedded) scripts directly within the
  webserver.

14
CGI

• most generallystandard for interfacing
  information servers (e.g. web-servers) to
  external applications
• most commonly used as astandard way for a
  web-server for passing request data to an
  application program, and to receive data back for
  the user
• the information server acts as a gateway, hence
  the name
• CGI is an interface, not a language!

15
CGI example request

16
CGI example request

17
CGI example database request

18
CGI and the Webserver

• Q how does a web server know that a URL points
  to a CGI-based program that must be executed?
• A it can be configured to map certain URLs to
  CGI-based programs
• for example, the cgi-bin directory as
  inhttp//www.mediatech.nl/cgi-bin/hello.pl
• the web server will then recognize the resource
  as a program, execute it, and pass it the HTTP
  request data

19
CGI Programs

• CGI program is a confusing term (a.k.a. CGI
  script, CGI application)
• it is not a program written in the CGI language
• we use it to indicate executable programs written
  in any language that use the CGI interface for
  receiving HTTP request data
• Perl, C, C, Java, JavaScript, UNIX sh,
• usually, a scripting language is used
  (interpreted, not compiled)
• Perl is very popular for CGI scripting, because
  of its regular expressions that make processing
  of CGI data easier (they are used for handling of
  text data mostly)

20
CGI Output

• how is data retrieved from a CGI program?
• the CGI program generates output in some form
• the web-server adds HTTP headers to form a valid
  HTTP response
• the result is sent to the client

21
CGI Output

• output of CGI programs begins with a small header
• in same format as HTTP headers
• any headers other than server directives are sent
  directly back to client
• common server directivesContent-type specifies
  output content typeLocation redirect to
  another URLStatus indicates HTTP status code

22
CGI Input

• how is data passed from a web request to a CGI
  program?
• the server will
• pass HTTP request headers as environment
  variables
• pass HTTP request body to the programs standard
  input
• pass query string part of URL as environment
  variable and translate it to command line
  arguments of CGI program callhttp//www.car.com/
  products.pl?typepeugeotyear1991

23
CGI Environment Variables

• environment variables specific to the request
  being fulfilled by the web server
• REQUEST_METHODhow was the request made? For HTTP
  this is GET, POST, HEAD,
• QUERY_STRINGwhatever follows ? in the URL
  which referenced the program
• CONTENT_TYPE, CONTENT_LENGTHfor queries with
  information attached by the client, such as HTTP
  POST and PUT, this is the content type of the
  data and its length

24
CGI Environment Variables

• DOCUMENT_ROOT/home/httpd/wwwhome/cswwwGATEWAY_I
  NTERFACECGI/1.1HTTP_ACCEPTimage/gif,
  image/x-xbitmap, image/jpeg, image/pjpeg,
  HTTP_ACCEPT_ENCODINGgzip, deflateHTTP_ACCEPT_L
  ANGUAGEen-usHTTP_CONNECTIONKeep-AliveHTTP_HOST
  www.liacs.nlHTTP_USER_AGENTMozilla/4.0
  (compatible MSIE 5.5 Windows NT
  5.0)PATH/sbin/usr/sbin/bin/usr/bin/usr/X11R6
  /binQUERY_STRINGflowertulpREMOTE_ADDR213.17.5
  9.82REMOTE_PORT3039REQUEST_METHODGETREQUEST_U
  RI/joostd/cgi-bin/envs.cgi?flowertulpSCRIPT_FI
  LENAME/home/httpd/wwwhome/joostd/cgi-bin/envs.cgi
  SCRIPT_NAME/joostd/cgi-bin/envs.cgiSERVER_ADDR
  132.229.44.15SERVER_ADMINwww_at_liacs.nlSERVER_NA
  MEwww.liacs.nlSERVER_PORT80SERVER_PROTOCOLHTT
  P/1.1SERVER_SOFTWAREApache/1.3.12 (Unix) (Red
  Hat/Linux) PHP/3.0.18 mod_perl/1.23

25
CGI Input POST

• POST /icecream.pl HTTP/1.1...Content-Type
  application/x-www-form-urlencodedContent-Length
  31scoops2flavorscherryvanilla
• for requests with information attached after the
  header (e.g. HTTP POST or PUT), the information
  is sent to the script on standard input
• standard input is a standard way in UNIX or DOS
  to pass data to any program. Perl handles it
  also.
• basically, the server sends a series of bytes
  (CONTENT_LENGTH) to the CGI program file

26
CGI Drawbacks

• decoding of URLs can be a hasslethe program
  must do this to access the individual chunks of
  data
• inefficientfor every request a new copy of the
  program is started this can become a very big
  load on the server
• Security Issues
• too low level
• solutions
• more efficient CGI (FastCGI, mod_perl)
• server side inside out programming methods
  (ASP, PHP, )

27
Printing a dynamic date

• HTML is embedded within the script-code
• becomes unreadable if there is more HTML than code

print("ltHTMLgt") print("ltH1gtSamplelt/H1gt") var
now new Date() print("It is now ltstronggt" now
"lt/stronggt") print("lt/HTMLgt")
28
Printing a dynamic date using PHP

• script-code is embedded in HTML instead of other
  way around!
• inside-out programming
• remember JavaScript?

ltHTMLgt ltH1gtSamplelt/H1gt It is now ltstronggt lt?php
echo date ?gt lt/stronggt lt/HTMLgt
29
PHP

• Personal Home Page Tool
• PHP Hypertext Preprocessor
• server-side programming (scripting) language
• freely available under open-source license
• alternative to Microsofts Active Server Pages
  (ASP)
• inside-out programming
• syntax is a mix of Java, C and Perl languages
• files usually have the extension .php so that the
  web server knows it should be processed by PHP
  interpreter.

30
PHP scenario
web server
DB
PHP interpreter
response

• Web
• client

run script
request
PHP file
ice.php
31
PHP code example

• script is included between lt?php ... ... ... ?gt
• echo statement just prints output to HTML
  document
• HTML tags can be in the output
• every statement must end with character
• lt? ... ... ... ?gt is also allowed

ltHTMLgt ltHEADgt ... lt/HEADgt ltBODYgt
ltH1gtExamplelt/H1gt lt?php echo Tim teaches
ltBgtwebtechlt/Bgt! ?gt Today is lt?php echo
date(l, F dS Y.) ?gt lt/BODYgt lt/HTMLgt
32
PHP comments

• comment between / / or after //
• note this is not HTML comment, but PHP comment!

ltHTMLgt ltHEADgt ... lt/HEADgt ltBODYgt Dutch
lesson number 1ltBRgt lt?php /
Here is a useful sentence. / echo
ltEMgtTwee kroketten, alstublieft!lt/EMgt //
It means Two kroketten, please. ?gt
lt/BODYgt lt/HTMLgt
33
PHP variables

• no declaration required, type is automatically
  deduced
• variable names start with character
• they are case-sensitive, beware!
• Tim ! tim

dutch_dessert Vanillevla // string x
28 // integer pi 3.1415 //
double whatever TRUE // boolean
34
PHP string variable interpolation

• with double quotes (), variable names within the
  text string are substituted for their value
• with single quotes () they are not
• this is very useful!
• output

dutch_dessert vanillevla scottish_food
Haggis echo Willem-Alexander loves
dutch_dessertltBRgt echo Why would anyone like
scottish_food?
Willem-Alexander loves vanillevlaltBRgt Why would
anyone like scottish_food?
35
PHP strings

• to include weird characters in a text string, you
  may have to use an escape character
• the following statements will not workcity
  s Gravenhage // problem w/ quotesprice
  My car costs 200 // problem w/ -char
• instead, docity s\ Gravenhage // escape
  charactercity s Gravenhage // double
  quotesprice My car costs \200 // escape
  character

36
PHP string concatenation

• string concatenation (pasting strings together)
• use the . operator
• example
• output

echo Koninginne . dag . ltBRgt first
fiets second zadel echo ltBgt . first .
second . lt/Bgt
KoninginnedagltBRgt ltBgtfietszadellt/Bgt
37
PHP arrays

• arrays can contain elements of different types

myarray array(een, 2, drie) echo
myarray0 // outputs een echo
myarray1 // outputs 2 myarray1
twee // changes element myarray3
vier // creates new element myarray
vijf // adds new element // to array
end echo myarray4 // outputs vijf
38
PHP associative arrays

• indices of array can be strings also !!!
• these are associative arrays
• a bit strange, but quite useful
• outputJan-Peter is from the Netherlands

countryJan-Peter the Netherlands countr
yMaxima Argentina countryYiwei
China name Jan-Peter echo name is
from countryname
39
Back to HTML forms

• GET requestGET /icecream.php?scoops2flavorch
  erry HTTP/1.0Referer http//www.ml.com/shop.html
  Accept /
• POST requestPOST /icecream.php
  HTTP/1.0Referer http//www.ml.com/shop.htmlAcce
  pt /Content-type application/x-www-form-urlen
  codedContent-length 22scoops2flavorcherry

40
PHP GET request

• PHP automatically creates an associative array of
  all name-value pairs from the query part of a
  requested URL, called _GET

lt?php howmany _GETscoops which
_GETflavor echo So, you want howmany
scoops of which icecream ?gt
41
PHP POST request

• PHP automatically creates an associative array of
  the URL-encoded name-value pairs in the
  entity-body of a POST request, called _POST

lt?php howmany _POSTscoops which
_POSTflavor echo Yes, I want howmany
scoops of which icecream ?gt
42
PHP Request

• alternatively, you can use _REQUEST which
  contains both the GET and POST request name-value
  pairs

lt?php fname _REQUESTfirstname lname
_REQUESTlastname echo Welcome to this
webpage, fname lname ?gt
43
PHP Much used functionsmail()

• bool mail ( string to, string subject, string
  message , string additional_headers , string
  additional_parameters )
• To
• user_at_example.comuser_at_example.com,
  anotheruser_at_example.comUser ltuser_at_example.comgtUser
  ltuser_at_example.comgt, Another User
  anotheruser_at_example.com
• SUBJECT
• No newline!!

44
PHP Much used functionsforeach()

• This simply gives an easy way to iterate over
  arrays
• foreach (array_expression as value)
• statement
• foreach (array_expression as key gt value)
• statement
• The first form loops over the array given by
  array_expression. On each loop, the value of the
  current element is assigned to value and the
  internal array pointer is advanced by one (so on
  the next loop, you'll be looking at the next
  element).
• The second form does the same thing, except that
  the current element's key will be assigned to the
  variable key on each loop.
• Useful for reading out database results!

45
PHP Much used functionscount()

• int count ( array var)Returns the number of
  elements in var
• a0 1
• a1 3
• a2 5
• result count(a)// result 3

46
PHP and JavaScript

• PHP can create dynamic javascript functions.
• Therefore, designers are able to make
  dynamic(ally built) pages that can behave
  dynamically.
• For example a response function for all allowed
  actions for a certain logged in user.

47
PHP and JavaScript

• Foreach (students as row)
• echo function enable.rownumber.( )
• echo
• echo studentimage.rownumber..classNamevi
  sible
• echo

48
PHP and Databases

• PHP has got a number of very convenient functions
  for database use
• In particular for MySQL

49
What is a database?

• A structured collection of related data
• An filing cabinet, an address book, a telephone
  directory, a timetable, etc.
• In a management system, your Database is your
  collection of related tables

50
DBMS DataBase Management System

• Piece of software that helps you
• Storing data
• Changing data
• Requesting data
• Use the DB simultaneously
• Examples
• MS Access
• Oracle
• MySQL

51
Data vs. Information

• Data a collection of facts made up of text,
  numbers and dates
• Murray 35000 7/18/86
• Information - the meaning given to data in the
  way it is interpreted
• Mr. Murray is a sales person whose annual salary
  is 35,000 and whose hire date is July 18, 1986.

52
Database Structure

• Table
• A set of related records
• Record
• A collection of data about an individual item
• Field
• A single item of data common to all records

Name Barry HarrisCollege MedicineTel 392-5555
Name Barry Harris
53
Table Example

Fields
Records
54
Relational Databases

• A relational database is a collection of tables
  from which data can be accessed in many different
  ways without having to reorganize the database
  tables.
• That is, once relationships are created, tables
  can talk to each other. We can link (relate)
  the tables to find
• Which doctors are seeing a patient
• Which students are in which class
• Which item is selling the most on Fridays

55
Relational Databases Design Rules

• Data is broken down into Smallest Logical Parts
• Putting all of the home address in one field may
  make for convenient data entry, but it makes it
  very difficult to work with the data. For
  example, what if I needed to sort by City or Zip
  Code? Pulling fields together is fairly simple,
  pulling them apart is very difficult.

56
Relational Databases Design Rules

• Unique Field Names
• A DBMS wont let you use the same field name
  twice in one table but it can become confusing to
  people doing data entry if you are not clear.
  Try to keep a consistent naming convention.

57
Relational Databases Design Rules

• Unique Field Names
• You also want to be aware of the field names
  across tables. For example several tables may
  use the Field FirstName. When you use those
  fields in other parts of the database things can
  become very confusing very quickly.

58
Relational Databases Design Rules

• No Calculated or Derived Fields
• If we wanted to see how long an employee had been
  working with us, we can calculate their Length
  Employed by subtracting their hire date from
  todays date. However, since todays date is
  always changing, this data very quickly becomes
  stagnant.

59
Relational Databases Design Rules

• Unique Records
• If you dont have unique records, your database
  cant tell which record you may be referring to.

60
Primary Keys

• To ensure that each record is unique in each
  table, we can set one field to be a Primary Key
  field.
• A Primary Key is a field that that will contain
  no duplicates and no blank values.
• Looking at the table above, what would be the
  best Primary Key?

61
Primary Keys

• While each column in this particular data set has
  unique data, the field that will work best for us
  is GL (GatorLink). Many employees will work for
  the same college, have the same last name and
  possibly even share telephone numbers, but each
  employee should have a unique GatorLink ID.
• When there is not a unique field in your data
  set, you can use an AutoNumber. Most DBMS can
  create incremented or random AutoNumbers for your
  primary key.

62
Database Queries

• Whenever you view a certain portion of a
  database you are querying the database
• The portion of data you view (the result) is
  called the query-result
• The request for data you made is called a query

63
SQL Structured Query Language

• Need for a standard way to create and query
  databases
• SQL is a language for accomplishing this
• Standardized by both ANSI and ISO
• Widely supported by DBMSs

64
Basic SQL commandos

• Creating tables with CREATE
• Adding data with INSERT
• Viewing data with SELECT
• Removing data with DELETE
• Modifying data with UPDATE
• Destroying tables with DROP

65
CREATE

• Generic form
• CREATE TABLE tablename (
• column_name data_type attributes,
• column_name data_type attributes,
• )
• Table and column names cant have spaces or be
  reserved words like TABLE, CREATE, etc.

66
CREATE example

• Name Character
• Address Character
• Company Character
• Phone Number Character
• URL/Web Page Character
• Age Integer
• Height Real (float)
• Birthday Date
• When we added the entry Timestamp

67
CREATE instance

• CREATE TABLE contacts (
• Name VARCHAR(40),
• Address VARCHAR(60),
• Company VARCHAR(60),
• Phone VARCHAR(11),
• URL VARCHAR(80),
• Age INT,
• Height FLOAT,
• Birthday DATE,
• WhenEntered TIMESTAMP
• )

68
INSERT

• Generic Form
• INSERT INTO tablename (column_name,)
• VALUES (value,)

69
INSERT example

• INSERT INTO contacts (contactid,name,address,compa
  ny,phone,url,age,height,birthday,whenentered)
• VALUES
• (1,Joe,123 Any St.,ABC,
• 800-555-1212,http//abc.com,30,1.9,6/14/197
  2,now())

70
INSERT Partial Record

• INSERT INTO contacts (contactid,name,phone)
• VALUES (2,Jane,212-555-1212)

71
SELECT

• Generic Form
• SELECT column, FROM table, WHERE condition
  GROUP BY group_by_expression HAVING condition
  ORDER BY order_expression
• The most used command (QUERY!)
• Probably the most complicated also
• If used improperly, can cause very long waits
  because complex computations

72
SELECT simple examples

• SELECT FROM contacts
• Display all records in the contacts table
• SELECT contactid,name FROM contacts
• Display only the record number and names
• SELECT DISTINCT url FROM contacts
• Display only one entry for every value of URL.

73
SELECT WHERE

• The WHERE subclause allows you to select
  records based on a condition.
• SELECT FROM contacts WHERE agelt10
• Display records from contacts where agelt10
• SELECT FROM contacts WHERE age BETWEEN 18 AND
  35
• Display records where age is 18-35

74
SELECT ORDER BY

• The ORDER BY clause allows you to sort the
  results returned by SELECT.
• SELECT FROM contacts
• ORDER BY company
• SELECT FROM contacts
• ORDER BY company, name

75
GROUP BY

• The GROUP BY clause allows you to group results
  together with aggregate functions
• AVG(), COUNT(), MAX(), MIN(), SUM()
• COUNT DISTINCT

76
SELECT JOINS

• SELECT name,phone,zip FROM people, phonenumbers,
  address WHERE people.addressidaddress.addressid
  AND people.idphonenumbers.id

77
DELETE

• Generic Form
• DELETE FROM table WHERE condition
• DELETE FROM contacts WHERE agelt13

78
UPDATE

• Generic Form
• UPDATE table SET columnexpression
• WHERE condition
• UPDATE contacts SET companyAOL
• WHERE companyTime Warner

79
DROP

• Generic Form
• DROP TABLE tablename
• DROP TABLE contacts

80
PHP MyAdmin

• For creation, deletion etc of a database we
  employ external software
• We dont need to write specific creation PHP
  scripts
• PHP MyAdmin is an excellent open source project.
• It is a website that runs in php on the webserver
  and does all this tasks for you.
• Only database views and updates are done through
  your own scripts
• Viewing and storing of highscores!

81
PHP and MySQL opening and selecting databases

• link mysql_connect('mysql_host', 'mysql_user',
  'mysql_password')
• echo 'Connected successfully'
• mysql_select_db('my_database')

82
PHP and MySQL Querying

• query 'SELECT FROM my_table'
• result mysql_query(query)

83
PHP and MySQL Displaying data

• echo "lttablegt"while (line mysql_fetch_array(
  result, MYSQL_ASSOC))    echo "lttrgt"  
  foreach (line as col_value)        echo
  "lttdgtcol_valuelt/tdgt"      echo
  "lt/trgt"echo "lt/tablegt"

84
PHP and MySQL Closing Connection

• // Free resultsetmysql_free_result(result)
• // Closing connectionmysql_close(link)

85
Next Time

• In 2 weeks!
• (Streaming) Media

86
Homework

• The material for Lecture 5
• This will appear on the site today!