Creating databases for web applications

Creating databases for web applications Play quizzes Testing process regular expressions: form validation PHP coding handling forms Homework: regular expressions assignment

Class projects • Play something ???

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

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

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

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

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

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-9]{5} -- matches 5 digits [0-9]{1,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

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.

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

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

Quick test <?php $test=$_GET['test']; $pattern = "(cat)|(dog)"; if (eregi($pattern,$test)) { print("Entry $test passed the test"); } else { print("Entry $test failed the test"); } ?>

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

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

<html><head><title>Validation test </title> </head> <body> <h1>Information </h1><hr> <form action="validate.php" method=post> Name: <input type=text name='cname'> <br> Email address: <input type=text name="email"><br> SS#: <input type=text name='ssn' value='999-99-9999' size=11><br> Address: <input type=text name='address'><br> Zip code (5 digit or 5+4 format): <input type=text name="zipcode"><br> <input type=submit value="Send data"> <input type=reset value="Reset data"> </form> </body> </html>

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

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

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.

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

<html><head><title>form handler</title></head> <body> <?php $cname=$_POST['cname']; $address=$_POST['address']; $ssn = $_POST['ssn']; $zipcode=$_POST['zipcode']; $email = $_POST['email']; print ("hello, $cname!");

Email pattern $emailpattern="^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+\.([a-z0-9-]+)*$"; one letter or number followed by any number of periods and letters or numbers followed by @ 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

Social Security number pattern $ssnpattern="(^D|[0-9])[0-9]{2}-[0-9]{2}-[0-9]{4}$"; anchored at both ends. Yes, D is valid.

Zip code $zippattern="^[0-9]{5}(-[0-9]{4})?$"; anchored at both ends exactly 5 numbers and optionally exactly 4 more numbers

Now start checking…. $oksofar=true; if ($cname=="" OR $address=="") { $oksofar=FALSE; print("<br>Please enter a name and an address. "); }

if (!eregi($emailpattern,$email) ){ $oksofar=FALSE; print ("<br>E-mail address given,$email, is not in standard format."); } The eregi (case Insensitive) is a good idea here.

if (ereg ("999-99-9999",$ssn)) { $oksofar=FALSE; print("<br>Please enter a valid social security number."); } if (eregi($ssnpattern,$ssn)) { $oksofar = FALSE; print("<br>Social Security number is not in the proper format."); } • Do the first check, to make sure user put in something

if (!ereg ($zippattern,$zipcode)) { $oksofar=FALSE; print ("<br>Zip code given, $zipcode, is not in standard format."); }

if ($oksofar) { print ("<br>Your data is acceptable."); } ?> </body> </html>

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.

Preview • SQL queriesSELECT field1, field2,… FROM tablename WHERE condition • SELECT pname, score FROM players WHERE score>100 • SELECT * FROM players WHERE pname='Jeanine' • * means all the fields • NOTE: equality test uses just 1 equal sign!

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…

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.

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 <input type=hidden name="submitted" value=TRUE>

php form + handler <? if (isset($_POST['cname'])) { …. all the code in the handler } else { ?> all the code in the form <? }

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

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

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.

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!!!!!

Creating databases for web applications