Creating databases for web applications

1
Creating databases for web applications

• Play quizzes
• Testing process
• regular expressions form validation
• PHP coding
• handling forms
• Homework regular expressions assignment

2
Class projects

• Play something ???

3
Testing process

• Write scripts using Dreamweaver, Textwrangler,
  NotePad, Textpad, etc. on lab computers or your
  own computers
• Do not UPDATE links
• use Filezilla or other secure ftp program to
  upload html files and script files
• use browser to go to appropriate URL

4
Testing

• is not easy.
• Need to upload files to test.
• Need to confirm the state of the databases,
  cookies, etc.
• May need to erase table (scary) and re-enter
  information

5
Server accounts

• MAKE SURE YOU CAN DO THIS
• upload to your students.purchase.edu account
• create an MySql database

6
Variables

• php does not require you to declare a variable
  before use.
• If you do not set (initialize) a variable, php
  assumes NULL (equivalent to false, the empty
  string, or 0).
• Can use function isset, for example
• isset(_POST' ')
• REMEMBER variables in php start with .

7
Regular expressions

• Used to check for strings within strings and/or
  confirm format
• General procedure there is a string to be
  checked and a pattern.
• php pattern is delimited by " "
• alternative is "/ /" This is required when
  using php_match
• "Regular expressions" represents a language all
  by itself independent of php

8
Examples

• (cat) -- matches cat at the start of a string
• (cat) -- matches cat at the end of a string
• (cat)(dog) -- matches cat or dog in the string
• 0-9 -- matches any digit
• 0-95 -- matches 5 digits
• 0-91,2 --matches 1 or 2 digits
• a-z? -- matches 0 or 1 letter
• a-z -- matches 0 or any number of letters
• a-z -- matches 1 or more letters
• . -- matches any single character

9
More complex

• ((cat)(dog))
• matches cat, catcat, catdog, dog, dogdog,
  catdogcat,
• j matches a string starting with a j
• a.z matches a string starting with an a and
  ending with a z, with at least one character but
  any number of characters in between.

10
Escaping characters

• \. -- matches a period. Other things
  need to be 'escaped' also, such as quotation
  marks.
• \\ -- seems to be necessary in php to get
  an actual dollar sign

11
Regular expression functions

• php (assume pattern string are variables)
• ereg(pattern, string) returns true or false
• eregi(pattern, string) same, but case
  Insensitive
• php_match(pattern, string) pattern must have
  slashes

12
Quick test

• lt?php
• test_GET'test'
• pattern "(cat)(dog)"
• if (eregi(pattern,test))
• print("Entry test passed the test")
• else
• print("Entry test failed the test")
• ?gt

13
Procedure

• Write quicktest.php script, setting the pattern
  with the pattern you want to test.
• Upload to server
• Test using a direct call with a query string

14
(No Transcript)
15
(No Transcript)
16
(No Transcript)
17
Review Form handling

• Two file method form in HTML and handler as
  distinct asp/php file
• This example form handler just checks the input

18

• lthtmlgtltheadgtlttitlegtValidation test lt/titlegt
  lt/headgt
• ltbodygt
• lth1gtInformation lt/h1gtlthrgt
• ltform action"validate.php" methodpostgt
• Name ltinput typetext name'cname'gt ltbrgt
• Email address ltinput typetext name"email"gtltbrgt
• SS ltinput typetext name'ssn'
  value'999-99-9999' size11gtltbrgt
• Address ltinput typetext name'address'gtltbrgt
• Zip code (5 digit or 54 format) ltinput
  typetext name"zipcode"gtltbrgt
• ltinput typesubmit value"Send data"gt ltinput
  typereset value"Reset data"gt
• lt/formgt lt/bodygt lt/htmlgt

19
(No Transcript)
20
Form handling basics

• php the form data is accessible using the _POST
  collection.
• NOTE older versions of php allowed use of
  cname, etc. for post, get, cookie data.
• This was considered less secure.
• Can use _REQUEST which will return get or
  post data

21
Overview of form handler

• obtain the form input
• greet user by name (cname)
• construct the patterns
• confirm name and address given (to be precise,
  check if name is empty string OR address is empty
  string)
• use patterns to confirm email, ssn, zipcode
• for any problem, let user know
• if all okay (indicated by a variable remaining
  TRUE), let user know

22
oksofar coding

• Comments apply to php and other languages
• oksofar is example of a flag flag up or down
• oksofar starts off true
• If anything happens, it is set to false.
• It may be set to false more than once.
• At the end, if it is still true, something
  happens.

23
Data to be validated

• Name Anything but a blank
• Address Anything but a blank
• SSN Check for change
  Check pattern
