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, vendorv_id, expected_date, status (orders,
  received, partial), billed_amount
• Item_in_shipment
• i_ship_id, s_id, p_id
• Products
• p_id, name/description, vendorv_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 actionphp 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(na
  me 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 requestURLRequest new URLRequest("sendemail
  .php")
• var variablesURLVariables new URLVariables()
• var gameURLString "http//newmedia.purchase.edu
  /Jeanine/as30/chasebothedogemailb.html"
• //multiple inputs to the php
• variables.body"I caught Bo on "caught". Visit
  "gameURL
• variables.totoaddress.text
• variables.subject"news"
• variables.from fromaddress.text
• request.data variables
• request.methodURLRequestMethod.POST
• sendToURL(request)

15
Bo email example sendemail.php

• lt?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 /
• ?gt

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(eventEvent)void
• aTa.text "Group " event.target.selectedItem.
  data
• var variablesURLVariables new URLVariables
  ()
• variables.group event.target.selectedItem.data
  
• var requestURLRequest new URLRequest()
• request.data variables
• request.url "getgroupclips.php"
• var loaderURLLoader new URLLoader()
• loader.load(request)
• loader.addEventListener(Event.COMPLETE,
  getgroupclips)

18
Function getgroupclips(eventEvent) gets data
from php encoded with , and

• var clipsString event.target.data
• var clipsarrayArray clips.split(",")
• var cliptitlesArray new Array()
• var clipaddrsArray new Array()
• var clipinfoArray
• var iint
• var numint
• num clipsarray.length
• for(i0iltnumi)
• clipinfo clipsarrayi.split("")
• cliptitles.push(clipinfo1)
• clipaddrs.push(clipinfo0)
• more

19
Video example getgroupclips.php

• lt?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 (iltnum)
• fa mysql_result(result,i,"fileaddr")
• ct mysql_result(result,i,"cliptitle")
• sendback sendback . "fact"
• i
• if (iltnum)
• sendback sendback . ","
• print "sendback"
• ?gt

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(evEvent) sends
data (request) to a php file

• var requestURLRequest new
  URLRequest("fetchdesign.php")
• var variablesURLVariables new URLVariables()
• variables.designnameplayersfilename.text
• request.data variables
• playersfilename.text "fetching "
• var loaderURLLoader new URLLoader()
• loader.load(request)
• loader.addEventListener(Event.COMPLETE,
  getfromdb)

23
Function getfromdb(evEvent) gets the
information back from php

• var dsString ev.target.data
• var dxmlXML new XML()
• if (ds.lengthgt4)
• 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

• lt?php
• des_name _REQUEST'designname'
• require("opendbq.php")
• query"SELECT des_string from designs where
  des_name'des_name'"
• resultmysql_query(query)
• NoR _at_mysql_num_rows(result)
• if (NoRgt1)
• sendback mysql_result(result,0,"des_string")
  
• else
• sendback ""
• print sendback
• mysql_close(link)
• ?gt

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.