Creating databases for Web Applications

1
Creating databases for Web Applications

• Continue with php basics emailing
• Introduction to databases.
• Access demonstration. MySQL
• Homework Practice with Access or Open Office
  Base. Use phpMyAdmin. Make posting with unique
  source on MySql.

2
Did you?

• Get anything working?
• Will show code now

3
Reprise PHP

• php files are interpreted by/on/at the server.
• php code within html
• One or more of the following happens
• php interface to operating system is used to do
  things such as interact with databases OR files
  OR email
• HTML is produced (and sent to the client)
• cookie is set and/or accessed

4
Structure
CLIENT Browser (Firefox) Requests URL
SERVER PHP interpreter
SERVER DBMS (MySQL)
Build HTML page
5
php

• echo prints material to the html document to be
  returned/sent to the client
• Can use print
• Extra credit opportunity is there a difference?

6
Look at Flash

• Download source of Catching Bo
• http//newmedia.purchase.edu/Jeanine/as30/chasebo
  thedogemaila.fla

7
(No Transcript)
8
Actions for Catching Bo

• Set up call when player clicks button
• sendbtn.addEventListener(MouseEvent.CLICK,sendemai
  l)
• Start of sendemail function. Doesn't send email
  if player hasn't caught Bo.
• function sendemail(evEvent)
• if (caught"")
• toaddress.text "You didn't catch Bo!"
• else

9

