Creating Databases for Web Applications

1. Creating Databases for Web Applications Reprise: Systems design (diagrams) Flash to php Homework: Prepare for quiz

2. Systems design • Focus on information • what is the information and how do [things] relate • Entity relationship diagrams is tool • Information may exist already in the organization • Focus on function of application(s) • who does what • … using what information

3. System design • slow down rush to implement • [start] documentation • Design and documentation may change as system is being built, deployed, refined…. • Still, goal is to decrease bugs

4. ER diagram • Database holds • Tables. Table represents one type of entityRow in table holds information on one example of the entity • Row in table made up of • Attributes (aka fields) • Lines connecting tables indicate occurrence of attribute in one table that points to a row in another table (or that table) • What is the pointer? The id of the row. This is called a foreign key. • Seems simple but recall how definition of library changed (evolved) during discussion

5. Store • Vendors (suppliers) • v_id, name, address • Shipments from vendors • s_id, vendor=v_id, expected_date, status (orders, received, partial), billed_amount • Item_in_shipment • i_ship_id, s_id, p_id • Products • p_id, name/description, vendor=v_id, size, type, quantity_in_stock, cost, base_price

6. Design to build • Use system design to plan • Decide on technologies • software & hardware • For example, what programming languages? what DBMS? who does / who can do each process? should application be customized for PDA (e.g., IPOD) • Plan / invoke usability standards. Plan testing / involvement of users • Allocate and schedule work • refine / update documentation • Deploy / deliver / distribute

7. Software decision • What already exists? • inside organization • generally available • something such as Google Maps API? • Open source • Make / buy • What is level of expertise in organization?

8. Example: What are pros/cons • JavaScript • Processing • ActionScript

9. ActionScript • Continue with implementation of video clip archive • NOTE: ActionScript invoking and getting something back from php scripts • Compare to: • html action=php script • THEN php creates a new html file

10. Aside • html is interpreted by the browser on the client computer • php is interpreted by the php engine installed on the server computer • SQL is interpreted by the MySQL program on the server computer.

11. ActionScript basics Code, that is, ActionScript is • in the .fla file, • created in the Flash development environment using the Actions panel • associated with frame • in separate .as files • created in the Flash development environment • each file formatted as a package • named class in Document package • named class in named package

12. ActionScript basics review • Data is in internal variables or dynamic or input text fields on the Stage • Text field needs a name. Get contents by .text attribute • fromaddress.text • Set up response to event using addEventListener(name of event, function) • sendbtn.addEventListener(MouseEvent.CLICK, sendemail); • loader.addEventListener(Event.COMPLETE, getfromdb);

13. ActionScript to/from php • URLRequest object: holds the php file name • URLVariables object: holds the data sent to php • URLLoader object: holds the data from php • NOTE: this is not instant! It is asynchronous process. Need to wait for the Event.COMPLETE event • Look at specific code!

14. Bo Email example: from sendemail function: 1 direction, don't need to use URLLoader var request:URLRequest = new URLRequest("sendemail.php"); var variables:URLVariables = new URLVariables(); var gameURL:String = "http://newmedia.purchase.edu/~Jeanine/as30/chasebothedogemailb.html" //multiple inputs to the php variables.body="I caught Bo on "+caught+". Visit "+gameURL; variables.to=toaddress.text; variables.subject="news"; variables.from = fromaddress.text; request.data = variables; request.method=URLRequestMethod.POST; sendToURL(request); }

15. Bo email example: sendemail.php <?php $to = $_POST['to']; $subject = $_POST['subject']; $body = $_POST['body']; $headers = "From: " . $_POST['from']; if (mail($to, $subject, $body,$headers)) { echo("Your message was sent"); } else { echo("There was a problem."); } /* the error messages will not appear if called from Flash application, but will appear if called from html */ ?>

16. Video example • choosevideoplay.fla uses an external Class definition • Video3.as • Invokes php functions to get information to populate each of two ComboBox components • Note: the data returned is the name of the video file NOT the video file itself.

17. Video example: cbHandler: a group of clips has been selected, the group name is to be sent to php private function cbHandler(event:Event):void { aTa.text = "Group: " + event.target.selectedItem.data; var variables:URLVariables = new URLVariables (); variables.group = event.target.selectedItem.data; var request:URLRequest = new URLRequest(); request.data = variables; request.url = "getgroupclips.php"; var loader:URLLoader = new URLLoader(); loader.load(request); loader.addEventListener(Event.COMPLETE, getgroupclips); }

18. Function: getgroupclips(event:Event) gets data from php encoded with , and : var clips:String = event.target.data; var clipsarray:Array = clips.split(","); var cliptitles:Array = new Array(); var clipaddrs:Array = new Array(); var clipinfo:Array; var i:int; var num:int; num = clipsarray.length; for(i=0;i<num;i++) { clipinfo = clipsarray[i].split(":"); cliptitles.push(clipinfo[1]); clipaddrs.push(clipinfo[0]); } … more …

19. Video example: getgroupclips.php <?php require("opendbq.php"); $group = $_GET['group']; $query = "SELECT fileaddr, cliptitle FROM videoclips2 where clipgroup='" . $group . "'"; $result = mysql_query($query); $sendback = ""; mysql_close(); $num = mysql_num_rows($result);

20. getgroupclips.php, cont. $i = 0; while ($i<$num) { $fa = mysql_result($result,$i,"fileaddr"); $ct = mysql_result($result,$i,"cliptitle"); $sendback = $sendback . "$fa:$ct"; $i++; if ($i<$num) { $sendback = $sendback . ","; } } print "$sendback"; ?> Putting in the colon Putting in the comma

21. Grid design example • Design is list of x, y coordinates and name of symbol in cell at those coordinates • Sends and receives [long] string that was/will be XML • Use built-in ActionScript to set up XML and then extract information about the design

22. Example: function fetchfromdb(ev:Event) sends data (request) to a php file var request:URLRequest = new URLRequest("fetchdesign.php"); var variables:URLVariables = new URLVariables(); variables.designname=playersfilename.text; request.data = variables; playersfilename.text ="fetching "; var loader:URLLoader = new URLLoader(); loader.load(request); loader.addEventListener(Event.COMPLETE, getfromdb);

23. Function getfromdb(ev:Event) gets the information back from php var ds:String = ev.target.data; var dxml:XML = new XML(); if (ds.length>4) { dxml = new XML(ds); restoredesign(dxml); // my function to use the XML } else { //empty data is 4 bytes long! playersfilename.text = "Design not in database"; } }

24. fetchdesign.php <?php $des_name = $_REQUEST['designname']; require("opendbq.php"); $query="SELECT des_string from designs where des_name='$des_name'"; $result=mysql_query($query); $NoR = @mysql_num_rows($result); if ($NoR>=1) { $sendback = mysql_result($result,0,"des_string"); } else { $sendback = ""; } print $sendback; mysql_close($link); ?>

25. Comments • The php back to Flash ActionScript uses print or echo. • The $sendback is my name. It could be anything. • The stuff sent back is the ev.target.data • Note: use of the aTa.text field to show you what is going on. This is not necessary in the html to/from php.

26. Homework • Practice ! • php to MySQL • ERD, DFD, [Storyboard] • Check out on-line midterm study guide • Catch up, review next class.