Creating Databases

Creating Databases Uploading Files. Reading & writing files. Homework: Starting planning ‘original’ project.

Uploading files using php • What if you have an application, such as origami store, video portal, etc., and you want to provide a way for users to upload files to the server? • Now, users are not that trustworthy, so need to put in checks!!! • Some php installations may not allow this at all. • CTS was reluctant.

Example • http://socialsoftware.purchase.edu/jeanine.meyer/fileupload1.html • HTML does much of the work for us: a special type of input plus special attributes in the form tag. • This invokes fileupload2.php and stores image files under a certain size in the subfolder uploads

fileupload1.html <html> <head><title>Get file name </title></head> <body> Upload image file: <form action="fileupload2.php" method="post" enctype="multipart/form-data"> <label for="file">Filename:</label> <input type="file" name="ufile" id="file" size="100"/> <br /> <input type="submit" name="submit" value="Submit" /> </form> </body> </html>

fileupload2.php • My code displays more information that you would want in a production system!!! • You would want the checking. • Information on the uploaded files are in a php variable calls $_FILES. The uploaded files are in a temporary directory. My code moves it into a subfolder of the folder of the current script.CTS may have made special permissions for this to work.

<?php echo "Script path is $basename <br/>"; echo "File type is " . $_FILES["ufile"]["type"] . "<br/>"; echo "File size is " . $_FILES["ufile"]["size"]. "<br/>" ; if ((($_FILES["ufile"]["type"] == "image/gif") || ($_FILES["ufile"]["type"] == "image/jpeg") || ($_FILES["ufile"]["type"] == "image/pjpeg")) && ($_FILES["ufile"]["size"] < 20000000)) {

if ($_FILES["ufile"]["error"] > 0) { echo "Return Code: " . $_FILES["ufile"]["error"] . "<br />"; } else { echo "Upload: " . $_FILES["ufile"]["name"] . "<br />"; echo "Type: " . $_FILES["ufile"]["type"] . "<br />"; echo "Size: " . ($_FILES["ufile"]["size"] / 1024) . " Kb<br />"; echo "Temp file: " . $_FILES["ufile"]["tmp_name"] . "<br />"; if (file_exists($_FILES["ufile"]["name"])) { echo $_FILES["ufile"]["name"] . " already exists. "; } else { $target = "uploads/" . $_FILES["ufile"]["name"] ; echo "The length of $target is " . strlen($target) . "<br/>"; move_uploaded_file($_FILES["ufile"]["tmp_name"],$target); echo "Stored as: " . $target; } } } else { echo "Invalid file"; } ?>

Files • Before there were databases, there were simple files. • What about using a file for data? • Example application: top best scores. • My demonstration application: • http://socialsoftware.purchase.edu/jeanine.meyer/bestscores.html • [Simply] requests player name and score and may or may not add to current set of top 5.

bestscores.html <!DOCTYPE html> <html> <head> <title>Input new scores</title> </head> <body> <form action="bestscores.php"> Player <input type="text" name="player" /> <br/> Score <input type="number" name="score" /> <br/> <input type="submit" value="ENTER"/> </form> </body> </html>

Note • The score input is a piece of text. • Some browsers may check that that text represents a number, but it is still text. • My script will create a scores.txt file if one does not already exist in the subfolder uploads. • My script has debugging messages that should be removed for a production system.

strategy • Open [connection to] file for reading. • Read in the whole file (5 records) into an array variable $data. Close the connection to the file. • Each record is name,score • Use explode to get the two different things. • Convert the score to a number • Produce an array $scores of numbers. • Compare intval($newscore) to elements in $scores. • Find the first one smaller than the proposed new score. Manipulate $data by inserting a record holding “$newname,$newscore\n” • Open [connection to] file for writing. For php, writing means erasing whole file and then re-writing it. • Write out $data items as records.