• Email Check pattern
• Zipcode Check for 5 or 5 plus 4 nums

24

• lthtmlgtltheadgtlttitlegtform handlerlt/titlegtlt/headgt
  ltbodygt
• lt?php
• cname_POST'cname'
• address_POST'address'
• ssn _POST'ssn'
• zipcode_POST'zipcode'
• email _POST'email'
• print ("hello, cname!")

25
Email pattern

• emailpattern"_a-z0-9-(\._a-z0-9-)_at_a-z0-
  9-\.(a-z0-9-)"
• one letter or number followed by any number of
  periods and letters or numbers followed by _at_
  followed by 1 or more letters or numbers followed
  by a period followed by 1 or more letters or
  numbers. Note the \ is an escape character for
  the period

26
Social Security number pattern

• ssnpattern"(D0-9)0-92-0-92-0-94
  "
• anchored at both ends. Yes, D is valid.

27
Zip code

• zippattern"0-95(-0-94)?"
• anchored at both ends
• exactly 5 numbers and optionally exactly 4 more
  numbers

28
Now start checking.

• oksofartrue
• if (cname"" OR address"")
• oksofarFALSE
• print("ltbrgtPlease enter a name and an address.
  ")

29

• if (!eregi(emailpattern,email) )
• oksofarFALSE
• print ("ltbrgtE-mail address given,email, is not
  in standard format.")
• The eregi (case Insensitive) is a good idea
  here.

30

• if (ereg ("999-99-9999",ssn))
• oksofarFALSE
• print("ltbrgtPlease enter a valid social security
  number.")
• if (eregi(ssnpattern,ssn))
• oksofar FALSE
• print("ltbrgtSocial Security number is not in the
  proper format.")
• Do the first check, to make sure user put in
  something

31

• if (!ereg (zippattern,zipcode))
• oksofarFALSE
• print ("ltbrgtZip code given, zipcode, is not in
  standard format.")

32

• if (oksofar)
• print ("ltbrgtYour data is acceptable.")
• ?gt
• lt/bodygt
• lt/htmlgt

33
Regular expressions

• Also can be used to make substitutions
• READ UP ON THIS using sources posted.
• If you have a comment on a source, make a reply
  post.

34
Preview

• SQL queriesSELECT field1, field2, FROM
  tablename WHERE condition
• SELECT pname, score FROM players WHERE scoregt100
• SELECT FROM players WHERE pname'Jeanine'
• means all the fields
• NOTE equality test uses just 1 equal sign!

35
LIKE

• MySQL (and other DBMS) support regular expression
  calculations (REGEXP) and also the operator LIKE
• SELECT author, joketext FROM jokes WHERE joketext
  LIKE "knock"
• Returns the author and joketext fields of all
  records in which the joketext contains the string
  knock anywhere in it

36
Form handling

• Does something with the information beyond
  validating it!
• could be accessing and, perhaps, changing a
  database or flat file,
• doing more extensive calculations,
• and/or using such information to construct a
  customized html page for the client.
• My example did that in a small way by greeting
  the client by name
• Will show how to create and use a cookie to do
  that.

37
Form handling in one form

• In place of 2 files
• 1 (perhaps pure html) with the form
• 1 distinct form handling file, combine into one
• Use presence or absence of a variable set by the
  form
• one of the input values or
• could use a special input just for this purpose
• ltinput typehidden name"submitted" valueTRUEgt

38
php form handler

• lt?
• if (isset(_POST'cname'))
• . all the code in the handler
• else
• ?gt
• all the code in the form
• lt?

39
Combining form handlers

• Pro
• everything in one file, so easier to change
  things
• Con
• general rule in programming divide tasks into
  smaller tasks

40
Homework

• Design (and test) regular expressions to search
  for each of the following
• a string with "curley", "larry", or "moe"
  somewhere in it. Case does not matter.
• a dollar amount for example, accept 2.59, 10,
  1,200 and reject 1.2345, 3.4.5.
• Valid date in MM/DD/YYYY or MM/DD/YY format (for
  example, 14/2/2001 would not be acceptable. See
  if you can allow 1/4/04 as well as 01/04/2004.
• For state caps quiz New York or NY, St. Paul or
  Saint Paul

41
Homework, cont.

• Design (create) 3 questions for a quiz show game
  and design regular expressions that validate the
  answers. The challenge is to be no more and no
  less exacting than a human checker.

42
Homework, continued

• Modify the quicktest.php script to test the
  patterns.
• You may be called on in class to show and explain
  your work!
• Use on-line resources (but try it first on your
  own and be prepared to explain).
• THIS COUNTS!!!!!