Creating databases for web applications

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

Testing process • Write scripts using Sublime, other text editor on lab computers or your own computers • 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 • There are debugging tools and also ways to build server on the client computer BUT for this stage in your learning, stick to this way!

Server accounts MAKE SURE YOU CAN DO THIS • upload to your students.purchase.edu account • Add tables to your established MySQL database • May mean to delete existing table • Run your version of the email scripts • Run your copy of the simple quiz based on parallel arrays • Run your own simple quiz

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

HTML5 form validation • New type attributes for form plus other attributes such as max and min for numbers and also required, placeholder,… • Browsers MAY do checking. • Faster than php code would be

Possibilities for type • text • number • email • url • date, datetime, datetime-local, month, time,week • range (Chrome, others present slider) • color • tel • search

But…. • may not be fully-functional for a time so • Need to do checking, especially if the input is used to change or query a database. • Extra credit opportunity to research opinion and status…

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 • You can look up patterns!

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 • The patterns for php start and stop with forward slashes EXCEPT if you want to specify case Insensitive, you can add an i at the end. • php (assume $pattern & $string are variables) • preg_match($pattern, $string) returns true or false • preg_match(($pattern.i), $string) same, but case Insensitive. This is done by making the pattern have an i

Quick test <?php $test=$_GET['test']; $pattern = "/(cat)|(dog)/"; if (preg_match($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-]+)*$/i"; 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 (!preg_match($emailpattern,$email) ){ $oksofar=FALSE; print ("<br>E-mail address given,$email, is not in standard format."); }

if (preg_match ("999-99-9999",$ssn)) { $oksofar=FALSE; print("<br>Please enter a valid social security number."); } if (!preg_match($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 (!preg_match($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 file • 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> NOTE: <?php and ?> can be used to go in and out of php independent of if and else clauses.

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 • CATCH UP • 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. • You can modify your simple quiz to incorporate regular expressions!

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

Next class • [Time in class to work] • Show (modified) quizzes • Show regular expressions

Creating databases for web applications