Creating Databases for Web Applications

Creating Databases for Web Applications 3-Tier. Design vs Function vs Content. More SQL. More php. Homework: work on final projects

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.

More SQL Assuming the simple student database (id, sname, department, gpa) • Produce a list of departments, with number of students, average, maximum, and minimum gpa • Produce a list of departments, number of students with gpa at least 2 (not on academic probation?) • Produce a list of departments, number of students on academic probation, limit to the 5 with the most on academic probation

Students in clubs • Assume the student table (sid, sname, dept, gpa) plus 2 more tables • clubs: clubid, clubname, clubdesc • clubmemberships: mid, sid, clubid • Usual technique, similar to tags, ordered items.

Generate list of students in club named Hiking SELECT s.sname FROM students as s, clubs as c, clubmemberships as m WHERE c.clubname='Hiking' AND m.clubid=c.clubid AND s.sid=m.sid ALTERNATIVE (may need parentheses) SELECT s.sname FROM students as s JOIN clubmemberships as m ON m.sid = s.sid JOIN clubs as c WHERE clubname='Hiking' ON m.clubid=c.clubid

Extra credit • (Difficult to check without creating all the tables, but try) • Generate list of club names, count of students in clubs, ordered from most to least

Left Join • Matches rows of two tables using the ON condition, but if something on the LEFT does not have a match, generate a row with null values. • Used in the quiz show to find questions that haven't been answered or asked recently. • NOTE: can have SELECT within a SELECT

Students not in any club SELECT sname FROM SELECT * FROM students as s LEFT JOIN clubmemberships as m ON s.sid=m.sid WHERE m.sid=null

php to php • One way to acquire and pass information from one php script to another is to use the query string. • In the store application, html is produced that has <a> tags with href=makeorder.php?3 for example, where 3 represents a product id.

Select product: <table> <?php $query="Select * from catalog"; $result=mysql_db_query($DBname, $query, $link); while ($row=mysql_fetch_array($result)) { print ("<tr><td><a href=makeorder.php"); print ("?p_id="); print($row['id']); print(">"); print($row['p_name']); print("</a></td>"); print("<td><img src=\""); $picture=$row['picture']; print("$picture"); print("\" width='200'></td></tr>"); } print ("</table>");

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; } ?>

Class work • Gather around someone with laptop. • Each group find different uses of JOIN (inner join) and LEFT JOIN to explain.

Homework • Work on final projects.

Creating Databases for Web Applications