bestscores.php <html> <head><title>Best scores </title> </head> <body> <?php $newname = $_GET['player']; $newscore = $_GET['score']; $filen = "uploads/scores.txt"; $open = fopen($filen,"r"); print ("<br/> Just tried to open file to add $newname and $newscore.<br/> "); print ("returned handler is $open <br/>"); if ($open) { $data = file($filen); fclose($open); //file closed for ($i=0;$i<count($data);$i++) { $item = explode(",",$data[$i]); $score = intval($item[1]); $scores[] = $score; print ("current score: $i ".$item[0]." ".$item[1]." <br/>"); }

for($i=0;$i<count($scores);$i++) { if (intval($newscore)>$scores[$i]) { $olddata = $data[$i]; $data[$i] = "$newname,$newscore\n"; for ($j=$i+1;$j<count($scores);$j++) { $nextone = $data[$j]; $data[$j] = $olddata; $olddata = $nextone; } // $j for break; //leave $i for loop } // if newscore better } // $i loop

print ("now will write out new data array. <hr>"); for($i=0;$i<count($scores);$i++) { print($data[$i]."<br/>"); } // now close the file which was open for reading fclose($open); print ("<br/>Trying to open $filen for writing <br/>"); $open = fopen($filen,"w");

if ($open) { print("writing out to file <br/>"); for($i=0;$i<count($scores);$i++) { fwrite($open,$data[$i]); } fclose($open); } else { print ("<br/> Unable to write updated file. The returned handler value was $open. <br/>"); } } // file opened successfully for initial read

else { // need to create file @fclose($open); //may not be necessary since file wasn't opened. print ("scores file doesn't exist yet<br/>"); $open = fopen($filen,"w"); if ($open) { $setsize = 5; //keep 5 top scores fwrite($open,"$newname,$newscore\n"); for ($i=1;$i<$setsize;$i++) { fwrite($open,"X,0\n"); } fclose($open); } else { print ("couldn't create scores file."); } } //needed to create scores file ?> </body> </html>

Application • Store results of a "test" with one file / person • file name based on "code" • If person takes test more than once, add on to the file • http://socialsoftware.purchase.edu/jeanine.meyer/testquiz.html

testquiz.html function check() { var oksofar = true; if (!((document.f.a1.value.length>0) && (document.f.a2.value.length>0) && (document.f.a3.value.length))){ alert("please submit answer for each question"); oksofar = false; } if (document.f.code.value.length<3){ alert ("The identifying code must be at least 3 characters long"); oksofar = false; } if (oksofar) { return true;} else { return false; } }

body of testquiz <body> Sample quiz <hr/> <form name="f" action="storeanswers.php" onsubmit="return check();" method="POST"> Identifying code: <input type="password" name="code"/> <br/> Answer 1: <input type="text" name="a1"> <br/> Answer 2: <input type="text" name="a2"> <br/> Answer 3: <input type="text" name="a3"> <br/> <input type="submit" value="Submit answers"/> </form> </body>

from storeanswers.php <?php $code = $_POST['code']; $a1 = $_POST['a1']; $a2 = $_POST['a2']; $a3 = $_POST['a3']; $nowp= new DateTime(); $now = $nowp->format('Y-m-d H:i:s'); $answers = "$code $now answers are 1= $a1 2= $a1 3= $a3 ".PHP_EOL; $filen ="uploads/answers" . $code . ".txt" ; $open=fopen($filen,"a"); if ($open) { fwrite($open,$answers); fclose($open); print "Answers stored "; } else { print "Problem with storing answers"; } ?>

sample output: done twice, producing 2 lines • jmm 2013-04-06 19:54:54 answers are 1= 23 2= 23 3= 4 • jmm 2013-04-06 19:55:09 answers are 1= 20 2= 20 3= 6

More on files • Create a subfolder in the folder/directory where you php file is: call it uploads. • Consult with CTS to confirm you can do this. • May need their help to set permissions

Why use files • Very simple structure OR • more complex or just different from tables • Perhaps with links (pointers) such as family or corporate tree • ?

Refrain on 3 tier • Some divide the html tier into content versus style, with CSS holding the style. This is the interaction tier. • Note: Flash and other languages (Processing, Java, ??) also do more function • Middle tier, php, do 'business logic', other function. • Information tier, MySQL, holds information! • Serves multiple functions. Implemented (possibly) by different groups in an enterprise.

Another tier? or is the 3 tier terminology insufficient • Organizations use code and content developed and maintained by others. • Web services • cloud computing • content such as Google maps • ??? Extra credit opportunity to report / comment.

php to php • Alternative to cookies or data passed via query strings are Sessions. • The sessions may be passed via the HTTP headers • Extra credit opportunity: research and do posting on php Sessions • Access and set using $_SESSION. • This, like $_COOKIE, etc. is an associative array: accessed using names not indices. • NOTE: the shopping cart in my store application is stored as a Session variable and is itself an associative array.

<?php • session_start(); • if (!isset($_SESSION["cart"])) { • $_SESSION['cart']=array(); • $_SESSION['items'] = 0; • $_SESSION['totalprice']=0.00; • $cart = array(); • } • else { • //print ("cart already started "); • $cart = $_SESSION['cart']; • } • ?>

<html><head><title>Shopping Cart</title> <? require("displaycartfunction.php"); ?> </head> <body> <?php require("opendbo.php"); ?> <h1>Shopping cart</h1> <p> <? if (isset($_GET['productid'])) { $p_id = $_GET['productid']; $quantity=$_GET['quantity']; $cart[$p_id] = $quantity; $_SESSION['cart'] = $cart; }

displaycart(); ?> <hr> <a href="submitorder.php"> Checkout (submit order)! </a> <a href="orderproduct.php"> More shopping! </a> </body> </html>

displaycart • Function stored in file displaycartfunction. • Assumes that connection has been made and session started. • Makes use of the foreach construction for associative arrays. • Since associative arrays don't use index values 0 to length of array, what is the code to examine each element? • Answer: foreach($aa as $key=>$qty) { }assuming $aa is the associative array and $key and $qty are variables used in the loop for the keys and values • Makes use of number_format($totalprice,2) to produce dollars and cents

<?php //assumes that opendbo called, and session started when call is made. function displaycart() { global $cart, $DBname, $link, $totalprice; print ("<table border=1>"); print ("<tr><td> Product ID </td> <td> Product Name </td><td> Quantity </td> <td> Total cost </td> </tr>"); $items = 0; //note session variable items not used $totalprice = 0.00; $cart = $_SESSION['cart'];

foreach (@$cart as $pid => $qty) { $items += $qty; //print(" the pid is ".$pid . " and the qty is ". $qty); $query="Select * from catalog where id='$pid'"; //print("query is $query"); $result = mysql_db_query($DBname, $query, $link); $item_price = mysql_result($result,0,"cost"); $item_name = mysql_result($result,0,"p_name"); $item_total_price = $item_price * $qty; $totalprice += $item_total_price; $item_total_pricef = number_format($item_total_price,2); print ("<tr><td> $pid </td> <td> $item_name </td><td> $qty </td> <td> $item_total_pricef </td> </td> "); }

$totalpricef = "$" . number_format($totalprice,2); print("<tr> <td> TOTALS </td> <td> </td> <td> $items items</td><td> $totalpricef </td></tr> </table>"); $_SESSION['items']=$items; $_SESSION['totalprice']=$totalprice; } ?>

Project assignment • Design and develop your own database php project • work individually and then gather team to determine general idea • Make posting to moodle with idea and names of people on team • YOU MAY WORK BY YOURSELF or in small group. From more, more is expected. • Develop database design (ER diagram) and Data flow diagram • Presentations on 4/15 • Complete project • Presentations on 5/2

Minimal requirements • At least 2 tables and at least 2 SQL statements • make use of at least one of • localStorage • file(s) • file uploading • sending email • At least 2 types of agents. For example: setup and production use. • Error handling (form input validation)

Classwork / homework • More postings (mainly from those people who haven’t done it) on security, passwords, normalization, and other topics). • Think about ‘original / from scratch’ project. Think about your teams. • Maybe smaller? • It is okay to ‘double-dip’; for example, build on project done for another class or you anticipate doing by adding a database. • Make proposal to moodle forum • Topic, names of people on team

Planning presentation • Tell what your project is • Show ER diagram • Definition of the tables • Show DFD • Definition of the tasks and the agents (aka users) and the data stores (database, maybe tables, maybe localStorage) • If you have it, perhaps a form This is a presentation of plans!!!!!!

Creating Databases