• var requestURLRequest new URLRequest("sendemail
  .php")
• var variablesURLVariables new URLVariables()
• var gameURLString "http//newmedia.purchase.ed
  u/Jeanine/as30/chasebothedogemailb.html"
• variables.body"I caught Bo on "caught".
  Visit "gameURL
• variables.totoaddress.text
• variables.subject"news"
• variables.from fromaddress.text
• //trace("variables.body is "variables.body)
• request.data variables
• request.methodURLRequestMethod.POST
• sendToURL(request)

10
sendemail.php (used for Bo)

• lt?php
• to _POST'to'
• subject _POST'subject'
• body _POST'body'
• headers "From " . _POST'from'
• if (mail(to, subject, body,headers))
• echo("Your message was sent")
• else
• echo("There was a problem.")
• ?gt

11
html to php

• Sets of html (set up) and php files
• sendemailfromhtmlp.html
• calls sendemailp.php
• sendemailfromhtmlg.html
• calls sendemailg.php
• sendemailfromhtmlgfancy.html
• calls sendemailgfancy.php

12
Note

• These all look the same when viewing the
  displayed html
• The source shows that each calls a different php
  file!

13
sendemailfromhtmlg.html

• lthtmlgt
• ltheadgt
• lttitlegtEmail
• lt/titlegt
• lt/headgt

14

• ltbodygt
• ltform action"sendemailg.php"gt
• ltpgtYour email (for reply)
• ltinput type"text" name"from" /gt lt/pgt ltpgt
• To email ltinput type"text" name"to" /gt
  /pgtltpgt
• Subject ltinput type"text" name"subject"
  /gt lt/pgt ltpgt
• Body of message (you may change it) ltbr/gt
• ltTEXTAREA NAME"body" COLS40 ROWS6gt
• This is email sent from my own html/php
  application
• lt/TEXTAREAgt lt/pgt
• ltinput type"submit" value"SEND" /gt
• lt/formgt lt/bodygt lt/htmlgt

15
textarea

• Similar to input
• Used for multi-line
• Can put in default content
• Where should instructions go????

16
sendemailg.php

• lt?php
• to _GET'to'
• subject _GET'subject'
• body _GET'body'
• headers "From " . _GET'from'
• if (mail(to, subject, body,headers))
• echo("Your message was sent")
• else
• echo("There was a problem.")
• ?gt

17
Question

• What is different for the post version?

18
Extra credit possibility

• Figure out how to get an error
• Note the use of mail is an asynchronous
  operation initiated from php (sent to operating
  system on the server)

19
Mechanics in php script

• Need to distinguish html versus php
• Common task will be to generate a string (aka
  character string) made up of html you know ahead
  of time and html generated from variables.

20
phpinfo script

• lthtmlgt
• ltheadgt
• lttitlegtPHP info testlt/titlegt
• lt/headgt
• ltbodygt
• lt?
• echo phpinfo()
• ?gt
• lt/bodygt
• lt/htmlgt

21
Let's do it phpinfo

• http//newmedia.purchase.edu/Jeanine/db/phpinfote
  st.php
• Is this more than we want to know? Is this more
  than we should know????

22
Variables

• Concept in all computing
• Variable 'is' a name that holds a value. Can be
  used in place of a literal value. The value can
  vary over use
• PHP variable names begin with , no spaces,
  avoid punctuation except for _
• Rule long enough for you to remember what it is
  used for and short enough to type.

23
php example

• lthtmlgtltheadgtlttitlegt Test lt/titlegtlt/headgt
• ltbodygt
• lth1gt Welcome to the store lt/h1gt
• lt?php
• .
• Print("lth2gtNew Productslt/h2gt")
• Print( "ltbrgt"newproduct1name)
• ?gt
• lt/bodygtlt/htmlgt

Variable set used
24
php variables within strings

• For all php variables, you can put the variable
  name in a string
• print("The name input was fname")
• php figures out that fname is a variable and
  gets its value.
• NOTE out of habit in using other programming
  languages, sometimes I don't do thatprint ("The
  name input was " . fname)
• NOTE the string concatenation operator is .
• Caution SQL often requires a single quotation
  mark

25
Form data in php

• Built-in functions
• _GET' '
• _POST' '
• If you want to NOT distinguish (considered less
  secure)
• _REQUEST' '
• also includes _COOKIE

Name in form
26
So

• Try writing yet another html file calling a php
  file that uses _Request

27
Checking if a form variable has been set

• Common situation to check if this is first time
• saywhich_at__GET'saywhich'

Prevents error if no value exists
28
random

• rand (a, b) returns a pseudo-random choice from a
  to b, inclusive
• choicerand(0, sizeOf(states)-1)
• The arrays in php (like JavaScript and
  ActionScript) start indexing at zero.

29
Overview

• Useful to think in terms of 3 language domains
  (my terminology)
• client side browser (interpreter of html)
• server side interpreter of asp/JavaScript or php
• (There is another level here for the operating
  system, but this is what the asp objects and the
  php built-in functions variables provide.)
• database interpreter of SQL (by Access or MySQL)

30
Warnings

• Applicable to php and other languages
• To output quotation marks in the HTML, you may
  use single quotes or 'escape' double quotation
  marks
• print ("lta href\"filename\"gt")
• Preview SQL statements require quotation marks
  around values in certain cases.
• SQL statements use a single equals sign () for
  checking for equality. JavaScript and php use
  in IF statements.

31
Claim

• All database management systems share similar
  structure.
• Tables Records Fields
• Records have primary keys
• Records may have fields that hold foreign keys,
  that is, pointers to records in other tables or
  in that table.
• They differ on efficiencies noticeable in
  larger, production systems
• How many simultaneous queries
• Security issues

32
Example (NOT ACCURATE)

• Table of courses
• Each record represents one course.
• Course identifier MAT3530
• Cross-listing NME3520
• Name Creating Databases for Web Applications
• ASSUMING a course has at most one pre-requisite,
  each record has field that
• Pre-req MAT1420
• NOTE the pre-reqs are more complicated, since
  there are many possibilities. This would require
  a new table.

33
Creating database

• Create tables
• Create / specify relations.
• If database to be used on stand-alone computer,
  create Forms, Queries, Views. Instead, we will
  create programs (html and php scripts) to do this.

34
Table

• Define what is a record in the table what are
  the fields?
• What is the information associated with this one
  thing?
• What is the data type of each field?
• If the databases will contain many records, may
  be important to be careful as to size
• Is there an intrinsic primary key (unique for the
  record in the table) or should system supply one?
• Fields can have at most one value (can be null)
• Multiple values means you need another table

35
Caution

• Defining (designing) a database is not trivial.
• May require revision.
• Interview process with clients/customers/systems
  owners can take time.

36
What should we do?

• Budget (past and future expenses, dates)
• Favorite web sites
• Music collection (with features)
• Courses taken (grades) / will take
• Candidates
• ?

37
Access

• Part of Microsoft Office suite

38
Open Office Base

• Open source product part of Open Office
• Very similar to Access

39
Create table

• Create table Use Design view
• To specify primary key. Give field a name
• Access click in left column to specify as
  primary key. Autonumber is a datatype
• Base right click to specify as primary key.
  Select Integer. Select Auto in property panel.
• Wizards are useful for common business
  applications
• Create form Use Wizard
• Use form to populate (that is, put data into
  table)
• Create report

40
Alternative spreadsheet to db

• Extra credit? Recovery Act
• http//www.hhs.gov/recovery/reports/index.html
• Click on weekly report Excel file
• In Excel Massage file (remove first few rows)
  (Protected cells may cause problems.)
• Open Access. File/Open. Browse to the xls file.
  Follow directions.
• Create query get data on NY, NJ, CT.

41
Spreadsheet vs Database

• Scale DBMS can handle large amounts of
  information
• Efficiency DBMS can handle different datatypes
• DBMS have facilities for MANAGING access by
  multiple users
• DBMS supports multiple tables and queries across
  the multiple tables.
• MySQL (and other DBMS) have easier (?)
  connections to php and other programming
  languages
• Extra credit opportunity do posting on php and
  Excel or php and Open Office or VB.net xls,
  etc. Give explanation / examples, NOT JUST THE
  LINK!

42
Trivia game

• Questions table
• Question id
• Question text
• Question answer
• Question category
• Question score
• Player table
• History table

43
Trivia database players

• Player id
• Player name
• Player password

44
Trivia database history

• Event id
• Player id
• Question id
• Result (right or wrong)
• Time stamp

45
Entity Relationship Diagram
Questions Ques id Text Answer Category score
PlayersPlayer idPlayer namePassword
HistoryhistoryIDQues idPlayer idResultTime
stamp
46
Access

• Create relationships using the Lookup Wizard for
  the Data Type and specifying getting values from
  a table.

47
Finalize web accounts (for MySql)

• Use secure ftp
• REMEMBER YOUR MYSQL ACCOUNT NUMBER PASSWORD

48
phpMyAdmin

• Will do this with one of your accounts or mine if
  I get one!

49
Working environment

• You will
• Write php scripts just to create tables. These
  scripts may just be executed one time.
• You will
• create html and php files using Dreamweaver or
  Textpad or Textwriter or equivalents.
• upload to your account using suggested ftp
• Test
• There is an alternative setting up your computer
  to be a server. This is fairly complex.

50
Reprise

• Useful to think in terms of 3 language domains
  (my terminology)
• client side browser (interpreter of html)
• server side interpreter of asp/JavaScript or php
• (There is another level here for the operating
  system, but this is what the asp objects and the
  php built-in functions variables provide.)
• database interpreter of SQL (by Access or MySQL)

51
Homework

• Get html email examples working if you haven't
  done so
• _Request approach
• Practice with Access
• Practice with Open Office Base
• Extra Credit Make posting comparing the two.
• Required Find a good source on MySql and make
  posting.
• Continue review of HTML and ActionScript